Oracle臨時表GLOBAL TEMPORARY TABLE

```
臨時表：像普通表一樣，有結構，但是對資料的管理上不一樣，臨時表儲存事務或會話的中間結果集，臨時表中儲存的資料只對當前會話可見，所有會話都看不到其他會話的資料，即使其他會話提交了，也看不到。臨時表不存在併發行為，因為他們對於當前會話都是獨立的。
建立臨時表時，Oracle只建立了表的結構（在資料字典中定義），並沒有初始化記憶體空間，當某一會話使用臨時表時，ORALCE會從當前使用者的 臨時表空間分配一塊記憶體空間。也就是說只有向臨時表中插入資料時，才會給臨時表分配儲存空間。
 
臨時表分事務級臨時表和會話級臨時表。
事務級臨時表只對當前事務有效，透過語句：ON COMMIT DELETE ROWS 指定。
會話級臨時表對當前會話有效，透過語句：ON COMMIT PRESERVE ROWS語句指定。
 
用法舉例（在SCOTT模式下）：
CREATE GLOBAL TEMPORARY TABLE session_temp_tab ON COMMIT PRESERVE ROWS AS SELECT * FROM emp WHERE 1=2;
 ON COMMIT PRESERVE ROWS語句指定所建立的臨時表是會話級臨時表，當我們斷開連線或手動執行DELETE或TRUNCATE之前，臨時表中的資料一直存
 在，並且只有當前會話可以看到，其他會話看不到。
 
 CREATE GLOBAL TEMPORARY TABLE transaction_temp_tab ON COMMIT DELETE ROWS AS SELECT * FROM emp WHERE 1=2;
 ON COMMIT DELETE ROWS語句指定所建立的臨時表是事務級臨時表，當COMMIT或ROLLBACK之前，這些資料一直存在，當事務提交之後，表中資料自動清除。
 
insert into session_temp_tab select * from emp ;
insert into transaction_temp_tab select * from emp ;
 
 
 SQL> select count(*) from session_temp_tab ;
 
 COUNT(*)
----------
        14
 
 SQL> select count(*) from transaction_temp_tab ;
 
 COUNT(*)
----------
        14
SQL> commit;
 
 Commit complete
 
 SQL> select count(*) from session_temp_tab ;
 
 COUNT(*)
----------
        14
 
 SQL> select count(*) from transaction_temp_tab ;
 
 COUNT(*)
----------
        0
 
 
當COMMIT之後事務級臨時表中的資料自動清除，所以再次查詢的時候得到結果為0；
SQL> disconnect ;
Not logged on
 
 SQL> connect scott/tiger ;
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as scott
 
 SQL> select count(*) from transaction_temp_tab ;
 
 COUNT(*)
----------
        0
 
 SQL> select count(*) from session_temp_tab ;
 
 COUNT(*)
----------
        0
當斷開之後重新連線之後，會話級臨時表中的資料也被自動刪除了。
===============================================================================
5.tempporary
也是heap表的一種，有兩種臨時表。
SQL> create global temporary table gt1 as select * from dba_objects;

Table created.

SQL> select * from gt1;--沒有資料，因為省掉了on commit delete rows

no rows selected
SQL> create global temporary table gt2 on commit delete rows as select * from dba_objects;

Table created.

--還有一種臨時表，在提交的時候保留資料
SQL> create global temporary table gt3 on commit preserve rows as select * from dba_objects;

Table created.

SQL> select count(1) from gt3;

  COUNT(1)
----------
     10042
--另一個CMD
SQL> select count(1) from gt3;

  COUNT(1)
----------
         0
這個表是實實在在的表，這個表結構任何一個SESSION都能看到。但是每個SESSION的資料都是隔離的。定義保留在資料字典，資料儲存在臨時表空間裡。
使用它減少對日誌的產生。
10g,建立這個表屬於哪個使用者，就預設的放在這個使用者預設的臨時表空間裡。11g可以單獨放在一個表空間裡。
===============================================================================
在Oracle8i或以上版本中，可以建立以下兩種臨時表：
1。會話特有的臨時表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>)
ON COMMIT PRESERVE ROWS；
 
2。事務特有的臨時表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>)
ON COMMIT DELETE ROWS；
CREATE GLOBAL TEMPORARY TABLE MyTempTable
所建的臨時表雖然是存在的，但是你試一下insert 一條記錄然後用別的連線登上去select，記錄是空的，明白了吧，我把下面兩句話再貼一下：
--ON COMMIT DELETE ROWS 說明臨時表是事務指定，每次提交後ORACLE將截斷表（刪除全部行）
--ON COMMIT PRESERVE ROWS 說明臨時表是會話指定，當中斷會話時ORACLE將截斷表。
衝突的問題更本不用考慮.
 
臨時表只是儲存當前會話(session)用到的資料，資料只在事務或會話期間存在。
 
透過CREATE GLOBAL TEMPORARY TABLE命令建立一個臨時表，對於事務型別的臨時表，
資料只是在事務期間存在，對於會話型別的臨時表，資料在會話期間存在。
 
會話的資料對於當前會話私有。每個會話只能看到並修改自己的資料。DML鎖不會加到
臨時表的資料上。下面的語句控制行的存在性。
 
● ON COMMIT DELETE ROWS 表名行只是在事務期間可見
● ON COMMIT PRESERVE ROWS 表名行在整個會話期間可見
 
可以對臨時表建立索引，檢視，出發器，可以用export和import工具匯入匯出表的
定義，但是不能匯出資料。表的定義對所有的會話可見。

Temporary Tables臨時表
1簡介
   ORACLE資料庫除了可以儲存永久表外，還可以建立臨時表temporary tables。這些臨時表用來儲存一個會話SESSION的資料，
   或者儲存在一個事務中需要的資料。當會話退出或者使用者提交commit和回滾rollback事務的時候，臨時表的資料自動清空，
   但是臨時表的結構以及後設資料還儲存在使用者的資料字典中。
   臨時表只在oracle8i以及以上產品中支援。
2詳細介紹
   Oracle臨時表分為 會話級臨時表 和 事務級臨時表。
會話級臨時表是指臨時表中的資料只在會話生命週期之中存在，當使用者退出會話結束的時候，Oracle自動清除臨時表中資料。
事務級臨時表是指臨時表中的資料只在事務生命週期中存在。當一個事務結束（commit or rollback），Oracle自動清除臨時表中資料。
臨時表中的資料只對當前Session有效，每個Session都有自己的臨時資料，並且不能訪問其它Session的臨時表中的資料。因此，
臨時表不需要DML鎖.當一個會話結束(使用者正常退出 使用者不正常退出 ORACLE例項崩潰)或者一個事務結束的時候，Oracle對這個會話的
表執行 TRUNCATE 語句清空臨時表資料.但不會清空其它會話臨時表中的資料.
你可以索引臨時表和在臨時表基礎上建立檢視.同樣,建立在臨時表上的索引也是臨時的,也是隻對當前會話或者事務有效.
臨時表可以擁有觸發器.
3建立臨時表
   臨時表的定義對所有會話SESSION都是可見的,但是表中的資料只對當前的會話或者事務有效.
   建立方法:
1) ON COMMIT DELETE ROWS 定義了建立事務級臨時表的方法.
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT DELETE ROWS;
EXAMPLE:
SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area
  2  (startdate DATE,
  3  enddate DATE,
  4  class CHAR(20))
  5  ON COMMIT DELETE ROWS;
SQL> create table permernate( a number);
SQL> insert into admin_work_area values(sysdate,sysdate,'temperary table');
SQL> insert into permernate values(1);
SQL> commit;
SQL> select * from admin_work_area;
SQL> select  * from permernate;
A
1
2)ON COMMIT PRESERVE ROWS 定義了建立會話級臨時表的方法.
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT PRESERVE ROWS;
EXAMPLE:
會話1:
SQL> drop table admin_work_area;
SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area
  2  (startdate DATE,
  3  enddate DATE,
  4  class CHAR(20))
  5  ON COMMIT PRESERVE ROWS;
SQL> insert into permernate values(2);
SQL> insert into admin_work_area values(sysdate,sysdate,'session temperary');
SQL> commit;
SQL> select * from permernate;
         A
----------
         1
         2
SQL> select * from admin_work_area;
STARTDATE  ENDDATE    CLASS
---------- ---------- --------------------
17-1?? -03 17-1?? -03 session temperary
會話2:
SQL> select * from permernate;
         A
----------
         1
         2
SQL> select * from admin_work_area;
未選擇行.
會話2看不見會話1中臨時表的資料.
 
4 ORACLE臨時表和SQLSERVER臨時表異同
SQLSERVER臨時表
也可以建立臨時表。臨時表與永久表相似，但臨時表儲存在 tempdb 中，當不再使用時會自動刪除。
有本地和全域性兩種型別的臨時表，二者在名稱、可見性和可用性上均不相同。本地臨時表的名稱以單個數字符號 (#) 打頭；
它們僅對當前的使用者連線是可見的；當使用者從 Microsoft? SQL Server? 2000 例項斷開連線時被刪除。全域性臨時表的名稱以數學符號
(##) 打頭，建立後對任何使用者都是可見的，當所有引用該表的使用者從 SQL Server 斷開連線時被刪除。
例如，如果建立名為 employees 的表，則任何人只要在資料庫中有使用該表的安全許可權就可以使用該表，除非它已刪除。
如果建立名為 #employees 的本地臨時表，只有您能對該表執行操作且在斷開連線時該表刪除。如果建立名為 ##employees 的全域性臨時表
，資料表中的任何使用者均可對該表執行操作。如果該表在您建立後沒有其他使用者使用，則當您斷開連線時該表刪除。如果該表在您建立
後有其他使用者使用，則 SQL Server在所有使用者斷開連線後刪除該表
不同:
1.SQL SERVER臨時表是一種”記憶體表”,表是儲存在記憶體中的.ORACLE臨時表除非執行DROP TABLE,否則表定義會保留在資料字典中.
2.SQL SERVER臨時表不存在類似ORACLE臨時表 事務級別 上的功能.
3.SQL SERVER本地臨時表(#) 與 ORACLE的會話級別臨時表類似,但是在會話退出的時候,ORACLE不會刪除表.
4.SQL SERVER的全域性臨時表(##) 是指多個連線共享同一片記憶體.當沒有指標引用該記憶體區域時,SQL SERVER自動釋放全域性臨時表.
5.由於ORACLE不是一種 記憶體中的資料庫. 所以如果ORACLE類似SQL SERVER 頻繁的對臨時表進行建立和刪除,必定會影響效能.
所以ORACLE會保留臨時表的定義直到使用者DROP TABLE.
6.在ORACLE中,如果需要多個使用者共享一個表(類似SQL SERVER的全域性臨時表##).則可以利用永久表,
並且在表中新增一些可以唯一標識使用者的列.利用觸發器和檢視.當使用者退出的時候,根據該登陸使用者的唯一資訊刪除相應的表中的資料.
這種方法給ORACLE帶來了一定量的負載.

===============================================================================
SQL的IN中有很多值，如何處理能提高效率？            

近日碰到一個棘手的問題，從外部得到了一組ID資料，要跟Oracle中的某個表做比對，從Oracle中取出ID一樣的資料。舉個例子，pub有版主有會員，版主是會員的子集，版主名單存在Oracle裡面，但會員名單沒存在裡面。假設pub在搞抽獎活動，一小時一次，中獎的會員名單透過中獎系統傳給Oracle。我現在想把剛才中獎的會員中是版主的名單列出來，該怎麼做。
實際環境中，透過外部獲得的資料數量級為K，Oracle的這個表數量級為幾十K到上百K，比對效率成為一個關鍵因素。我能想到的方法就是構造多條SQL語句，把這些ID作為IN的列表值傳進去（IN後的列表項不能超過1000個，故可能會需要多個sql）。
帶著問題諮詢了yangtingkun ，得到的答覆是效率不會太高也不會太低，Oracle可能會做全表掃描，但效率比你一個一個去比對肯定要快了。實際執行了一下，IN裡面傳了300個引數，得到執行計劃如下（做的是 INDEX RANGE SCAN）：
執行計劃
----------------------------------------------------------
Plan hash value: 1656459801
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |   284 |  1136 |     1   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR  |         |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| XXXXXX|   284 |  1136 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
拼多個sql語句是麻煩一點，不過這樣做效率上沒啥問題。想想也是，總比我將資料插入表，透過表關聯去做要方便一些，因為建表的話我畢竟還需要對錶裡的資料進行維護。
“哦，這樣的話，你建一個臨時表吧！”
“global temporary table？”
“嗯，這表就是專門用來解決這樣的問題的！臨時資料只管往裡面插，可透過on submit關鍵字設定為會話有效或者事務有效，各會話間互相看不到資料，不會互相影響。”
哈哈，原來問題這麼簡單啊！省去了資料維護的麻煩，得到了表關聯帶來的高效資料處理，唯一會影響到效率的問題就是往臨時表裡插資料了，一條一條顯然很慢了，考慮用儲存過程來實現，呵呵。

插入多行資料，使用union
DB.ExcuteNonQuery("Delete from t863_Y_UKIDS_XL");
sSqlInsertTemp = "INSERT INTO  t863_Y_UKIDS_XL(iukid) " + sXLsqlvalue;

INSERT INTO  t863_Y_UKIDS(iukid)
SELECT  123   FROM  DUAL
 UNION SELECT  456   FROM  DUAL
 UNION SELECT  1233   FROM  DUAL
 UNION SELECT  4526   FROM  DUAL
 UNION SELECT  12333   FROM  DUAL
 UNION SELECT  456   FROM  DUAL
```