Oracle臨時表 優化查詢速度
我對臨時表的理解:在Oracle中建立一張表,這個表不用於其他的什麼功能,主要用於自己的軟體系統一些特有功能才用的,而當你用完之後表中的資料就沒用了。Oracle的臨時表建立之後基本不佔用表空間,如果你沒有指定臨時表(包括臨時表的索引)存放的表空的時候,你插入到臨時表的資料是存放在ORACLE系統的臨時表空間中(TEMP)。
1、前言
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
以上是我在對大表進行優化的時候採用的一些手段,效果顯著。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-85057/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle臨時表最佳化查詢速度Oracle
- Oracle臨時表 最佳化查詢速度Oracle
- 優化sql查詢速度優化SQL
- Oracle下查詢臨時表空間佔用率Oracle
- 優化sql提高查詢速度優化SQL
- oracle 表空間,臨時表空間使用率查詢Oracle
- Oracle效能優化:收縮臨時表空間Oracle優化
- [ORACLE BUG]查詢結果錯誤--臨時表並行Oracle並行
- Oracle in 查詢優化Oracle優化
- 資料庫優化之臨時表優化資料庫優化
- Oracle中用子查詢建立臨時表 並賦值資料Oracle賦值
- ORACLE Temporary Tables臨時表更適合做插入和查詢操作Oracle
- MySQL臨時表的優化方案MySql優化
- SQLServer效能優化之活用臨時表SQLServer優化
- Oracle:優化方法總結(關於連表查詢)Oracle優化
- 利用Redis cache優化app查詢速度實踐Redis優化APP
- oracle臨時表Oracle
- Oracle 臨時表Oracle
- Oracle 12CR2查詢轉換之cursor-duration臨時表Oracle
- oracle 查詢什麼sql佔用臨時段OracleSQL
- ORACLE臨時表和SQLSERVER臨時表異同OracleSQLServer
- 臨時表空間被佔滿的原因查詢
- PHP專案中如何用PDO查詢臨時表?PHP
- mysql處理海量資料時的一些優化查詢速度方法MySql優化
- [Hive]Hive中表連線的優化,加快查詢速度Hive優化
- SQL Server查詢速度慢原因及優化方法SQLServer優化
- 【Database】Oracle10g臨時表空間的管理和優化DatabaseOracle優化
- oracle11g 查詢臨時表空間的使用率和正在使用臨時表空間的使用者Oracle
- Oracle的臨時表Oracle
- oracle 臨時表空間的增刪改查Oracle
- ORACLE 臨時表空間的增刪改查:Oracle
- oracle臨時表空間的增刪改查Oracle
- 優化mysql資料字典表查詢優化MySql
- 資料庫全表查詢之-分頁查詢優化資料庫優化
- 系統優化之---分割槽表加速查詢速度和刪除速度優化
- 臨時表空間的空間使用情況查詢
- mysql千萬級資料量根據索引優化查詢速度MySql索引優化
- 提升網站訪問速度的 SQL 查詢優化技巧網站SQL優化