[[163836]]

　　崔華，網(wǎng)名 dbsnake

　　Oracle ACE Director，ACOUG 核心專家

　　編輯手記：感謝崔華授權(quán)我們獨(dú)家轉(zhuǎn)載其精品文章，也歡迎大家向“Oracle”社區(qū)投稿。

　　這里我們稍微討論一下CBO對(duì)于Cost值相同的索引的選擇，可能會(huì)有朋友認(rèn)為在同樣Cost的情況下，Oracle會(huì)按照索引名的字母順序來(lái)選擇索引，實(shí)際上并不完全是這樣，CBO對(duì)于Cost值相同的索引的選擇和Oracle的版本有關(guān)。

　　原理說(shuō)明

　　MOS上文章“Handling of equally ranked (RBO) or costed (CBO) indexes [ID 73167.1]”明確指出——When the CBO detects 2 indexes that cost the same, it makes the decision based on the following:

　　(up to release 9.2.06) indexes ascii name so that index ‘AAA’ would be chosen over index ‘ZZZ’. See Bug 644757

　　(starting with 9.2.0.7 and in 10gR1) bigger NDK for fully matched indexes (not for fast full scans). See Bug 2720661

　　(in 10gR2 and above) index with lower number of leaf blocks. See Bug 6734618

　　這意味著對(duì)于Oracle 10gR2及其以上的版本，CBO對(duì)于Cost值相同的索引的選擇實(shí)際上會(huì)這樣：

　　1-如果Cost值相同的索引的葉子塊數(shù)量不同，則Oracle會(huì)選擇葉子塊數(shù)量較少的那個(gè)索引;

　　2-如果Cost值相同的索引的葉子塊數(shù)量相同，則Oracle會(huì)選擇索引名的字母順序在前面的那個(gè)索引。

　　測(cè)試驗(yàn)證

　　這個(gè)非常容易驗(yàn)證，我們來(lái)看一個(gè)實(shí)例。在一個(gè)11.2.0.3的環(huán)境中創(chuàng)建一個(gè)測(cè)試表T1：

　　Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

　　Connected as nbs

　　SQL> create table t1 as select * from dba_objects;

　　Table created

　　對(duì)T1增加一列object_id_1，并將其值修改成和列object_id的值一致：

　　SQL> alter table t1 add (object_id_1 number);

　　Table altered

　　SQL> update t1 set object_id_1=object_id;

　　83293 rows updated

　　SQL> commit;

　　Commit complete

　　分別在列object_id和列object_id_1上創(chuàng)建名為a_idx_t1和b_idx_t1的B樹(shù)索引：

　　SQL> create index a_idx_t1 on t1(object_id);

　　Index created

　　SQL> create index b_idx_t1 on t1(object_id_1);

　　Index created

　　對(duì)表T1收集一下統(tǒng)計(jì)信息：

　　SQL> exec dbms_stats.gather_table_stats(ownname => ‘NBS’, tabname => ‘T1’, estimate_percent => 100, cascade => TRUE, no_invalidate => false);

　　PL/SQL procedure successfully completed

　　此時(shí)索引a_idx_t1和b_idx_t1的統(tǒng)計(jì)信息顯然是完全一致的(這意味著走這兩個(gè)索引的同類型執(zhí)行計(jì)劃的Cost值會(huì)相同)，從如下查詢結(jié)果中我們可以看到，它們的葉子塊的數(shù)量均為185：

　　SQL> select index_name,leaf_blocks from dba_indexes where table_owner=’NBS’ and table_name=’T1′;

　　INDEX_NAMELEAF_BLOCKS

　　—————————— ———–

　　A_IDX_T1185

　　B_IDX_T1 185

　　在當(dāng)前情形下，如果我們執(zhí)行目標(biāo)SQL：

　　“select * from t1 where object_id=1000 and object_id_1=1000”

　　顯然此時(shí)Oracle既可以走索引a_idx_t1，也可以走索引b_idx_t1。

　　從如下查詢結(jié)果中我們可以看到，此時(shí)Oracle選擇了走索引a_idx_t1：

　　SQL> set autotrace traceonly explain

 

　　SQL> select * from t1 where object_id=1000 and object_id_1=1000;

　　這就驗(yàn)證了我們之前提到的結(jié)論——對(duì)于Oracle 10gR2及其以上的版本，如果Cost值相同的索引的葉子塊數(shù)量相同，則Oracle會(huì)選擇索引名的字母順序在前面的那個(gè)索引。

　　現(xiàn)在我們把索引b_idx_t1的葉子塊數(shù)量從之前的185改為現(xiàn)在的184：

　　SQL> exec dbms_stats.set_index_stats(ownname => ‘NBS’, indname => ‘B_IDX_T1’, numlblks => 184);

　　PL/SQL procedure successfully completed

　　從如下查詢結(jié)果中我們可以看到，上述改動(dòng)生效了：

　　SQL> select index_name,leaf_blocks from dba_indexes where table_owner=’NBS’ and table_name=’T1′;

　　INDEX_NAMELEAF_BLOCKS

　　—————————— ———–

　　A_IDX_T1185

　　B_IDX_T1184

　　然后我們?cè)俅螆?zhí)行上述目標(biāo)SQL：

 

　　SQL> select * from t1 where object_id=1000 and object_id_1=1000;

　　從上述顯示內(nèi)容中我們可以看到，上述SQL的執(zhí)行計(jì)劃從之前的走對(duì)索引a_idx_t1的索引范圍掃描變?yōu)榱爽F(xiàn)在的走對(duì)索引b_idx_t1的索引范圍掃描，這就驗(yàn)證了我們之前提到的結(jié)論：對(duì)于Oracle 10gR2及其以上的版本，如果Cost值相同的索引的葉子塊數(shù)量不同，則Oracle會(huì)選擇葉子塊數(shù)量較少的那個(gè)索引。