詳細講解Oracle資料庫中的暫存資料表用法

這篇文章主要介紹了Oracle資料庫中的暫存資料表用法，希望對大家的學習和工作有所協助。

一：文法

在Oracle中，可以建立以下兩種暫存資料表：

(1) 會話特有的暫存資料表

CREATE GLOBAL TEMPORARY ( )

ON COMMIT PRESERVE ROWS；

(2) 事務特有的暫存資料表

CREATE GLOBAL TEMPORARY ( )

ON COMMIT DELETE ROWS；

CREATE GLOBAL TEMPORARY TABLE MyTempTable

所建的暫存資料表雖然是存在的，但是如果insert 一條記錄然後用別的串連登上去select，記錄是空的。　　

--ON COMMIT DELETE ROWS 說明暫存資料表是事務指定，每次提交後ORACLE將截斷表（刪除全部行）

--ON COMMIT PRESERVE ROWS 說明暫存資料表是會話指定，當中斷會話時ORACLE將截斷表。　　

二：動態建立

create or replace procedure pro_temp(v_col1 varchar2,v_col2 varchar2) as

v_num number;

begin

select count(*) into v_num from
user_tables where table_name=''T_TEMP'';　　

--create temporary table

if v_num<1 then

execute immediate ''CREATE GLOBAL TEMPORARY TABLE T_TEMP (

COL1 VARCHAR2(10),

COL2 VARCHAR2(10)

) ON COMMIT delete ROWS'';

end if;　　

--insert data

execute immediate ''insert into t_temp values
(''''''　　v_col1　　'''''',''''''　　v_col2　　'''''')'';　　

execute immediate ''select col1 from t_temp'' into v_num;

dbms_output.put_line(v_num);

execute immediate ''delete from t_temp'';

commit;

execute immediate ''drop table t_temp'';

end pro_temp;　

　

測試:　　

15:23:54 SQL> set serveroutput on

15:24:01 SQL> exec pro_temp(''11'',''22'');

11　　

PL/SQL 過程已成功完成。　　

經過時間: 00: 00: 00.79

15:24:08 SQL> desc t_temp;

ERROR：

ORA-04043: 對象 t_temp 不存在　

　

三:特性和效能(與普通表和視圖的比較)

暫存資料表只在當前串連內有效

暫存資料表不建立索引,所以如果資料量比較大或進行多次查詢時,不推薦使用

資料處理比較複雜的時候時錶快,反之視圖快點

在僅僅查詢資料的時候建議用遊標: open cursor for ''sql clause'';

==================

 

暫存資料表特性

1、資料庫中的所有會話均可以訪問同一暫存資料表，但只有插入資料到暫存資料表中的會話才能看到它本身插入的資料。

2、可以把暫存資料表指定為事務相關（預設）或者是會話相關：

3、如果暫存資料表中有記錄的話，是無法刪除表的。即無法drop table。

4、雖然暫存資料表不產生 "REDO" ,但卻是要產生 "UNDO" 的

暫存資料表的分類及建立1、會話特有的暫存資料表

記錄將留在此表中，直到會話斷開或通過DELETE或TRUNCATE從物理上刪除這些記錄。

CREATE GLOBAL TEMPORARY TABLE <TABLE_NAME> (<column specification> )

ON COMMIT PRESERVE ROWS;

2、事務特有的暫存資料表

當事務提交後，在事務之中插入的記錄不會被保留，自動刪除。

CREATE GLOBAL TEMPORARY TABLE <TABLE_NAME> (<column specification> )

ON COMMIT DELETE ROWS;

3、DDL文法注釋

這裡的“GLOBAL”表示暫存資料表的定義，是所有會話都能看見的。建立暫存資料表只有 CREATE GLOBAL TEMPORARY TABLE，而沒有其它CREATE ****** TEMPORARY TABLE形式的命令。

暫存資料表的刪除1、刪除會話特有的暫存資料表

想快速刪除此類暫存資料表，必須先truncate表中的資料，然後drop表結構。如果使用DELETE命令先刪除表中記錄的話，還無法直接刪除表。只有等當前會話退出後，在其它會話或新的會話中刪除表結構。

使用DELTETE後DROP表報錯：

SQL> DELETE tmp_test;

8 rows deleted

SQL> commit;

Commit complete

SQL> drop table tmp_test;

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

這是因為用“ON COMMIT PRESERVE ROWS”子句時,會加行鎖(ROW-X).

TYPE=TO

TO Lock "Temporary Table Object Enqueue"

具體請看DOC ID:186854.1

2、刪除事務特有的暫存資料表

用ON COMMIT DELETE ROWS 子句就不會有那麼多限制。COMMIT以後，記錄自動清除，可以直接就刪除表。

暫存資料表的資料表空間的分配

暫存資料表在建立的時候，是不分配資料表空間的。當使用者使用暫存資料表儲存資料時，從該使用者預設的暫存資料表空間來分配儲存空間。

參考資料

1、http://blog.itpub.net/post/10/8764

使用暫存資料表的sql最佳化案例二－暫存資料表的統計資訊

2、http://www.itpub.net/178008.html&highlight=%C1%D9%CA%B1%B1%ED

拋磚引玉：三種主流資料庫中暫存資料表的使用.doc

MS SQLSERVER

