共計(jì) 6926 個(gè)字符，預(yù)計(jì)需要花費(fèi) 18 分鐘才能閱讀完成。
這篇文章給大家分享的是有關(guān) Oracle 中組合索引怎么用的內(nèi)容。丸趣 TV 小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨丸趣 TV 小編過來(lái)看看吧。
Oracle 中組合索引的使用
關(guān)鍵詞：組合索引 前導(dǎo)列 基于規(guī)則的優(yōu)化器（RBO） 基于成本的優(yōu)化器（CBO） 提示（hint）
在 Oracle 中可以創(chuàng)建組合索引，即同時(shí)包含兩個(gè)或兩個(gè)以上列的索引。在組合索引的使用方面，Oracle 有以下特點(diǎn)：
1、 當(dāng)使用基于規(guī)則的優(yōu)化器（RBO）時(shí)，只有當(dāng)組合索引的前導(dǎo)列出現(xiàn)在 SQL 語(yǔ)句的 where 子句中時(shí)，才會(huì)使用到該索引；
2、 在使用 Oracle9i 之前的基于成本的優(yōu)化器（CBO）時(shí)，只有當(dāng)組合索引的前導(dǎo)列出現(xiàn)在 SQL 語(yǔ)句的 where 子句中時(shí)，才可能會(huì)使用到該索引，這取決于優(yōu)化器計(jì)算的使用索引的成本和使用全表掃描的成本，Oracle 會(huì)自動(dòng)選擇成本低的訪問路徑（請(qǐng)見下面的測(cè)試 1 和測(cè)試 2）；
3、 從 Oracle9i 起，Oracle 引入了一種新的索引掃描方式——索引跳躍掃描（index skip scan），這種掃描方式只有基于成本的優(yōu)化器（CBO）才能使用。這樣，當(dāng) SQL 語(yǔ)句的 where 子句中即使沒有組合索引的前導(dǎo)列，并且索引跳躍掃描的成本低于其他掃描方式的成本時(shí)，Oracle 就會(huì)使用該方式掃描組合索引（請(qǐng)見下面的測(cè)試 3）；
4、 Oracle 優(yōu)化器有時(shí)會(huì)做出錯(cuò)誤的選擇，因?yàn)樗佟奥斆鳌保膊蝗缥覀?SQL 語(yǔ)句編寫人員更清楚表中數(shù)據(jù)的分布，在這種情況下，通過使用提示（hint），我們可以幫助 Oracle 優(yōu)化器作出更好的選擇（請(qǐng)見下面的測(cè)試 4）。
[@more@]
關(guān)于以上情況，我們分別測(cè)試如下：
我們創(chuàng)建測(cè)試表 T，該表的數(shù)據(jù)來(lái)源于 Oracle 的數(shù)據(jù)字典表 all_objects，表 T 的結(jié)構(gòu)如下：
SQL desc t
名稱 是否為空? 類型
—————————————– ——– ———————
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
表中的數(shù)據(jù)分布情況如下：
SQL select object_type,count(*) from t group by object_type;
OBJECT_TYPE COUNT(*)
—————— ———-
CONSUMER GROUP 20
EVALUATION CONTEXT 10
FUNCTION 360
INDEX 69
LIBRARY 20
LOB 20
OPERATOR 20
PACKAGE 1210
PROCEDURE 130
SYNONYM 16100
TABLE 180
TYPE 2750
VIEW 8600
已選擇 13 行。
SQL select count(*) from t;
COUNT(*)
———-
29489
我們?cè)诒?T 上創(chuàng)建如下索引并對(duì)其進(jìn)行分析：
SQL create index indx_t on t(object_type,object_name);
索引已創(chuàng)建。
SQL ANALYZE TABLE T COMPUTE STATISTICS
2 FOR TABLE
3 FOR ALL INDEXES
4 FOR ALL INDEXED COLUMNS
5 /
表已分析。
現(xiàn)在讓我們編寫幾條 SQL 語(yǔ)句來(lái)測(cè)試一下 Oracle 優(yōu)化器對(duì)訪問路徑的選擇：
測(cè)試 1）
SQL set autotrace traceonly
SQL SELECT * FROM T WHERE OBJECT_TYPE= LOB
已選擇 20 行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=20 Bytes=1740)
1 0 TABLE ACCESS (BY INDEX ROWID) OF T (Cost=22 Card=20 Bytes=1740)
2 1 INDEX (RANGE SCAN) OF INDX_T (NON-UNIQUE) (Cost=2 Card=20)
正如我們所期望的，由于使用了組合索引的前導(dǎo)列并且訪問了表中的少量記錄，Oracle 明智地選擇了索引掃描。那么，如果我們?cè)L問表中的大量數(shù)據(jù)時(shí)，Oracle 會(huì)選擇什么樣的訪問路徑呢？請(qǐng)看下面的測(cè)試：
測(cè)試 2）
SQL SELECT * FROM T WHERE OBJECT_TYPE= SYNONYM
已選擇 16100 行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=16100 Bytes=1400700)
1 0 TABLE ACCESS (FULL) OF T (Cost=38 Card=16100 Bytes=1400700)
Statistics
———————————————————-
0 recursive calls
0 db block gets
1438 consistent gets
13 physical reads
0 redo size
941307 bytes sent via SQL*Net to client
12306 bytes received via SQL*Net from client
1075 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16100 rows processed
很明顯，即使使用了組合索引的前導(dǎo)列，但是由于訪問了表中的大量數(shù)據(jù)，Oracle 選擇了不使用索引而直接使用全表掃描，因?yàn)閮?yōu)化器認(rèn)為全表掃描的成本更低，但事實(shí)是不是真的這樣的？我們通過增加提示（hint）來(lái)強(qiáng)制它使用索引來(lái)看看：
SQL SELECT/*+ INDEX (T INDX_T)*/ * FROM T WHERE OBJECT_TYPE= SYNONYM
已選擇 16100 行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16180 Card=16100 Bytes=1400700)
1 0 TABLE ACCESS (BY INDEX ROWID) OF T (Cost=16180 Card=16100 Bytes=1400700)
2 1 INDEX (RANGE SCAN) OF INDX_T (NON-UNIQUE) (Cost=80 Card=16100)
Statistics
———————————————————-
0 recursive calls
0 db block gets
17253 consistent gets
16 physical reads
0 redo size
298734 bytes sent via SQL*Net to client
12306 bytes received via SQL*Net from client
1075 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16100 rows processed
從以上結(jié)果可以看出，在訪問大量數(shù)據(jù)的情況下，使用索引確實(shí)會(huì)導(dǎo)致更高的執(zhí)行成本，這從 statistics 部分的邏輯讀取數(shù)（consistent gets）就可以看出，使用索引導(dǎo)致的邏輯讀取數(shù)是不使用索引導(dǎo)致的邏輯讀的 10 倍還多。因此，Oracle 明智地選擇了全表掃描而不是索引掃描。
下面，讓我們來(lái)看看 where 子句中沒有索引前導(dǎo)列的情況：
測(cè)試 3）
SQL select * from t where object_name= DEPT
已選擇 10 行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=14 Bytes=1218)
1 0 TABLE ACCESS (BY INDEX ROWID) OF T (Cost=29 Card=14 Bytes=1218)
2 1 INDEX (SKIP SCAN) OF INDX_T (NON-UNIQUE) (Cost=14 Card=14)
Statistics
———————————————————-
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
1224 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
OK！由于只查詢了 10 條數(shù)據(jù)，即使沒有使用前導(dǎo)列，Oracle 正確地選擇了索引跳躍掃描。我們?cè)賮?lái)看看如果不使用索引跳躍掃描，該語(yǔ)句的成本：
SQL select/*+ NO_INDEX(T INDX_T)*/ * from t where object_name= DEPT
已選擇 10 行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=14 Bytes=1218)
1 0 TABLE ACCESS (FULL) OF T (Cost=38 Card=14 Bytes=1218)
Statistics
———————————————————-
0 recursive calls
0 db block gets
375 consistent gets
17 physical reads
0 redo size
1224 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
正如我們所料，不使用索引所導(dǎo)致的邏輯讀（375）確實(shí)比使用索引的邏輯讀多（24），達(dá)到 10 倍以上。
繼續(xù)我們的測(cè)試，現(xiàn)在我們來(lái)看看 Oracle 不選擇使用索引的情況：
測(cè)試 4）
SQL select * from t where object_name LIKE DE%
已選擇 180 行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=37 Bytes=3219)
1 0 TABLE ACCESS (FULL) OF T (Cost=38 Card=37 Bytes=3219)
Statistics
———————————————————-
0 recursive calls
0 db block gets
386 consistent gets
16 physical reads
0 redo size
12614 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
180 rows processed
這次只選擇了 180 條數(shù)據(jù)，跟表 T 中總的數(shù)據(jù)量 29489 條相比，顯然只是很小的一部分，但是 Oracle 還是選擇了全表掃描，有 386 個(gè)邏輯讀。這種情況下，如果我們強(qiáng)制使用索引，情況會(huì)怎樣呢？
SQL select/*+ INDEX(T INDX_T)*/ * from t where object_name LIKE DE%
已選擇 180 行。
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=182 Card=37 Bytes=3219)
1 0 TABLE ACCESS (BY INDEX ROWID) OF T (Cost=182 Card=37 Bytes=3219)
2 1 INDEX (FULL SCAN) OF INDX_T (NON-UNIQUE) (Cost=144 Card=37)
Statistics
———————————————————-
0 recursive calls
0 db block gets
335 consistent gets
0 physical reads
0 redo size
4479 bytes sent via SQL*Net to client
624 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
180 rows processed
通過添加提示（hint），我們強(qiáng)制 Oracle 使用了索引掃描（index full scan），執(zhí)行了 335 個(gè)邏輯讀，比使用全表掃描的時(shí)候少了一些。
由此可見，Oracle 優(yōu)化器有時(shí)會(huì)做出錯(cuò)誤的選擇，因?yàn)樗佟奥斆鳌保膊蝗缥覀?SQL 語(yǔ)句編寫人員更清楚表中數(shù)據(jù)的分布，在這種情況下，通過使用提示（hint），我們可以幫助 Oracle 優(yōu)化器作出更好的選擇。
感謝各位的閱讀！關(guān)于“Oracle 中組合索引怎么用”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！
