SQL*Plus: Release 9.2.0.3.0 - Production on Mon Jun 28 17:11:15 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production
SQL> show parameter optimizer_index_cost_adj
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_index_cost_adj integer 100 SQL>
创建测试表:
SQL> create table t as select * from dba_objects;
Table created.
SQL> create index ind_owner on t(owner);
Index created.
SQL> analyze table t compute statistics;
Table analyzed.
我们分别观察一下全表扫描和索引访问的成本:
SQL> set autotrace traceonly
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)
SQL> select /*+ index(t ind_owner) */ * from t where owner='EYGLE';
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=476 Bytes=36652) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=16 Card=476 Bytes=36652) 2 1 INDEX (RANGE SCAN) OF 'IND_OWNER' (NON-UNIQUE) (Cost=2 Card=476)