Oracle臨時表優化查詢速度

2024-08-29 13:28:45

字體：大中小

來源：轉載

供稿：網友

商業源碼熱門下載www.html.org.cn

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"

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

上一篇：解讀Oracle 9201的控制文件

下一篇：oracle數據庫開發的一些經驗積累（一）