SQL Server 支援暫存資料表。暫存資料表就是那些名稱以井號 (#) 開頭的表。如果當使用者中斷連線時沒有除去暫存資料表，SQL Server 將自動除去暫存資料表。暫存資料表不儲存在當前資料庫內，而是儲存在系統資料庫 tempdb 內。

暫存資料表有兩種類型：

本地暫存資料表：本地暫存資料表的名稱以單個數字記號 (#) 打頭；它們僅對當前的使用者串連是可見的；當使用者從 Microsoft SQL Server 2000 執行個體中斷連線時被刪除。

全域暫存資料表：全域暫存資料表的名稱以數學符號 (##) 打頭，建立後對任何使用者都是可見的。如果在建立全域暫存資料表的串連斷開前沒有顯式地除去這些表，那麼只要所有其它任務停止引用它們，這些表即被除去。當建立全域暫存資料表的串連斷開後，新的任務不能再引用它們。當前的語句一執行完，任務與表之間的關聯即被除去；因此通常情況下，只要建立全域暫存資料表的串連斷開，全域暫存資料表即被除去。

例如，如果建立名為 employees 的表，則任何人只要在資料庫中有使用該表的安全許可權就可以使用該表，除非它已刪除。如果建立名為 #employees 的本地暫存資料表，只有您能對該表執行操作且在中斷連線時該表刪除。如果建立名為 ##employees 的全域暫存資料表，資料表中的任何使用者均可對該表執行操作。如果該表在您建立後沒有其他使用者使用，則當您中斷連線時該表刪除。如果該表在您建立後有其他使用者使用，則 SQL Server在所有使用者中斷連線後刪除該表。

現在，暫存資料表的許多傳統用途可由具有 table 資料類型的變數替換。

ORACLE

Oracle支援暫存資料表。暫存資料表用來儲存事務或會話期間的中間結果。在暫存資料表中儲存的資料只有對當前會話是可見的，任何會話都不能看到其他會話的資料，即使在當前會話COMMIT資料以後也是不可見的。多使用者並行不是問題，一個會話從來不阻塞另一個會話使用暫存資料表。即使鎖定暫存資料表，一個會話也不會阻塞其他會話使用暫存資料表。暫存資料表比正常表產生的REDO少得多，然而，由於暫存資料表必須產生包含資料的UNDO資訊，所以會產生一定數量的REDO日誌。

暫存資料表將從使用者暫存資料表空間的的目前日誌中分配空間，或者如果從有定義權的程式中訪問，將使用程式所有者的暫存資料表空間。全域暫存資料表實際上只是表本身的模板。建立暫存資料表的行為不包括儲存空間的分配，也不包括INITIAL的分配。因此，在運行時當一個會話首先將資料放到暫存資料表中時，這時將建立這個會話的臨時段。由於每個會話擷取自己的臨時段，每個使用者可能在不同的資料表空間中為暫存資料表分配空間。USER1的default暫存資料表空間為TEMP1，他的暫存資料表將從TEMP1中分配空間，USER2的default暫存資料表空間為TEMP2，他的暫存資料表將從TEMP2中分配空間。

暫存資料表在每個資料庫中只需建立一次，不必在每個預存程序中建立。暫存資料表總是存在的，除非手動的刪除他。暫存資料表作為對象存在資料字典中，並且總是保持為空白，直到有會話在其中放入資料。Oracle允許建立基於暫存資料表的視圖和預存程序。

暫存資料表可以是以會話為基礎的，也可以是以事務為基礎的。ON COMMIT PRESERVE ROWS子句使暫存資料表成為基於會話的模式。行將留在此表中，直到會話斷開或通過DELETE或TRUNCATE從物理上刪除這些行。ON COMMIT DELETE ROWS子句使暫存資料表成為基於事務的模式。當會話提交後，行消失。這個暫存資料表的自動清除過程不會有額外的開銷。

在oracle中，應用程式需要的暫存資料表應該在程式安裝時建立，而不是在程式運行時建立。（這是與ms sqlserver或sybase的使用的不同）

在任何資料庫中，暫存資料表的一個缺點是：事實上最佳化器在暫存資料表中沒有真正的統計功能。然而，在oracle中，一系列較好的統計猜測可以通過DBMS_STATS包在暫存資料表中設定。

DB2

可使用 DECLARE GLOBAL TEMPORARY TABLE 語句來定義暫存資料表。DB2的暫存資料表是基於會話的，且在會話之間是隔離的。當會話結束時，暫存資料表的資料被刪除，暫存資料表被隱式卸下。對暫存資料表的定義不會在SYSCAT.TABLES中出現

下面是定義暫存資料表的一個樣本：

DECLARE GLOBAL TEMPORARY TABLE gbl_temp

LIKE empltabl

ON COMMIT DELETE ROWS

NOT LOGGED

IN usr_tbsp

此語句建立一個名為 gbl_temp 的使用者暫存資料表。定義此使用者暫存資料表 所使用的列的名稱和說明與 empltabl 的列的名稱和說明完全相同。隱式定義 只包括列名、資料類型、可為空白特性和列預設值屬性。未定義所有其他列屬性，包括唯一約束、外部關鍵字約束、觸發器和索引。執行 COMMIT 操作時， 若未對該表開啟 WITH HOLD 遊標，則該表中的所有資料都被刪除。不記錄 對使用者暫存資料表所作的更改。使用者暫存資料表被放在指定的使用者暫存資料表空間中。此資料表空間必須存在，否則此表的聲明將失敗。

戶定義暫存資料表不支援：

LOB 類型的列（或基於 LOB 的單實值型別列）

使用者定義型別列

LONG VARCHAR 列

DATALINK 列

End of document.