ORACLE cbo成本计算

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (Cost=2 Card=1 By
tes=103)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST1_N1' (NON-UNIQUE) (Cost=
SQL> declare
2 i number;
3 begin
4 i:=1;
5 for j in 1..5000 loop
6 i:=mod(j,250);
7 insert into test2 values(i,'test');
8 insert into test3 values(i);
1 0 TABLE ACCESS (FULL) OF 'TEST2' (Cost=9 Card=20 Bytes=2060)
SQL> select /*+ index (test2 IDX_TEST2_N1)*/ * from test2 where n1 = 100;
Execution Plan
TEST1 5000 1 1 76
TEST2 5000 1 20 5000
表已创建。
SQL> create table test3(n1 number(10));
表已创建。
begin
for i in 1..5000 loop
insert into test1 values(i,'test');
end loop;
end;
/
PL/SQL 过程已成功完成。
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=20 Bytes=60)
1 0 INDEX (RANGE SCAN) OF 'IDX_TEST2_N1' (NON-UNIQUE) (Cost=1
select /*+ rule */ * from test2 where n1 = 100;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=20 Bytes=2060
)
Bytes=2060)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST2_N1' (NON-UNIQUE) (Cost=
1 Card=20)
如果通过索引访问：
cost计算：从t2的索引统计信息中可以看出，idx_test2_n1的l_blocks为1，d_blocks为20，cost＝1+20=21，其中索引叶块物理读取cost为1，数据块物理读取cost为20
当然，在oracle 920 开始，对于cluster_factor 比较接近表块数量的根据索引的大范围查询做了特别的处理，不再是读一个索引记录去搜索一个表记录了，而是成批处理(通过索引块一批rowid一次去表块中获得一批记录)，这样就大大节约了读的成本( consistent gets)。
SQL> set autotrace trace exp
SQL> conn test/test
已连接。
SQL> @D:\oracle\ora92\rdbms\admin\utlxplan.sql
表已创建。
SQL> create table test1(n1 number(10),c1 char(100));
表已创建。
SQL> create table test2(n1 number(10),c1 char(100));
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=20 Bytes=60)
1 0 INDEX (RANGE SCAN) OF 'IDX_TEST3_N1' (NON-UNIQUE) (Cost=1
TEST3 12 5000
SQL> select table_name,num_rows,avg_leaf_blocks_per_key l_blocks,
2 avg_data_blocks_per_key d_blocks,
3 clustering_factor cl_fac
4 from user_indexes;
TABLE_NAME NUM_ROWS L_BLOCKS D_BLOCKS CL_FAC
------------------------------ ---------- ---------- ---------- ----------
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST2'
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST2_N1' (NON-UNIQUE)
SQL> select * from test3 where n1 = 100;
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=21 Card=20 Bytes=206
0)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST2' (Cost=21 Card=20
SQL> select * from test1 where n1 = 100;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=103)
1 Card=1)
cost计算：从t1的索引统计信息中得知，idx_test1_n1的l_blocks，d_blocks均为1，cost＝1+1=2，索引叶块物理读取cost为1，数据块物理读取cost为1
SQL> select * from test2 where n1 = 100;
9 end loop;
10 end;
11 /
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> create index idx_test1_n1 on test1(n1);
索引已创建。
SQL> create index idx_test2_n1 on test2(n1);
这样计算下来，clustering_factor 应该是介于 表 block数量 和 表记录数之间 的一个值,若 clustering_factor 接近 表block数量，则说明表中数据具有比较好的跟索引字段一样排序顺序的存储，通过索引进行 range scan 的代价比较小（需要读取的表块可能比较少）,若clustering_factor 接近 row数量，则说明表中数据和索引字段排序顺序差异很大，杂乱无张。则通过索引进行 range scan 的代价比较大（需要读取的表块可能更多）。
clustering_factor 是表征表中数据的存储顺序和某索引字段顺序的符合程度。
oracle 按照索引块中存储的rowid 来识别相临的索引中记录 在表block中是否为相同块,如果索引中存在记录 rowid a,b,c,d……,若b 和 a 是同一个block，则比较 c 和 b，若这时不是同一个block，则clustering_factor + 1 ,然后比较 d 和 c，若还不是同一个 block，则clustering_factor + 1 ……
TEST3 5000 1 8 2000
各列的粗略解释每个索引值的平均叶块数目。
avg_data_blocks_per_key d_blocks：每个索引值的平均数据块数目。
------------------------------ ---------- ----------
PLAN_TABLE
TEST1 76 5000
TEST2 76 5000
Card=20 Bytes=60)
cost计算：从t3的索引统计信息，idx_test2_n1的l_blocks为1，d_blocks为15，但因为无需访问表,故cost＝1，索引叶块物理读取cost为1
如果单纯是选择索引列的话,不需要访问表数据块.如下例所示:
Execution Plan
索引已创建。
SQL> create index idx_test3_n1 on test3(n1);