This is continuation of my previous post : http://vkoracle.blogspot.com/2012/08/query-not-using-index-1-of-4.html
OPTIMIZER_INDEX_COST_ADJ :
The following is from Oracle documentation :
http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams144.htm
"OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal."
You can set this paramter at session or system level. Example:
SQL> set autotrace trace explain;
SQL> select * from test where id=3;
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33 | 462 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 33 | 462 | 2 (0)|
| 2 | INDEX RANGE SCAN | IDX_TEST_ID | 33 | | 1 (0)|
--------------------------------------------------------------------------------
SQL> alter session set optimizer_index_cost_adj =100;
Session altered.
SQL> select * from test where id=1;
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 476 | 2 (0)|
| 1 | TABLE ACCESS FULL| TEST | 34 | 476 | 2 (0)|
---------------------------------------------------------------
SQL> select * from test where id=2;
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 546 | 2 (0)|
| 1 | TABLE ACCESS FULL| TEST | 39 | 546 | 2 (0)|
---------------------------------------------------------------
SQL> select * from test where id=16;
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 98 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 7 | 98 | 2 (0)|
| 2 | INDEX RANGE SCAN | IDX_TEST_ID | 7 | | 1 (0)|
--------------------------------------------------------------------------------
Now with the same set of data we are telling optimizer that index path is half expensive as full tablescan.
SQL> alter session set optimizer_index_cost_adj =50;
Session altered.
SQL> select * from test where id=1;
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 476 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 34 | 476 | 2 (0)|
| 2 | INDEX RANGE SCAN | IDX_TEST_ID | 34 | | 1 (0)|
--------------------------------------------------------------------------------
Query using index and the same query was doing full table scan.
SQL> select * from test where id=2;
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 546 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 39 | 546 | 2 (0)|
| 2 | INDEX RANGE SCAN | IDX_TEST_ID | 39 | | 1 (0)|
--------------------------------------------------------------------------------
SQL> select * from test where id=16;
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 98 | 1 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 7 | 98 | 1 (0)|
| 2 | INDEX RANGE SCAN | IDX_TEST_ID | 7 | | 1 (0)|
--------------------------------------------------------------------------------
No comments:
Post a Comment