首先要聲明兩個知識點
（）RBO&CBO
Oracle有兩種執行優化器一種是RBO(Rule Based Optimizer)基於規則的優化器這種優化器是基於sql語句寫法選擇執行路徑的另一種是CBO（Cost Based Optimizer）基於規則的優化器這種優化器是Oracle根據統計分析信息來選擇執行路徑如果表和索引沒有進行分析Oracle將會使用RBO代替CBO如果表和索引很久未分析CBO也有可能選擇錯誤執行路徑不過CBO是Oracle發展的方向自i版本來已經逐漸取代RBO
（）AUTOTRACE
要看索引是否被使用我們要借助Oracle的一個叫做AUTOTRACE功能它顯示了sql語句的執行路徑我們能看到Oracle內部是怎麼執行sql的這是一個非常好的輔助工具在sql調優裡廣泛被運用我們來看一下怎麼運用AUTOTRACE
① 由於AUTOTRACE自動為用戶指定了Execution Plan因此該用戶使用AUTOTRACE前必須已經建立了PLAN_TABLE如果沒有的話請運行utlxplansql腳本（它在$ORACLE_HOME/rdbms/admin目錄中）
② AUTOTRACE可以通過運行plustrcesql腳本(它在$ORACLE_HOME/sqlplus/admin目錄中)來設置用sys用戶登陸然後運行plustrcesql後會建立一個PLUSTRACE角色然後給相關用戶授予PLUSTRACE角色然後這些用戶就可以使用AUTOTRACE功能了
③ AUTOTRACE的默認使用方法是set autotrace on但是這方法不總是適合各種場合特別當返回行數很多的時候Set autotrace traceonly提供了只查看統計信息而不查詢數據的功能
SQL> set autotrace on
SQL> select * from test;
A
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE
TABLE ACCESS (FULL) OF TEST
Statistics
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
SQL> set autotrace traceonly
SQL> select * from testtest;
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE
TABLE ACCESS (FULL) OF TEST
Statistics
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
Hints是Oracle提供的一個輔助用法按字面理解就是提示的意思確實它起得作用也是提示優化器按它所提供的關鍵字來選擇執行路徑特別適用於sql調整的時候使用方法如下
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]] */
具體可參考Oracle SQL Reference
有了前面這些知識點接下來讓我們來看一下什麼時候索引是不起作用的以下列出幾種情況
（）類型不匹配時
SQL> create table testtestindex (a varchar()b number);
表已創建
SQL> create index ind_cola on testtestindex(a);
索引已創建
SQL> insert into testtestindex values();
已創建 行
SQL> commit;
提交完成
SQL> analyze table testtestindex compute statistics for all indexes;
表已分析
SQL> set autotrace on;
SQL> select /*+RULE */* FROM testtestindex where a=;(使用基於rule的優化器數據類型匹配的情況下)
A B
Execution Plan
SELECT STATEMENT Optimizer=HINT: RULE
TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX
INDEX (RANGE SCAN) OF IND_COLA (NONUNIQUE)（使用了索引ind_cola）
――――――――――――――――――――――――――――――――――
SQL> select /*+RULE */* FROM testtestindex where a=;（數據類型不匹配的情況）
A B
Execution Plan
SELECT STATEMENT Optimizer=HINT: RULE
TABLE ACCESS (FULL) OF TESTINDEX（優化器選擇了全表掃描）
（）條件列包含函數但沒有創建函數索引
SQL> select /*+ RULE */* FROM testtestindex where upper(a)= A;（使用了函數upper()在列a上）;
A B
a
Execution Plan
SELECT STATEMENT Optimizer=HINT: RULE
TABLE ACCESS (FULL) OF TESTINDEX（優化器選擇全表掃描）
創建基於函數的索引
SQL> create index testind_fun on testtestindex(upper(a));
索引已創建
SQL> insert into testindex values(a);
已創建行
SQL> commit;
提交完成
SQL> select /*+ RULE*/* FROM testtestindex where upper(a)=A;
A B
a
Execution Plan
SELECT STATEMENT Optimizer=HINT: RULE
TABLE ACCESS (FULL) OF TESTINDEX
(在RULE優化器下忽略了函數索引選擇了全表掃描)
SQL> select * FROM testtestindex where upper(a)
=A;
A B
a
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX (Cost= Card=
Bytes=)
INDEX (RANGE SCAN) OF IND_FUN (NONUNIQUE) (Cost= Car
d=)(CBO優化器使用了ind_fun索引)
（）復合索引中的前導列沒有被作為查詢條件
創建一個復合索引
SQL> create index ind_com on testtestindex(ab);
索引已創建
SQL> select /*+ RULE*/* from testtestindex where a=;
A B
Execution Plan
SELECT STATEMENT Optimizer=HINT: RULE
INDEX (RANGE SCAN) OF IND_COM (NONUNIQUE)(條件列表包含前導列時使用索引ind_com)
SQL> select /*+ RULE*/* from testtestindex where b=;
未選定行
Execution Plan
SELECT STATEMENT Optimizer=HINT: RULE
TABLE ACCESS (FULL) OF TESTINDEX(條件列表不包括前導列是選擇全表掃描)
（）CBO模式下選擇的行數比例過大優化器采取了全表掃描
SQL> select * from testtestindex where a=;
A B
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
TABLE ACCESS (FULL) OF TESTINDEX (Cost= Card= Bytes=)
（表一共行選擇比例為%所以優化器選擇了全表掃描）
――――――――――――――――――――――――――――――――――
下面增加表行數
SQL> declare i number;
begin
for i in loop
insert into testtestindex values (to_char(i)i);
end loop;
end;
/
PL/SQL 過程已成功完成
SQL> commit;
提交完成
SQL> select count(*) from testtestindex;
COUNT(*)
SQL> select * from testtestindex where a=;
A B
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
INDEX (RANGE SCAN) OF IND_COM (NONUNIQUE) (Cost= Card= Bytes=)
（表一共行選擇比例為/=%所以優化器選擇了索引掃描）
（）CBO模式下表很久沒分析表的增長明顯優化器采取了全表掃描
SQL> select * from testtestindex where a like %;
A B
已選擇行
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
TABLE ACCESS (FULL) OF TESTINDEX (Cost= Card= Bytes=)
(表一共行選擇比例為/>%優化器選擇了全表掃描)
――――――――――――――――――――――――――――――――――
增加表行數
SQL> declare i number;
begin
for i in loop
insert into testtestindex values (to_char(i)i);
end loop;
end;
/
PL/SQL 過程已成功完成
SQL> commit;
提交完成
SQL> select count(*) from testtestindex;
COUNT(*)
SQL> select * from testtestindex where a like %;
A B
已選擇行
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
TABLE ACCESS (FULL) OF TESTINDEX (Cost= Card= Bytes=)
(表一共行選擇比例為/<%優化器選擇了全表掃描選擇路徑是錯誤的)
―――――――――――――――――――――――――――――
給表做分析
SQL> analyze table testtestindex compute statistics for table for all indexed c
olumns for all indexes;
表已分析
SQL> select * from testtestindex where a like %;
A B
已選擇行
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=)
TABLE ACCESS (BY INDEX ROWID) OF TESTINDEX (Cost= Card=
Bytes=)
INDEX (RANGE SCAN) OF IND_COLA (NONUNIQUE) (Cost= Ca
rd=)
（經過分析後優化器選擇了正確的路徑使用了ind_cola索引）
From:http://tw.wingwit.com/Article/program/Oracle/201311/17270.html
