Oracle暫存資料表 最佳化查詢速度

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"

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