何时Oracle使用绑定变量性能反而更差

核心提示： 这时，我们可以计算得出让优化器使用索引（无提示强制）的OPTIMIZER_INDEX_COST_ADJ值应该< ROUND(COST_FTS/COST_IDX*100) = ROUND(75/113*100) = 66，何时Oracle使用绑定变量性能反而更差(3)，而大于66则会使

这时，我们可以计算得出让优化器使用索引（无提示强制）的OPTIMIZER_INDEX_COST_ADJ值应该< ROUND(COST_FTS/COST_IDX*100) = ROUND(75/113*100) = 66，而大于66则会使用全表扫描：SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=67;
System altered.
SQL>
SQL> delete from plan_table;
2 rows deleted.
SQL>
SQL> explain plan for select * from T_PEEKING a where b = :V;
Explained.
SQL>
SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||
　2　　　　 object_name||' '||decode(id, 0, 'Cost='||position) "Query
　3　Plan_Table"
　4　　　from plan_table
　5　　　start with id = 0
　6　　　connect by prior id = parent_id;
Query
Plan_Table
-----------------------------------------------------------------
SELECT STATEMENT　 Cost=75
　TABLE ACCESS FULL T_PEEKING
SQL>
SQL>
SQL> alter system set OPTIMIZER_INDEX_COST_ADJ=66;
System altered.
SQL>
SQL> delete from plan_table;
2 rows deleted.
SQL>
SQL> explain plan for select * from T_PEEKING a where b = :V;
Explained.
SQL>
SQL> select lpad(' ', 2*(level-1))||operation||' '||options||' '||
　2　　　　 object_name||' '||decode(id, 0, 'Cost='||position) "Query
　3　Plan_Table"
　4　　　from plan_table
　5　　　start with id = 0
　6　　　connect by prior id = parent_id;
Query
Plan_Table
---------------------------------------------------------
SELECT STATEMENT　 Cost=75
　TABLE ACCESS BY INDEX ROWID T_PEEKING
　　INDEX RANGE SCAN T_PEEKING_IDX1