臨時(shí)表概念

昵稱10504424 2013-08-28

展開全文

臨時(shí)表就是用來暫時(shí)保存臨時(shí)數(shù)據(jù)（亦或叫中間數(shù)據(jù)）的一個(gè)數(shù)據(jù)庫對(duì)象，它和普通表有些類似，然而又有很大區(qū)別。它只能存儲(chǔ)在臨時(shí)表空間，而非用戶的表空間。ORACLE臨時(shí)表是會(huì)話或事務(wù)級(jí)別的，只對(duì)當(dāng)前會(huì)話或事務(wù)可見。每個(gè)會(huì)話只能查看和修改自己的數(shù)據(jù)。

臨時(shí)表語法

clip_image002

臨時(shí)表分類

ORACLE臨時(shí)表有兩種類型：會(huì)話級(jí)的臨時(shí)表和事務(wù)級(jí)的臨時(shí)表。

1）ON COMMIT DELETE ROWS

它是臨時(shí)表的默認(rèn)參數(shù)，表示臨時(shí)表中的數(shù)據(jù)僅在事物過程（Transaction）中有效，當(dāng)事物提交（COMMIT）后，臨時(shí)表的暫時(shí)段將被自動(dòng)截?cái)啵═RUNCATE），但是臨時(shí)表的結(jié)構(gòu) 以及元數(shù)據(jù)還存儲(chǔ)在用戶的數(shù)據(jù)字典中。如果臨時(shí)表完成它的使命后，最好刪除臨時(shí)表，否則數(shù)據(jù)庫會(huì)殘留很多臨時(shí)表的表結(jié)構(gòu)和元數(shù)據(jù)。

2）ON COMMIT PRESERVE ROWS

它表示臨時(shí)表的內(nèi)容可以跨事物而存在，不過，當(dāng)該會(huì)話結(jié)束時(shí)，臨時(shí)表的暫時(shí)段將隨著會(huì)話的結(jié)束而被丟棄，臨時(shí)表中的數(shù)據(jù)自然也就隨之丟棄。但是臨時(shí)表的結(jié)構(gòu)以及元數(shù)據(jù)還存儲(chǔ)在用戶的數(shù)據(jù)字典中。如果臨時(shí)表完成它的使命后，最好刪除臨時(shí)表，否則數(shù)據(jù)庫會(huì)殘留很多臨時(shí)表的表結(jié)構(gòu)和元數(shù)據(jù)。

1:會(huì)話級(jí)的臨時(shí)表的數(shù)據(jù)和你當(dāng)前會(huì)話有關(guān)系，當(dāng)前SESSION不退出的情況下，臨時(shí)表中的數(shù)據(jù)就還存在，臨時(shí)表的數(shù)據(jù)只有當(dāng)你退出當(dāng)前SESSION的時(shí)候才被截?cái)啵═RUNCATE TABLE），如下所示：

會(huì)話級(jí)別的臨時(shí)表創(chuàng)建：

CREATE GLOBAL TEMPORARY TABLE TMP_TEST
(
　　　　ID NUMBER ,
　　　　NAME VARCHAR2(32)
) ON COMMIT PRESERVE ROWS;
或
CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT PRESERVE ROWS
AS
SELECT * FROM TEST;
操作示例：
SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
 (
　　 ID NUMBER ,
 　　NAME VARCHAR2(32)
 ) ON COMMIT PRESERVE ROWS;
Table created
SQL> INSERT INTO TMP_TEST
    SELECT 1, 'kerry' FROM DUAL;
1 row inserted
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TMP_TEST;
ID 　　　　　　　　NAME
---------- ----------------
1 　　　　　　　　kerry
SQL> INSERT INTO TMP_TEST
 　　SELECT 2, 'rouce' FROM DUAL;
1 row inserted
SQL> ROLLBACK;
Rollback complete
SQL> SELECT * FROM TMP_TEST;
ID 　　　　　　　　　　NAME
---------- ----------------------
1 　　　　　　　　　　kerry
SQL>

2:事務(wù)級(jí)的臨時(shí)表（默認(rèn)），這種類型的臨時(shí)表與事務(wù)有關(guān)，當(dāng)進(jìn)行事務(wù)提交或者事務(wù)回滾的時(shí)候，臨時(shí)表的數(shù)據(jù)將自行截?cái)?，即?dāng)COMMIT或ROLLBACK時(shí)，數(shù)據(jù)就會(huì)被TRUNCATE掉，其它的特性和會(huì)話級(jí)的臨時(shí)表一致。

事務(wù)級(jí)臨時(shí)表的創(chuàng)建方法：

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 created
SQL> INSERT INTO TMP_TEST
 　　SELECT 1, 'kerry' FROM DUAL;
1 row inserted
SQL> SELECT * FROM TMP_TEST;
ID 　　　　　　　　　　NAME
---------- ----------------------
1 　　　　　　　　　　kerry
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TMP_TEST;
ID 　　　　　　　　　　  NAME
---------- ------------------------

