ORACLE暫存資料表總結

暫存資料表概念

   暫存資料表就是用來暫時儲存臨時資料（亦或叫中間資料）的一個資料庫物件，它和普通表有些類似，然而又有很大區別。它只能儲存在暫存資料表空間，而非使用者的資料表空間。ORACLE暫存資料表是會話或事務層級的，只對當前會話或事務可見。每個會話只能查看和修改自己的資料。

 

暫存資料表文法

 

 

 

暫存資料表分類

 

ORACLE暫存資料表有兩種類型：會話級的暫存資料表和事務級的暫存資料表。

1）ON COMMIT DELETE ROWS

它是暫存資料表的預設參數，表示暫存資料表中的資料僅在事物過程（Transaction）中有效，當事物提交（COMMIT）後，暫存資料表的暫時段將被自動截斷（TRUNCATE），但是暫存資料表的結構 以及中繼資料還儲存在使用者的資料字典中。如果暫存資料表完成它的使命後，最好刪除暫存資料表，否則資料庫會殘留很多暫存資料表的表結構和中繼資料。

2）ON COMMIT PRESERVE ROWS

它表示暫存資料表的內容可以跨事物而存在，不過，當該會話結束時，暫存資料表的暫時段將隨著會話的結束而被丟棄，暫存資料表中的資料自然也就隨之丟棄。但是暫存資料表的結構以及中繼資料還儲存在使用者的資料字典中。如果暫存資料表完成它的使命後，最好刪除暫存資料表，否則資料庫會殘留很多暫存資料表的表結構和中繼資料。

1:會話級的暫存資料表的資料和你當前會話有關係，當前SESSION不退出的情況下，暫存資料表中的資料就還存在，暫存資料表的資料只有當你退出當前SESSION的時候才被截斷（TRUNCATE TABLE），如下所示：

會話層級的暫存資料表建立：

CREATE GLOBAL TEMPORARY TABLE TMP_TEST(　　　　ID NUMBER ,　　　　NAME VARCHAR2(32)) ON COMMIT PRESERVE ROWS;或CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT PRESERVE ROWSASSELECT * FROM TEST;操作樣本：SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST (　　 ID NUMBER , 　　NAME VARCHAR2(32) ) ON COMMIT PRESERVE ROWS;Table createdSQL> INSERT INTO TMP_TEST    SELECT 1, 'kerry' FROM DUAL;1 row insertedSQL> COMMIT;Commit completeSQL> SELECT * FROM TMP_TEST;ID 　　　　　　　　NAME---------- ----------------1 　　　　　　　　kerrySQL> INSERT INTO TMP_TEST 　　SELECT 2, 'rouce' FROM DUAL;1 row insertedSQL> ROLLBACK;Rollback completeSQL> SELECT * FROM TMP_TEST;ID 　　　　　　　　　　NAME---------- ----------------------1 　　　　　　　　　　kerrySQL>

 

2:事務級的暫存資料表（預設），這種類型的暫存資料表與事務有關，當進行事務提交或者交易回復的時候，暫存資料表的資料將自行截斷，即當COMMIT或ROLLBACK時，資料就會被TRUNCATE掉，其它的特性和會話級的暫存資料表一致。

事務級暫存資料表的建立方法：

CREATE GLOBAL TEMPORARY TABLE TMP_TEST(　　　　ID NUMBER ,　　　　NAME VARCHAR2(32)) ON COMMIT DELETE ROWS;或CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST( 　　　　ID NUMBER , 　　　　NAME VARCHAR2(32) ) ON COMMIT DELETE ROWS;Table createdSQL> INSERT INTO TMP_TEST 　　SELECT 1, 'kerry' FROM DUAL;1 row insertedSQL> SELECT * FROM TMP_TEST;ID 　　　　　　　　　　NAME---------- ----------------------1 　　　　　　　　　　kerrySQL> COMMIT;Commit completeSQL> SELECT * FROM TMP_TEST;ID 　　　　　　　　　　  NAME---------- ------------------------SQL>

 

3:關於暫存資料表只對當前會話或事務可見。每個會話只能查看和修改自己的資料。

用DM使用者登入資料庫，開啟SESSION 1後，建立暫存資料表TMP_TEST

 

