Oracle的臨時表

臨時表概念

臨時表用于保存事務或會話期間的中間結果集。臨時表中保存的數據只對當前會話可見，所有會話都看不到其他會話的數據；即使當前會話已經提交(commit)了數據，別的會話也看不到它的數據。對于臨時表，不存在多用戶并發問題，因為一個會話不會因為使用一個臨時表而阻塞另一個會話，即使我們“鎖住”臨時表，也不妨礙其他會話使用它們自己的臨時表。

臨時表默認存儲在用戶的默認臨時空間中，用戶也可以使用TABLESPACE命令指定臨時表所在的臨時表空間。

臨時表可以創建臨時的索引、視圖、觸發器。臨時表的數據不會備份，恢復，對其的修改也不會有任何日志信息。盡管對臨時表的DML操作速度比較快，但同樣也是要產生Redo Log ，只是同樣的DML語句，比對PERMANENT表的DML 產生的Redo Log少。

基本語法

CREATE GLOBAL TEMPORARY TABLE [ schema. ] table
[ (relational_properties) ]
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
[ physical_properties ]
[ table_properties ]

詳細的語法說明參見官方文檔：http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm#i2149006

臨時表分類

ORACLE臨時表有兩種類型：會話級的臨時表和事務級的臨時表。

1.ON COMMIT DELETE ROWS

它是臨時表的默認參數，表示臨時表中的數據僅在事物過程（Transaction）中有效，當事物提交（COMMIT）后，臨時表的暫時段將被自動截斷（TRUNCATE）。

2.ON COMMIT PRESERVE ROWS

它表示臨時表的內容可以在當前會話跨事物而存在，不過，當該會話結束時，臨時表的臨時段將隨著會話的結束而被丟棄，臨時表中的數據自然也就隨之丟棄。

臨時表使用演示

1.基于事務的臨時表

--創建臨時表
zx@ORCL>create global temporary table temp_transaction
  2  (id number,
  3  name varchar2(10) )
  4  on commit delete rows;

Table created.
--session1插入測試數據
zx@ORCL>insert into temp_transaction values (1,'zx');

1 row created.

zx@ORCL>select * from temp_transaction;

        ID NAME
---------- ------------------------------
         1 zx
--同時session2查詢臨時表并插入測試數據
SQL> select * from temp_transaction;

未選定行

SQL> insert into temp_transaction values (2,'lx');

已創建 1 行。

SQL> select * from temp_transaction;

	ID NAME
---------- --------------------
	 2 lx
--session1 commit操作
zx@ORCL>commit;

Commit complete.

zx@ORCL>select * from temp_transaction;

no rows selected
--session2 查詢數據，commit操作
SQL> select * from temp_transaction;

	ID NAME
---------- --------------------
	 2 lx

SQL> commit;

提交完成。

SQL> select * from temp_transaction;

未選定行

由上述操作過程可以看到不同的會話對于臨時表的操作是完全隔離的，commit后，基于事務的臨時表數據就會“消失”。

2.基于會話的臨時表

zx@ORCL>create global temporary table temp_session
  2  (id number,
  3  name varchar2(10) )
  4  on commit preserve rows;

Table created.

zx@ORCL>insert into temp_session values (1,'zx');

1 row created.

zx@ORCL>select * from temp_session;

        ID NAME
---------- ------------------------------
         1 zx

zx@ORCL>commit;

Commit complete.

zx@ORCL>select * from temp_session;

        ID NAME
---------- ------------------------------
         1 zx

zx@ORCL>truncate table temp_session;

Table truncated.

zx@ORCL>select * from temp_session;

no rows selected
--truncate表后數據“消失”
zx@ORCL>insert into temp_session values (1,'zx');

1 row created.

zx@ORCL>commit;

Commit complete.

zx@ORCL>select * from temp_session;

        ID NAME
---------- ------------------------------
         1 zx
--commit后數據還在
--退出會話重新進入
zx@ORCL>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rhel6 ~]$ zx

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 1 22:46:13 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

zx@ORCL>select * from temp_session;

no rows selected

通過上述操作可知，基于會話的臨時表，commit后數據也不會“消失”，只有當truncate或退出會話后數據才會“消失”。

查看臨時表的存在

zx@ORCL>col table_name for a30
zx@ORCL>col tablespace_name for a30
zx@ORCL>col DURATION for a30
zx@ORCL>select table_name,TABLESPACE_NAME,LOGGING,TEMPORARY,DURATION,MONITORING from user_tables;

TABLE_NAME                     TABLESPACE_NAME                LOGGING   TEM DURATION                       MONITORIN
------------------------------ ------------------------------ --------- --- ------------------------------ ---------
T                              TT                             YES       N                                  YES
SESS_STATS                     TT                             YES       N                                  YES
TEMP_SESSION                                                  NO        Y   SYS$SESSION                    NO
TEMP_TRANSACTION                                              NO        Y   SYS$TRANSACTION                NO

臨時表用途

什么時候使用臨時表？用臨時表和用中間表有啥區別呢？

我覺得是在需要的時候應用，下面是David Dai關于臨時表的一個應用說明，我覺得非常形象的說明了臨時表的應用場景：對于一個電子商務類網站，不同消費者在網站上購物，就是一個獨立的 SESSION，選購商品放進購物車中，最后將購物車中的商品進行結算。也就是說，必須在整個SESSION期間保存購物車中的信息。同時，還存在有些消費者，往往最終結賬時放棄購買商品。如果，直接將消費者選購信息存放在最終表（PERMANENT）中，必然對最終表造成非常大的壓力。因此，對于這種案例，就可以采用創建臨時表(ON COMMIT PRESERVE ROWS)的方法來解決。數據只在 SESSION 期間有效，對于結算成功的有效數據，轉移到最終表中后，ORACLE自動TRUNCATE 臨時數據；對于放棄結算的數據，ORACLE 同樣自動進行 TRUNCATE ，而無須編碼控制，并且最終表只處理有效訂單，減輕了頻繁的DML操作的壓力。

1：當處理某一批臨時數據，需要多次DML操作時（插入、更新等），建議使用臨時表。

2：當某些表在查詢里面，需要多次用來做連接時。（為了獲取目標數據需要關聯A、B、C， 同時為了獲取另外一個目標數據，需要關聯D、B、C....）

關于臨時表和中間表（NOLOGGING，保存中間數據，使用完后刪除）那個更適合用來存儲中間數據，我個人更傾向于使用臨時表，而不建議使用中間表。

臨時表的限制

臨時表不能是分區表、聚簇表或索引組織表；

臨時表不可以有外鍵；

臨時表不能包含嵌套表；

臨時表不支持并行UPDATE、DELETE和MERGE；

臨時表不支持分布式事務；

刪除臨時表

zx@ORCL>drop table temp_transaction;

Table dropped.
--如果刪除時報錯ORA-14452: attempt to create, alter or drop an index on temporary table already in use說明有會話正在使用臨時表，無法刪除

參考：

http://www.cnblogs.com/kerrycode/p/3285936.html

《9I10G11G編程藝術  深入數據庫體系結構》