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.ora初始引數配置檔案的compatible修改為compatible = "8.1.0"，我的伺服器上就是這樣子配置的。當然也可以修改為compatible = "8.1.6"

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


[@more@]