CREATE GLOBAL TEMPORARY TABLE TMP_TEST(　　　　ID NUMBER ,　　　　NAME VARCHAR2(32)) ON COMMIT DELETE ROWS;或CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST ( 　　　　ID NUMBER , 　　　　NAME VARCHAR2(32) ) ON COMMIT DELETE ROWS;Table createdSQL> INSERT INTO TMP_TEST　　 SELECT 1, 'kerry' FROM DUAL;1 row insertedSQL> SELECT * FROM TMP_TEST;ID 　　　　　　　　　　NAME---------- ---------------------1 kerrySQL> COMMIT;Commit completeSQL> SELECT * FROM TMP_TEST;ID 　　　　　　　　　　NAME---------- -----------------------SQL>

 

 

用sys使用者登入資料庫，開啟SESSION 2

SELECT * FROM DBA_TABLES WHERE TABLE_NAME='TMP_TEST' --可以查到暫存資料表資料

SELECT * FROM DM.TMP_TEST; --查不到資料，即使TMP_TEST暫存資料表存在資料。

 

暫存資料表與永久表區別

SQL> SELECT TABLE_NAME, TABLESPACE_NAME,"LOGGING",　　  "TEMPORARY", DURATION, "MONITORING"　　 FROM DBA_TABLES WHERE TABLE_NAME IN ('TMP_TEST', 'TEST') ;TABLE_NAME   TABLESPACE_NAME LOGGING TEMPORARY DURATION    MONITORING------------- --------------  ------- --------- ----------- ---------TEST          TBS_EDS_DATA    YES       N                      YESTMP_TEST                      NO        Y     SYS$SESSION       NO

 

如上所示，暫存資料表是儲存在暫存資料表空間裡面的，但是上面指令碼可以看出，暫存資料表在資料字典中沒有指定其資料表空間，暫存資料表是NOLOGGING，DURATION為SYS$SESSION

暫存資料表的DML操作速度比較快，但同樣也是要產生 Redo Log ，只是同樣的DML語句，比對 PERMANENT 的DML 產生的Redo Log 少其實在應用中，往往會建立一個NOLOGGING的永久表（中間表）來儲存中間資料，從而代替暫存資料表，至於這這兩者有啥優劣，真是很難說清道明(歡迎大家探討)。

 

暫存資料表用途

 

什麼時候使用暫存資料表？用暫存資料表和用中間表有啥區別呢？

我覺得是在需要的時候應用，下面是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，儲存中間資料，使用完後刪除）那個更適合用來儲存中間資料，我個人更傾向於使用暫存資料表，而不建議使用中間表。

 

注意事項

1 ) 不支援 lob 對象，這也許是設計者基於運行效率的考慮，但實際應用中確實需要此功能時就無法使用暫存資料表了。這點網上很多資料都這麼說，我沒有追查到底是那個版本不支援lob對象，至少在ORACLE 10g這個版本中，暫存資料表是支援lob對象的.

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST

2 (

3 ID NUMBER ,

4 NAME CLOB

5 ) ON COMMIT PRESERVE ROWS;

Table created

SQL>

SQL> INSERT INTO TMP_TEST

2 SELECT 1, 'ADF' FROM DUAL;

1 row inserted

SQL> SELECT * FROM V$VERSION;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

2 ) 不支援主外鍵關係

3 ）暫存資料表不能永久的儲存資料。

4 ）暫存資料表的資料不會備份，恢複，對其的修改也不會有任何日誌資訊

5 ）暫存資料表不會有DML 鎖

DML locks are not acquired on the data of the temporary tables. The LOCK statement has no effect on a temporary table, because each session has its own private data.

6 ）儘管對暫存資料表的DML操作速度比較快，但同樣也是要產生 Redo Log ，只是同樣的DML語句，比對 PERMANENT 的DML 產生的Redo Log 少。請見官方文檔：

DML statements on temporary tables do not generate redo logs for the data changes.However, undo logs for the data and redo logs for the undo logs are generated. Data from the temporary table is automatically dropped in the case of session termination,either when the user logs off or when the session terminates abnormally such as during a session or instance failure.

7 ） 暫存資料表可以建立臨時的索引、視圖、觸發器。

8 ) 如果要DROP會話層級暫存資料表，並且其中包含資料時，必須先截斷其中的資料。否則會報錯。

SQL> DROP TABLE TMP_TEST PURGE;

DROP TABLE TMP_TEST PURGE

ORA-14452: 試圖建立, 更改或刪除正在使用的暫存資料表中的索引

SQL> TRUNCATE TABLE TMP_TEST;

Table truncated

SQL> DROP TABLE TMP_TEST PURGE;

Table dropped