SQL>

3:關(guān)于臨時(shí)表只對(duì)當(dāng)前會(huì)話或事務(wù)可見。每個(gè)會(huì)話只能查看和修改自己的數(shù)據(jù)。

用DM用戶登錄數(shù)據(jù)庫，打開SESSION 1后，創(chuàng)建臨時(shí)表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 created
SQL> INSERT INTO TMP_TEST
　　 SELECT 1, 'kerry' FROM DUAL;
1 row inserted
SQL> SELECT * FROM TMP_TEST;
ID 　　　　　　　　　　NAME
---------- ---------------------
1 kerry
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM TMP_TEST;
ID 　　　　　　　　　　NAME
---------- -----------------------

SQL>

用sys用戶登錄數(shù)據(jù)庫，打開SESSION 2

SELECT * FROM DBA_TABLES WHERE TABLE_NAME='TMP_TEST' --可以查到臨時(shí)表數(shù)據(jù)

SELECT * FROM DM.TMP_TEST; --查不到數(shù)據(jù)，即使TMP_TEST臨時(shí)表存在數(shù)據(jù)。

臨時(shí)表與永久表區(qū)別

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                      YES
TMP_TEST                      NO        Y     SYS$SESSION       NO

如上所示，臨時(shí)表是存儲(chǔ)在臨時(shí)表空間里面的，但是上面腳本可以看出，臨時(shí)表在數(shù)據(jù)字典中沒有指定其表空間，臨時(shí)表是NOLOGGING，DURATION為SYS$SESSION

臨時(shí)表的DML操作速度比較快，但同樣也是要產(chǎn)生 Redo Log ，只是同樣的DML語句，比對(duì) PERMANENT 的DML 產(chǎn)生的Redo Log 少其實(shí)在應(yīng)用中，往往會(huì)創(chuàng)建一個(gè)NOLOGGING的永久表（中間表）來保存中間數(shù)據(jù)，從而代替臨時(shí)表，至于這這兩者有啥優(yōu)劣，真是很難說清道明(歡迎大家探討)。

臨時(shí)表用途

什么時(shí)候使用臨時(shí)表？用臨時(shí)表和用中間表有啥區(qū)別呢？

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

1：當(dāng)處理某一批臨時(shí)數(shù)據(jù)，需要多次DML操作時(shí)（插入、更新等），建議使用臨時(shí)表。

2：當(dāng)某些表在查詢里面，需要多次用來做連接時(shí)。（為了獲取目標(biāo)數(shù)據(jù)需要關(guān)聯(lián)A、B、C，同時(shí)為了獲取另外一個(gè)目標(biāo)數(shù)據(jù)，需要關(guān)聯(lián)D、B、C....）

關(guān)于臨時(shí)表和中間表（NOLOGGING，保存中間數(shù)據(jù)，使用完后刪除）那個(gè)更適合用來存儲(chǔ)中間數(shù)據(jù)，我個(gè)人更傾向于使用臨時(shí)表，而不建議使用中間表。

注意事項(xiàng)

1 ) 不支持 lob 對(duì)象，這也許是設(shè)計(jì)者基于運(yùn)行效率的考慮，但實(shí)際應(yīng)用中確實(shí)需要此功能時(shí)就無法使用臨時(shí)表了。這點(diǎn)網(wǎng)上很多資料都這么說，我沒有追查到底是那個(gè)版本不支持lob對(duì)象，至少在ORACLE 10g這個(gè)版本中，臨時(shí)表是支持lob對(duì)象的.

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 ) 不支持主外鍵關(guān)系

3 ）臨時(shí)表不能永久的保存數(shù)據(jù)。

4 ）臨時(shí)表的數(shù)據(jù)不會(huì)備份，恢復(fù)，對(duì)其的修改也不會(huì)有任何日志信息

5 ）臨時(shí)表不會(huì)有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 ）盡管對(duì)臨時(shí)表的DML操作速度比較快，但同樣也是要產(chǎn)生 Redo Log ，只是同樣的DML語句，比對(duì) PERMANENT 的DML 產(chǎn)生的Redo Log 少。請(qǐng)見官方文檔：

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 ） 臨時(shí)表可以創(chuàng)建臨時(shí)的索引、視圖、觸發(fā)器。

8 ) 如果要DROP會(huì)話級(jí)別臨時(shí)表，并且其中包含數(shù)據(jù)時(shí)，必須先截?cái)嗥渲械臄?shù)據(jù)。否則會(huì)報(bào)錯(cuò)。

SQL> DROP TABLE TMP_TEST PURGE;

DROP TABLE TMP_TEST PURGE

ORA-14452: 試圖創(chuàng)建, 更改或刪除正在使用的臨時(shí)表中的索引

SQL> TRUNCATE TABLE TMP_TEST;

Table truncated

SQL> DROP TABLE TMP_TEST PURGE;

Table dropped

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自：昵稱10504424 > 《工作》

舉報(bào)/認(rèn)領(lǐng)