CBO对于Cost值相同的索引的选择

Posted: April 18, 2013 | Author: Cui Hua | Filed under: Oracle | 4 Comments »

这里我们稍微讨论一下CBO对于Cost值相同的索引的选择，可能会有朋友认为在同样Cost的情况下，Oracle会按照索引名的字母顺序来选择索引，实际上并不完全是这样，CBO对于Cost值相同的索引的选择和Oracle的版本有关。

   

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

   

这意味着对于Oracle 10gR2及其以上的版本，CBO对于Cost值相同的索引的选择实际上会这样：

1、    如果Cost值相同的索引的叶子块数量不同，则Oracle会选择叶子块数量较少的那个索引；

2、    如果Cost值相同的索引的叶子块数量相同，则Oracle会选择索引名的字母顺序在前面的那个索引。

 

这个非常容易验证，我们来看一个实例：

在一个11.2.0.3的环境中创建一个测试表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

 

对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上创建名为a_idx_t1和b_idx_t1的B树索引：

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

 

对表T1收集一下统计信息：

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

 

PL/SQL procedure successfully completed

 

此时索引a_idx_t1和b_idx_t1的统计信息显然是完全一致的（这意味着走这两个索引的同类型执行计划的Cost值会相同），从如下查询结果中我们可以看到，它们的叶子块的数量均为185：

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

 

INDEX_NAME                     LEAF_BLOCKS

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

A_IDX_T1                               185

B_IDX_T1                               185

 

在当前情形下，如果我们执行目标SQL“select * from t1 where object_id=1000 and object_id_1=1000”，显然此时Oracle既可以走索引a_idx_t1，也可以走索引b_idx_t1。

 

从如下查询结果中我们可以看到，此时Oracle选择了走索引a_idx_t1：

SQL> set autotrace traceonly explain

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

 

执行计划

———————————————————-

Plan hash value: 3036955902

 

—————————————————————————————-

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————————-

|   0 | SELECT STATEMENT            |          |     1 |   103 |     2   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |   103 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | A_IDX_T1 |     1 |       |     1   (0)| 00:00:01 |

—————————————————————————————-

 

Predicate Information (identified by operation id):

—————————————————

 

   1 – filter(“OBJECT_ID_1″=1000)

   2 – access(“OBJECT_ID”=1000)

    这就验证了我们之前提到的结论——对于Oracle 10gR2及其以上的版本，如果Cost值相同的索引的叶子块数量相同，则Oracle会选择索引名的字母顺序在前面的那个索引。

   

现在我们把索引b_idx_t1的叶子块数量从之前的185改为现在的184：

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

 

PL/SQL procedure successfully completed

 

从如下查询结果中我们可以看到，上述改动生效了：

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

 

INDEX_NAME                     LEAF_BLOCKS

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

A_IDX_T1                               185

B_IDX_T1                               184

 

然后我们再次执行上述目标SQL：

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

 

执行计划

———————————————————-

Plan hash value: 606792237

 

—————————————————————————————-

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————————-

|   0 | SELECT STATEMENT            |          |     1 |   103 |     2   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |   103 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | B_IDX_T1 |     1 |       |     1   (0)| 00:00:01 |

—————————————————————————————-

 

Predicate Information (identified by operation id):

—————————————————

 

   1 – filter(“OBJECT_ID”=1000)

   2 – access(“OBJECT_ID_1″=1000)

        从上述显示内容中我们可以看到，上述SQL的执行计划从之前的走对索引a_idx_t1的索引范围扫描变为了现在的走对索引b_idx_t1的索引范围扫描，这就验证了我们之前提到的结论——对于Oracle 10gR2及其以上的版本，如果Cost值相同的索引的叶子块数量不同，则Oracle会选择叶子块数量较少的那个索引。

 

    至于RBO对于Cost值相同的索引的选择，我在我新书《基于Oracle的SQL优化》的“第一章 Oracle里的优化器”中会详细描述，这里不再赘述。

---