索引成本计算的基础知识

索引访问路径的工作流程：

1 为定义的索引列准备相关的谓词
2 定位索引的根块
3 沿着索引分支层往下走，一直到叶块，这也是第一个符合谓词条件的索引项 start key
4 遍历整个叶块连表，一直到最后一个能与谓词匹配的索引项 stop key
5 对每一个索引项，确定是否访问表块


有以上可以知道和索引成本相关的3个因素：
1 按降续遍历的分之层数
2 遍历的叶快数目
3 访问过的表块数目


SQL> run;
1* select index_name,blevel,leaf_blocks,status,num_rows from user_indexes where table_owner='SCOTT
'

INDEX_NAME BLEVEL LEAF_BLOCKS STATUS NUM_ROWS
------------------------------ ---------- ----------- -------- ----------
PK_DEPT 0 1 VALID 4
PK_EMP 0 1 VALID 14
INDEX_TEST1 1 245 VALID 49834
IND_UPPER_TEST1 1 245 VALID 49834
IND_IND 0 1 VALID 3
TAI 0 0 VALID 0
TCI 0 0 VALID 0
IDX 0 1 VALID 93
JOB_IDX 0 1 VALID 5
GENDER_IDX 0 1 VALID 1
LOCATION_IDX 0 1 VALID 15

INDEX_NAME BLEVEL LEAF_BLOCKS STATUS NUM_ROWS
------------------------------ ---------- ----------- -------- ----------
AGE_GROUP_IDX 0 1 VALID 4
T2_PK 1 2 VALID 1024

已选择13行。



索引成本的基本公式：

cost=blevel+ceiling(leaf_blocks*effective index selectivity)+ceiling(clusting_factor*effective table selectivity)


第一行：blevel
由于平衡二叉树的原来，根块到每个叶块的距离是相等的，也就是说无论遍历哪个叶块，降序遍历访问的层数是相等的
第二行 ：leaf_blocks*effective index selectivity

表示的遍历的叶块数目

第三行

clusting_factor*effective table selectivity

表示表块访问的数目

下面举例说明：

环境搭建：

create tablespace test datafile '' size 100m extent management local uniform size 8k segment space management manual;

execute dbms_random.seed(0)

drop table t1;

begin
begin execute immediate 'purge recyclebin';
exception when others then null;
end;

begin execute immediate 'begin dbms_stats.delete_system_stats; end;';
exception when others then null;
end;

begin execute immediate 'alter session set "_optimizer_cost_model"=io';
exception when others then null;
end;

end;
/

create table t1
nologging
as
select
trunc(dbms_random.value(0,25)) n1,
rpad('x',40) ind_pad,
trunc(dbms_random.value(0,20)) n2,
lpad(rownum,10,'0') small_vc,
rpad('x',200) padding
from
all_objects
where
rownum <= 10000
;

create index t1_i1 on t1(n1, ind_pad, n2)
nologging
pctfree 91
;

begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/



SQL> select
2 num_rows, distinct_keys,
3 blevel, leaf_blocks, clustering_factor,
4 avg_leaf_blocks_per_key, avg_data_blocks_per_key
5 from
6 user_indexes
7 where table_name = 'T1'
8 and index_name = 'T1_I1'
9 ;

NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PE LOCKS_PER_KEY
---------- ------------- ---------- ----------- ----------------- -------------------------------
10000 500 2 1111 9745 2 19

已选择 1 行。


可以看到当前索引T1_I1

有10000行，500个不同值，blevel 为2 叶块1111，clustering_factor 9745

索引pctfree,只有当创建，重构，或者合并时才有用，并且只能对叶块起作用

SQL> select
2 column_name,
3 num_nulls, num_distinct, density,
4 low_value, high_value
5 from
6 user_tab_columns
7 where table_name = 'T1'
8 and column_name in ('N1','N2','IND_PAD')
9 order by
10 column_name
11 ;

COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY LOW_VALUE HIGH_VALUE
-------------------- ---------- ------------ ---------- ------------------------ ------------------------
COLUMN_NAME,NUM_NULLS,NUM_DISTINCT,DENSITY,LOW_VALUE,HIGH_VALUE
IND_PAD,0,1,1,7820202020202020202020202020202020202020202020202020202020202020,7820202020202020202020202020202020202020202020202020202020202020
N1,0,25,0.04,80,C119
N2,0,20,0.05,80,C114


已选择3行。

可以看出 列ind_pad 空值为0个，num_distinct 1 density 1
n1 列空值0，num_distinct 25 densitiy=0.04最小值80


SQL> select
2 small_vc
3 from
4 t1
5 where
6 n1 = 2
7 and ind_pad = rpad('x',40)
8 and n2 = 3
9 ;

已选择21行。


执行计划
----------------------------------------------------------
Plan hash value: 1429545322

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1160 | 25 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 20 | 1160 | 25 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 20 | | 5 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("N1"=2 AND "IND_PAD"='x
' AND "N2"=3)

Note
-----
- cpu costing is off (consider enabling it)


统计信息
----------------------------------------------------------
183 recursive calls
0 db block gets
55 consistent gets
0 physical reads
0 redo size
6045 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
21 rows processed



以上查询中 ，优化器选定一个索引 ，并计算成本为25，基数20，我们以上查询知道基数为20，估计非常准备 ，但是成本25怎么来的呢
成本第一部分来自于索引访问的第2行，cost=5,另一部分来自语表访问的第一行增加了20，索引的成本5，遍历分支层 2 可能是优化器已经年过估计为获取20行数据必须遍历三个 叶块



我们接着分析两种选择率和clusting_fator因子


有效索引选择率------

我们现在考虑查询使用3个谓词，那么索引的返回项是多少呢？
在上帖子中
http://www.itpub.net/thread-945217-1-3.html
联合选择率公式selectivity(p and q)=selectivity(p)*selectivity(q)
selectivity(x and y and z)=
selectivity((x and y)and z))=
selectivity((x and y)*selectivity(y)=
selectivity(x)*selectivity(y)*selectivity(z)

所以有效索引选择率=0.04*1*0.05=0.002不知道怎么来的 看上边查询的num_distinct

如果我们要遍历0.2%的索音项，必须遍历0.2%的叶块，因为索引中的叶块是按顺序存储的
也就是成本公式中的：leaf_blocks*effective index selectivity

在user_indexes视图中有distinct_keys为500，也就是有500个索引项。
也就是简单的认为索引中有500中组合，某一种的组合概率为1/500=0.002?
一般情况下，优化器紧紧是将多个 独立选择率想乘，并不留意联合索引选择率的问题。有一个特例，以后帖子中会介绍。
oracle 为每一列存储了density num_distinct，当存在直方图可用并且索引中的不同值的数目值得怀疑时，考虑到偏斜数据，将独立选择率想乘来计算联合选择率是一种通用的解决方案。



有效表选择率



在上个帖子中我们已经知道如何计算表选择率，当所有与表相关的谓词都是and连接时，只要简单的每个选择率相乘就OK
假设查询中我们还包含了一个额外的谓词small_vc='0000000001',如果我们选择通过现有的索引访问表，一直到接触到表之后，我们还不能用上谓词small_vc，因此这个谓词将不能影响我们要访问数据的分数，只能影响最终返回的数据的分数

所以有效表选择率是：紧紧基于那些在接触到表之前就能够在索引中进行评估的谓词。。。。。[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9907339/viewspace-1054955/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9907339/viewspace-1054955/