oracle参数解释(二)
表扫描的成本，和全表扫描的COST进行比较.这个转换需要一个转换因子.
就是optimizer_index_cost_adj:
optimizer_index_cost_adj * (Index Scan Cost) = 等价的 Full Scan Cost
这个 等价的 Full Scan Cost 就是来和全表扫描成本进行比较的.
而这个转换因子的临界值实际上就是Full Scan Cost 和 Index Scan Cost的比值.
即:
optimizer_index_cost_adj = Full Scan Cost / Index Scan Cost
SQL> set autotrace off
SQL> select (14/16)*100 from dual;
(14/16)*100
-----------
87.5
1 row selected.
我们通过调整optimizer_index_cost_adj来看一下执行计划的变化:
SQL> set autotrace traceonly
SQL> alter session set optimizer_index_cost_adj = 87;
Session altered.
SQL> select * from t where owner='EYGLE';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=14 Card=476 Bytes=36652)
2 1 INDEX (RANGE SCAN) OF 'IND_OWNER' (NON-UNIQUE) (Cost=2 Card=476)
此时使用索引成本较低.等价全表扫描成本为:
87% * (Index Scan Cost) < Full Scan Cost
此时Oracle选择了索引.
SQL> alter session set optimizer_index_cost_adj = 88;
Session altered.
SQL> select * from t where owner='EYGLE';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=476 Bytes=36652)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=14 Card=476 Bytes=36652)
此时使用索引成本较高.等价全表扫描成本为:
88% * (Index Scan Cost) > Full Scan Cost
所以Oracle选择了全表扫描.