开发学院数据库Oracle 何时Oracle使用绑定变量性能反而更差 阅读

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

 2007-05-09 12:12:50 来源:WEB开发网   
核心提示: 我们看下索引扫描的代价是多少:SQL> show parameter OPTIMIZER_INDEX_COST_ADJNAME TYPEVALUE --- --optimizer_index_cost_adj integer 100SQL> delete from plan_t

我们看下索引扫描的代价是多少:SQL> show parameter OPTIMIZER_INDEX_COST_ADJ
NAME                 TYPE    VALUE
------------------------------------ ----------- ------
optimizer_index_cost_adj       integer   100
SQL> delete from plan_table;
0 rows deleted.
SQL>
SQL> explain plan for select
/*+index(a T_PEEKING_IDX1)*/ * from T_PEEKING a where b = :V;
Explained.
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
 7 ;
Query
Plan_Table
-----------------------------------------------------
SELECT STATEMENT  Cost=113
 TABLE ACCESS BY INDEX ROWID T_PEEKING
  INDEX RANGE SCAN T_PEEKING_IDX1
SQL>

再看全表扫描的代价是多少:SQL> delete from plan_table;
3 rows deleted.
SQL>
SQL> explain plan for select
/*+full(a)*/ * 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
 7 ;
Query
Plan_Table
----------------------------------------------------
SELECT STATEMENT  Cost=75
 TABLE ACCESS FULL T_PEEKING
SQL>

上一页  1 2 3 4 5 6 7  下一页

Tags:何时 Oracle 使用

编辑录入:爽爽 [复制链接] [打 印]
[]
  • 好
  • 好的评价 如果觉得好,就请您
      0%(0)
  • 差
  • 差的评价 如果觉得差,就请您
      0%(0)
赞助商链接