CBO学习之一What do you mean by Cost(二)
for blocking
the index. On my 9.2.0.6 test, the no_merge hint managed to
reduce the lengths of the rows to be sorted, and therefore
calculated a smaller cost.
*/
/*
Block the index with a no_merge hint
*/
select * from (
select /*+ no_merge */
*
from t1
where modded = 0
)
order by
id
;
/*
Block the index with a no_index hint
*/
select /*+ no_index(t1,t1_pk) */
*
from t1
where modded = 0
order by
id
;
set autotrace off
/*
The costs under 8i are:
Using the PK index to avoid the sort: 1,450
Block the PK index (no_index) and sorting: 43
Cost ratio: 1450/43 = 33.729, so we test
_sort_elimination_cost_ratio at 33 and 34
At 33: 43 * 33 = 1,419: so the PK nosort should be ignored
At 34: 43 * 34 = 1,462: so the PK nosort falls inside the limit.
(Because of a change in the cost in 10g, the break point
for the parameter was 32/33 on my 10g system)
*/
set autotrace traceonly explain
alter session set "_sort_elimination_cost_ratio" = 34;
/*
Cost ratio set to 34 - PK path should be accepted
*/
select
*
from t1
where modded = 0
order by
id
;
alter session set "_sort_elimination_cost_ratio" = 33;
/*
Cost ratio set to 33 - PK NOSORT should be too expensive
*/
select
*
from t1
where modded = 0
order by
id
;
set autotrace off
spool off
参数“_sort_elimination_cost_ratio”含义如下（转自http://space.itpub.net/22034023/viewspace-716217）：
create table t1 as select * from dba_objects where object_id is not null;
alter table t1 add constraint t1_pk primary key(object_id);
create index t1_ind on t1(object_type);
alter session set optimizer_mode=first_rows;
alter session set "_sort_elimination_cost_ratio" = 0;
以上脚本创建了一张表，表的主键是object_id,表上有一个普通索引object_type.
set autotrace traceonly
select *
2 from t1
3 where object_type = 'TABLE'
4 order by
5 object_id
6 ;
2060 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=FIRST_ROWS (Cost=276 Card=423 Bytes=36378)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=276 Card=423 Bytes=36378)
2 1 INDEX (FULL SCAN) OF 'T1_PK' (UNIQUE) (Cost=23 Card=10986)
我们看到默认的执行计划走了主键索引的index full scan.算出来的cost是276.
select /*+ no_index(t1,t1_pk) */
2 *
3 from t1
4 where object_type = 'TABLE'
5 order by
6 object_id
7 ;
2060 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=FIRST_ROWS (Cost=46 Card=423 Bytes=36378)
1 0 SORT (ORDER BY) (Cost=46 Card=423 Bytes=36378)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=32 Card=423Bytes=36378)
3 2 INDEX (RANGE SCAN) OF 'T1_IND' (NON-UNIQUE) (Cost=1 Card=423)
通过为语句增加hint的方式，查询计划走了index RANGE scan.这个时候的cost是46.
select 276/46 from dual;
276/46
----------
6
默认的查询计划(INDEX pk full scan)的cost是走object_type索引查询计划的6倍。
我们通过设置_sort_elimination_cost_r