索引什么時候不工作
2024-07-21 02:38:06
供稿：網友
首先要聲明兩個知識點： （1）RBO&CBO。Oracle有兩種執行優化器，一種是RBO(Rule Based Optimizer)基于規則的優化器，這種優化器是基于sql語句寫法選擇執行路徑的；另一種是CBO（Cost Based Optimizer）基于規則的優化器，這種優化器是Oracle根據統計分析信息來選擇執行路徑，假如表和索引沒有進行分析，Oracle將會使用RBO代替CBO；假如表和索引很久未分析，CBO也有可能選擇錯誤執行路徑，不過CBO是Oracle發展的方向，自8i版本來已經逐漸取代RBO.（2）AUTOTRACE。要看索引是否被使用我們要借助Oracle的一個叫做AUTOTRACE功能,它顯示了sql語句的執行路徑，我們能看到Oracle內部是怎么執行sql的，這是一個非常好的輔助工具，在sql調優里廣泛被運用。我們來看一下怎么運用AUTOTRACE：① 由于AUTOTRACE自動為用戶指定了Execution Plan,因此該用戶使用AUTOTRACE前必須已經建立了PLAN_TABLE。假如沒有的話，請運行utlXPlan.sql腳本（它在$ORACLE_HOME/rdbms/admin目錄中）。② AUTOTRACE可以通過運行plustrce.sql腳本(它在$ORACLE_HOME/sqlplus/admin目錄中)來設置，用sys用戶登陸然后運行plustrce.sql后會建立一個PLUSTRACE角色，然后給相關用戶授予PLUSTRACE角色，然后這些用戶就可以使用AUTOTRACE功能了。③ AUTOTRACE的默認使用方法是set autotrace on，但是這方法不總是適合各種場合，非凡當返回行數很多的時候。Set autotrace traceonly提供了只查看統計信息而不查詢數據的功能。 SQL> set autotrace on
SQL> select * from test;
A
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE access (FULL) OF 'TEST'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
rows PRocessed
SQL> set autotrace traceonly
SQL> select * from test.test;Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
rows processedHints是Oracle提供的一個輔助用法，按字面理解就是‘提示’的意思，確實它起得作用也是提示優化器按它所提供的要害字來選擇執行路徑，非凡適用于sql調整的時候。使用方法如下：{DELETEINSERTSELECTUPDATE} /*+ hint [text] [hint[text]]... */具體可參考Oracle SQL Reference。有了前面這些知識點，接下來讓我們來看一下什么時候索引是不起作用的。以下列出幾種情況。（1）類型不匹配時。SQL> create table test.testindex (a varchar(2),b number);
表已創建。
SQL> create index ind_cola on test.testindex(a);
索引已創建。
SQL> insert into test.testindex values('1',1);
已創建 1 行。
SQL> commit;
提交完成。
SQL> analyze table test.testindex compute statistics for all indexes;
表已分析。
SQL> set autotrace on;SQL> select /*+RULE */* FROM test.testindex where a='1';(使用基于rule的優化器，數據類型匹配的情況下)
A B
-- ----------
1 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX'
2 1 INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE)（使用了索引ind_cola）
――――――――――――――――――――――――――――――――――
SQL> select /*+RULE */* FROM test.testindex where a=1;（數據類型不匹配的情況）
A B
-- ----------
1 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'（優化器選擇了全表掃描）
（2）條件列包含函數但沒有創建函數索引。SQL> select /*+ RULE */* FROM test.testindex where upper(a)= 'A';（使用了函數upper()在列a上）;
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'（優化器選擇全表掃描）
----------------------------------------------------------
創建基于函數的索引
SQL> create index test.ind_fun on test.testindex(upper(a));
索引已創建。
SQL> insert into testindex values('a',2);
已創建1行。
SQL> commit;
提交完成。
SQL> select /*+ RULE*/* FROM test.testindex where upper(a)='A';
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'
(在RULE優化器下忽略了函數索引選擇了全表掃描)
-----------------------------------------------------------
SQL> select * FROM test.testindex where upper(a)
='A';
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=2 Card=
1 Bytes=5)
2 1 INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car
d=1)(CBO優化器使用了ind_fun索引)（3）復合索引中的前導列沒有被作為查詢條件。創建一個復合索引
SQL> create index ind_com on test.testindex(a,b);
索引已創建。
SQL> select /*+ RULE*/* from test.testindex where a='1';
A B
-- ----------
1 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 INDEX (RANGE SCAN) OF 'IND_COM' (NON-UNIQUE)(條件列表包含前導列時使用索引ind_com)
SQL> select /*+ RULE*/* from test.testindex where b=1;
未選定行
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'(條件列表不包括前導列是選擇全表掃描)
-----------------------------------------------------------
（4）CBO模式下選擇的行數比例過大，優化器采取了全表掃描。
SQL> select * from test.testindex where a='1';
A B
-- ----------
1 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=1 Bytes=5)
（表一共2行，選擇比例為50%,所以優化器選擇了全表掃描）
――――――――――――――――――――――――――――――――――
下面增加表行數
SQL> declare i number;
2 begin
3 for i in 1 .. 100 loop
4 insert into test.testindex values (to_char(i),i);
5 end loop;
6 end;
7 /
PL/SQL 過程已成功完成。
SQL> commit;
提交完成。
SQL> select count(*) from test.testindex;
COUNT(*)
----------
102
SQL> select * from test.testindex where a='1';
A B
---- ----------
1 1
1 2
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)
1 0 INDEX (RANGE SCAN) OF 'IND_COM' (NON-UNIQUE) (Cost=1 Card=1 Bytes=5)
（表一共102行，選擇比例為2/102=2%,所以優化器選擇了索引掃描）（5）CBO模式下表很久沒分析，表的增長明顯，優化器采取了全表掃描。SQL> select * from test.testindex where a like '1%';
A B
---- ----------
1 2
1 1
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
100 100
已選擇13行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52)
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=13 Bytes=52)
(表一共102行，選擇比例為13/102>10%,優化器選擇了全表掃描)
――――――――――――――――――――――――――――――――――
增加表行數
SQL> declare i number;
2 begin
3 for i in 200 .. 1000 loop
4 insert into test.testindex values (to_char(i),i);
5 end loop;
6 end;
7 /
PL/SQL 過程已成功完成。
SQL> commit;
提交完成。
SQL> select count(*) from test.testindex;
COUNT(*)
----------
903
SQL> select * from test.testindex where a like '1%';
A B
---- ----------
1 2
1 1
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
100 100
1000 1000
已選擇14行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52)
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=13 Bytes=52)
(表一共903行，選擇比例為14/903<5%,優化器選擇了全表掃描,選擇路徑是錯誤的)
―――――――――――――――――――――――――――――
給表做分析
SQL> analyze table test.testindex compute statistics for table for all indexed c
olumns for all indexes;
表已分析。
SQL> select * from test.testindex where a like '1%';
A B
---- ----------
1 2
1 1
10 10
100 100
1000 1000
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
已選擇14行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=24 Bytes=120)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=4 Card=
24 Bytes=120)
2 1 INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE) (Cost=2 Ca
rd=24)
（經過分析后優化器選擇了正確的路徑，使用了ind_cola索引）
