In-depth understanding of Oracle indexes (17): Selection of indexes with the same cost value as CBO

The rules are as follows:

The test is as follows:

HR @ orcl> drop table t purge; Table dropped. HR @ orcl> Create Table T as select * From dba_objects; Table created. HR @ orcl> alter table t add (object_id_1 number); table altered. HR @ orcl> Update t set object_id_1 = object_id; 50363 rows updated. HR @ orcl> commit; Commit complete. HR @ orcl> Create index idx_t_a on T (object_id); index created. HR @ orcl> Create index idx_t_ B on T (object_id_1); index created. /* the same statistical information means that the cost values of the same execution plan will be the same */hr @ orcl> exec dbms_stats.gather_table_stats (ownname => 'hr', tabname => 'T ', estimate_percent => 100, cascade => true, no_invalidate => false); PL/SQL procedure successfully completed. /* The number of leaf blocks is the same, and CBO is indexed alphabetically */hr @ orcl> select index_name, leaf_blocks from user_indexes where table_name = 'T' and index_name in ('idx _ t_a', 'idx _ t_ B '); index_name leaf_blocks tables ----------- idx_t_a 111idx_t_ B 111hr @ orcl> set autot trace exphr @ orcl> select * from t where object_id = 1000 and object_id_1 = 1000; execution plan hash value: 1194865126 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | bytes | 0 | SELECT statement | 1 | 98 | 2 (0) | 00:00:01 | * 1 | table access by index rowid | T | 1 | 98 | 2 (0) | 00:00:01 | * 2 | index range scan | idx_t_a | 1 | 1 (0) | 00:00:01 | identified predicate information (identified by Operation ID ): ------------------------------------------------- 1-filter ("object_id_1" = 1000) 2-access ("object_id" = 1000) /* change the number of leaf blocks in idx_t_ B from 111 to 110 */hr @ orcl> set autot offhr @ orcl> exec dbms_stats.set_index_stats (ownname => 'hr ', indname = & gt; 'idx _ t_ B ', numlblks = & gt; 110); PL/SQL procedure successfully completed. HR @ orcl> select index_name, leaf_blocks from user_indexes where table_name = 'T' and index_name in ('idx _ t_a', 'idx _ t_ B '); index_name leaf_blocks tables ----------- idx_t_a 111idx_t_ B 110/* index with the same cost value and a smaller number of leaf blocks selected by CBO */hr @ orcl> set autot trace exphr @ orcl> select * from t where object_id = 1000 and object_id_1 = 1000; execution Plan -------------------------------------------------------- plan hash value: 3073359464 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | period | 0 | SELECT statement | 1 | 98 | 2 (0) | 00:00:01 | * 1 | table access by index rowid | T | 1 | 98 | 2 (0) | 00:00:01 | * 2 | index range scan | idx_t_ B | 1 | 1 (0) | 00:00:01 | identified predicate information (identified by Operation ID ): ------------------------------------------------- 1-filter ("object_id" = 1000) 2-access ("object_id_1" = 1000)

By David Lin
20113-06-05
Good luck