14-多表連線、索引選擇、顯式遊標和其他

安佰勝發表於2010-10-20
索引


14-多表連線、索引選擇、顯式遊標和其他

==============

1、連線多個掃描

如果你對一個列和一組有限的值進行比較，優化器可能執行多次掃描並對結果進行合併連線

例：

SQL> select count(*) from an1 where name in('anbaisheng','xiangxiang') ;

  COUNT(*)
----------
     20000

已用時間:  00: 00: 00.01

執行計劃
----------------------------------------------------------
Plan hash value: 3815355493

---------------------------------------------
| Id  | Operation          | Name           |
---------------------------------------------
|   0 | SELECT STATEMENT   |                |
|   1 |  SORT AGGREGATE    |                |
|   2 |   CONCATENATION    |                |
|*  3 |    INDEX RANGE SCAN| INDEX_AN1_NAME |
|*  4 |    INDEX RANGE SCAN| INDEX_AN1_NAME |
---------------------------------------------

oracle可能會將他轉換成以下語句

SQL> select count(*) from an1 where name='anbaisheng' or name='xiangxiang';

  COUNT(*)
----------
     20000

已用時間:  00: 00: 00.01

執行計劃
----------------------------------------------------------
Plan hash value: 3815355493

---------------------------------------------
| Id  | Operation          | Name           |
---------------------------------------------
|   0 | SELECT STATEMENT   |                |
|   1 |  SORT AGGREGATE    |                |
|   2 |   CONCATENATION    |                |
|*  3 |    INDEX RANGE SCAN| INDEX_AN1_NAME |
|*  4 |    INDEX RANGE SCAN| INDEX_AN1_NAME |
---------------------------------------------

當選擇執行路徑時，優化器可能對每一個條件採用loding$vmanager上的索引範圍掃描
返回的rowid用來訪問lodging表的記錄
最後兩組記錄以連線的形式被組合成一個單一的集合

-------------------------------

2、CBO下使用更具選擇性的索引

基於成本的優化器對索引的選擇性進行判斷來決定所以你的使用是否能提高效率

如果索引有很高的選擇性, 那就是說對於每個不重複的索引鍵值,只對應數量很少的記錄

比如, 表中共有100條記錄而其中有80個不重複的索引鍵值. 這個索引的選擇性就是80/100 = 0.8
 選擇性越高, 通過索引鍵值檢索出的記錄就越少.
 
如果索引的選擇性很低, 檢索資料就需要大量的索引範圍查詢操作和ROWID
訪問表的操作. 也許會比全表掃描的效率更低.

a. 如果檢索資料量超過30%的表中記錄數.使用索引將沒有顯著的效率提高.

b. 在特定情況下, 使用索引也許會比全表掃描慢, 但這是同一個數量級上的區別.
 而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍
 
 ----------------------------------
 
 3、避免使用消耗資源的操作
 
帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎執行耗費資源的排序(SORT)功能.
DISTINCT需要一次排序操作, 而其他的至少需要執行兩次排序.

例如,一個UNION查詢,其中每個查詢都帶有GROUP BY子句, GROUP BY會觸發嵌入排序(NESTED SORT)
這樣, 每個查詢需要執行一次排序, 然後在執行UNION時, 又一個唯一排序(SORT UNIQUE)操作被執行而且它只能在前面的嵌入排序結束後才能開始執行.
嵌入的排序的深度會大大影響查詢的效率.

通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫.

另外：
如果你的資料庫的SORT_AREA_SIZE調配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強


------------------------------------

4、優化group by

提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉.
下面兩個查詢返回相同結果但第二個明顯就快了許多.

低效:

SELECT JOB , AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER'

高效:

SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP JOB

------------------------------------

5、使用顯式遊標

使用隱式的遊標,將會執行兩次操作.
第一次檢索記錄, 第二次檢查TOO MANY ROWS 這個exception .
而顯式遊標不執行第二次操作.

------------------------------------

6、優化exp和imp

使用較大的buffer可以提高exp和imp的速度

------------------------------------

7、分離索引和表

將表和索引建立在不同的表空間
如果可以控制，最好放在不同的磁碟上
(俺覺得過時了)

 

 


 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13177610/viewspace-676350/，如需轉載，請註明出處，否則將追究法律責任。

相關文章