Database Reference
In-Depth Information
In summary, you see that the
optimizer_index_cost_adj
initialization parameter has a direct impact on the
I/O cost of an index access. When it's set to a value less than the default, all costs decrease proportionally. In some
cases this might be a problem because the query optimizer rounds off the results of its estimations. This means that,
even if the object statistics of several indexes are different, they may have the same cost as far as the query optimizer is
concerned. If several costs are equal in value, the query optimizer decides based on the name of the indexes! It simply
takes the first one in alphabetical order. This problem is demonstrated in the following example. Notice how the index
used for the
INDEX RANGE SCAN
operation changes when the
optimizer_index_cost_adj
initialization parameter and
the index name changes. This is an excerpt of the output generated by the
optimizer_index_cost_adj.sql
script:
SQL> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ =
100
;
SQL> SELECT * FROM t WHERE val1 = 11 AND val2 = 11;
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN |
T_VAL2_I
|
------------------------------------------------
1 - filter("VAL1"=11)
2 - access("VAL2"=11)
SQL> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ =
10
;
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN |
T_VAL1_I
|
------------------------------------------------
1 - filter("VAL2"=11)
2 - access("VAL1"=11)
SQL> ALTER INDEX
t_val1_i
RENAME TO
t_val3_i
;
SQL> SELECT * FROM t WHERE val1 = 11 AND val2 = 11;
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN |
T_VAL2_I
|
------------------------------------------------
1 - filter("VAL1"=11)
