Oracle 10g(10.1.0.2)中的OPTIMIZER_INDEX_COST_ADJ

Tom Kyte的新书Effective Oracle by Design的第6章 Getting the Most Out of the Cost-Based Optimizer中介绍了参数OPTIMIZER_INDEX_COST_ADJ，并认为可以理解为Oracle执行多块（MultiBlock）I/O（比如全表扫描）的代价与执行单块（Single-block）I/O代价的相对比例。Tom指出，9i下该参数缺省值为100，意即二者拥有相同的代价，若减小，则CBO倾向于使用索引（即单块I/O），反之则倾向于全表扫描（多块I/O）。Tom同样给出了一个不错的例子。
在自己的手提上试验了一下，硬盘大小原因，只装了一个Oracle 10g(10.1.0.2)，谁知就发现该参数在10g下取值发生了改变。我们先来看一下参照Tom的实验：
A. 创建数据表。
SQL> drop table t1;

表已删除。

SQL> drop table t2;

表已删除。

SQL> create table t1
2 as
3 select mod(rownum,1000) id,rpad('x',300,'x') data
4 from all_objects
5 where rownum<=5000;

表已创建。

SQL> ed
已写入 file afiedt.buf

1 create table t2
2 as
3 select rownum id,rpad('x',300,'x') data
4 from all_objects
5* where rownum<=1000
SQL> /

表已创建。

B.创建索引并分析。
SQL> create index idx_t1 on t1(id);

索引已创建。

SQL> create index idx_t2 on t2(id);

索引已创建。

SQL> ed
已写入 file afiedt.buf

1 begin
2 dbms_stats.gather_table_stats
3 (user,'T1',method_opt=>'for all indexed columns',cascade=>true);
4 dbms_stats.gather_table_stats
5 (user,'T2',method_opt=>'for all indexed columns',cascade=>true);
6* end;
SQL> /

PL/SQL 过程已成功完成。

C.查询缺省值，并设置好环境。
SQL> set autot off
SQL> show parameters optimizer_index_cost_adj;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
SQL> set autot traceonly exp stat;

D.在缺省值下查询的结果。
SQL> ed
已写入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已选择30行。

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=5 Bytes=1000
)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=2 Card
=1 Bytes=100)

2 1 NESTED LOOPS (Cost=13 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=3
Card=5 Bytes=500)

4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)

5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)

统计信息
----------------------------------------------------------
367 recursive calls
0 db block gets
101 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQLNet to client
523 bytes received via SQLNet from client
3 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
30 rows processed

E.修改参数值，注意，引用Tom的结论，9i中这个值在0-100之间，而10g呢？
SQL> alter session set optimizer_index_cost_adj=0;
ERROR:
ORA-00068: 值 0 对参数 optimizer_index_cost_adj 无效, 必须在 1 和 10000 之间

SQL> alter session set optimizer_index_cost_adj=1;

会话已更改。

SQL> ed
已写入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已选择30行。

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=1000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=1 Card
=1 Bytes=100)

2 1 NESTED LOOPS (Cost=2 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=1
Card=5 Bytes=500)

4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)

5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQLNet to client
523 bytes received via SQLNet from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed

SQL> alter session set optimizer_index_cost_adj=50;

会话已更改。

SQL> ed
已写入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已选择30行。

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=5 Bytes=1000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=1 Card
=1 Bytes=100)

2 1 NESTED LOOPS (Cost=7 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=2
Card=5 Bytes=500)

4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)

5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)

F.继续修改参数值，改大。
SQL> alter session set optimizer_index_cost_adj=200;

会话已更改。

SQL> ed
已写入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已选择30行。

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=5 Bytes=1000
)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=4 Card
=1 Bytes=100)

2 1 NESTED LOOPS (Cost=26 Card=5 Bytes=1000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (TABLE) (Cost=6
Card=5 Bytes=500)

4 3 INDEX (RANGE SCAN) OF 'IDX_T2' (INDEX) (Cost=2 Card=
5)

5 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)

SQL> alter session set optimizer_index_cost_adj=500;

会话已更改。

SQL> ed
已写入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已选择30行。

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=63 Card=5 Bytes=1000
)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=10 Car
d=1 Bytes=100)

2 1 NESTED LOOPS (Cost=63 Card=5 Bytes=1000)
3 2 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 By
tes=500)

4 2 INDEX (RANGE SCAN) OF 'IDX_T1' (INDEX) (Cost=1 Card=1)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
90 consistent gets
0 physical reads
0 redo size
1507 bytes sent via SQLNet to client
523 bytes received via SQLNet from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed

SQL> alter session set optimizer_index_cost_adj=1000;

会话已更改。

SQL> ed
已写入 file afiedt.buf

1 select * from t1,t2
2 where t1.id=t2.id
3* and t2.id between 50 and 55
SQL> /

已选择30行。

执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=66 Card=5 Bytes=1000
)

1 0 HASH JOIN (Cost=66 Card=5 Bytes=1000)
2 1 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=13 Card=5 Byte
s=500)

3 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=52 Card=26 Byt
es=2600)

统计信息
----------------------------------------------------------
1 recursive calls&nbs