Oracle 臨時表

1、前言
    目前所有使用Oracle作為數據庫支撐平臺的應用，大部分數據量比較龐大的系統，即表的數據量一般情況下都是在百萬級以上的數據量。當然在Oracle中創建分區是一種不錯的選擇，但是當你發現你的應用有多張表關聯的時候，並且這些表大部分都是比較龐大，而你關聯的時候發現其中的某一張或者某幾張表關聯之後得到的結果集非常小並且查詢得到這個結果集的速度非常快，那麼這個時候我考慮在Oracle中創建“臨時表”。
    我對臨時表的理解：在Oracle中創建一張表，這個表不用於其他的什麼功能，主要用於自己的軟件系統一些特有功能才用的，而當你用完之後表中的數據就沒用了。Oracle的臨時表創建之後基本不佔用表空間，如果你沒有指定臨時表（包括臨時表的索引）存放的表空的時候，你插入到臨時表的數據是存放在ORACLE系統的臨時表空間中（TEMP）。

2、臨時表的創建
    創建Oracle臨時表，可以有兩種類型的臨時表：會話級的臨時表和事務級的臨時表。

    1）會話級的臨時表因為這這個臨時表中的數據和你的當前會話有關系，當你當前SESSION不退出的情況下，臨時表中的數據就還存在，而當你退出當前SESSION的時候，臨時表中的數據就全部沒有了，當然這個時候你如果以另外一個SESSION登陸的時候是看不到另外一個SESSION中插入到臨時表中的數據的。即兩個不同的SESSION所插入的數據是互不相干的。當某一個SESSION退出之後臨時表中的數據就被截斷（truncate table，即數據清空）了。
會話級的臨時表創建方法：Create Global Temporary Table Table_Name(Col1 Type1,Col2 Type2...) On Commit Preserve Rows；
舉例create global temporary table Student(Stu_id Number(5),Class_id  Number(5),Stu_Name Varchar2(8),Stu_Memo varchar2(200)) on Commit Preserve Rows ;

    2）事務級臨時表是指該臨時表與事務相關，當進行事務提交或者事務回滾的時候，臨時表中的數據將自行被截斷，其他的內容和會話級的臨時表的一致（包括退出SESSION的時候，事務級的臨時表也會被自動截斷）。事務級臨時表的創建方法：Create Global Temporary Table Table_Name(Col1 Type1,Col2 Type2...) On Commit Delete Rows；
舉例：create global temporary table Classes(Class_id Number(5),Class_Name Varchar2(8),Class_Memo varchar2(200)) on Commit delete Rows ;

    3）、兩種不通類型的臨時表的區別：語法上，會話級臨時表採用on commit preserve rows而事務級則採用on commit delete rows；
用法上，會話級別只有當會話結束臨時表中的數據才會被截斷，而且事務級臨時表則不管是commit、rollback或者是會話結束，臨時表中的數據都將被截斷。

3、例子：
    1）、會話級（Session關閉掉之後數據就沒有了，當Commit的時候則數據還在，當Rollback的時候則數據也是一樣被回滾）：
     insert into student(stu_id,class_id,stu_name,stu_memo) values(1,1,'張三','福建');
     insert into student(stu_id,class_id,stu_name,stu_memo) values(2,1,'劉德華','福州');
     insert into student(stu_id,class_id,stu_name,stu_memo) values(3,2,'S.H.E','廈門');
SQL> select *from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- --------------------------------------------------------------------------------
     1        1 張三     福建
     2        1 劉德華   福州
     3        2 S.H.E    廈門
     4        2 張惠妹   廈門

SQL> commit;

Commit complete

SQL> select * from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- --------------------------------------------------------------------------------
     1        1 張三     福建
     2        1 劉德華   福州
     3        2 S.H.E    廈門
     4        2 張惠妹   廈門

SQL>insert into student(stu_id,class_id,stu_name,stu_memo) values(4,2,'張惠妹','廈門');

1 row inserted

SQL> select * from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- --------------------------------------------------------------------------------
     1        1 張三     福建
     2        1 劉德華   福州
     3        2 S.H.E    廈門
     4        2 張惠妹   廈門
     4        2 張惠妹   廈門

SQL> rollback ;

Rollback complete

SQL> select * from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- --------------------------------------------------------------------------------
     1        1 張三     福建
     2        1 劉德華   福州
     3        2 S.H.E    廈門
     4        2 張惠妹   廈門

SQL>
    2）、事務級（Commit之後就刪除數據）：本例子將採用以下的數據：
      insert into classes(Class_id,Class_Name,Class_Memo) values(1,'計算機','9608');
      insert into classes(Class_id,Class_Name,Class_Memo) values(2,'經濟資訊','9602');
      insert into classes(Class_id,Class_Name,Class_Memo) values(3,'經濟資訊','9603');
  在一個SESSION中（比如SQLPLUS登陸）插入上面3條記錄，然後再以另外一個SESSION（用SQLPLUS再登陸一次）登陸，當你select * from classes;的時候，classes表是空的，而你再第一次登陸的SQLPLUS中select的時候可以查詢到，這個時候你沒有進行commit或者rollback之前你可以對剛才插入的3條記錄進行update、delete等操作，當你進行commit或者rollback的時候，這個時候由於你的表是事務級的臨時表，那麼在插入數據的session也看不到數據了，這個時候數據就已經被截斷了。
     運行結果如下：
SQL> insert into classes(Class_id,Class_Name,Class_Memo) values(1,'計算機','9608');

1 row inserted

SQL> insert into classes(Class_id,Class_Name,Class_Memo) values(2,'經濟資訊','9602');

1 row inserted

SQL> insert into classes(Class_id,Class_Name,Class_Memo) values(3,'經濟資訊','9603');

1 row inserted

SQL> update classes set class_memo ='' where class_id=3 ;

1 row updated

SQL> select * from classes ;

CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- --------------------------------------------------------------------------------
       1 計算機     9608
       2 經濟資訊   9602
       3 經濟資訊  

SQL> delete from classes where class_id=3 ;

1 row deleted

SQL> select * from classes ;

CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- --------------------------------------------------------------------------------
       1 計算機     9608
       2 經濟資訊   9602
SQL> commit;

Commit complete

SQL> select *from classes ;

CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- --------------------------------------------------------------------------------

SQL>
再重復插入一次，然後rollback。
SQL> Rollback ;

Rollback complete

SQL> select * from classes ;

CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- --------------------------------------------------------------------------------

SQL>

4、臨時表的應用
    1）、當某一個SQL語句關聯的表在2張及以上，並且和一些小表關聯。可以採用將大表進行分拆並且得到比較小的結果集合存放在臨時表中。
    2）、程式執行過程中可能需要存放一些臨時的數據，這些數據在整個程式的會話過程中都需要用的等等。
5、注意事項：
    1）、臨時表的索引以及對表的修改、刪除等和正常的表是一致的。
    2）、Oracle的臨時表是Oracle8i才支援的功能特性，如果你的Oracle版本比較低的話，那麼就可能沒有辦法用到了，如果你的Oracle版本是8i的話，你還需要把$ORACLE_HOME/admin/${ORACLE_SID}/pfile目錄下的init<ORACLE_SID>.ora初始參數設定檔的compatible修改為compatible = "8.1.0"，我的服務器上就是這樣子配置的。當然也可以修改為compatible = "8.1.6"

以上是我在對大表進行優化的時候採用的一些手段，效果顯著。