执行计划oracle中cost,执行计划中cost计算方法

概念：

blevel：二元高度=索引高度-1

clustering_factor：集群因子，扫描index scan得出的要扫描的表中block数，clustering_factor<=table blocks

索引扫描的计算公式：

cost =

blevel +

ceil(leaf_blocks *effective index selectivity) +

ceil(clustering_factor * effective table selectivity)

SQL> select leaf_blocks,blevel,clustering_factor from dba_indexes where index_name='IDX_T';

LEAF_BLOCKS BLEVEL CLUSTERING_FACTOR

----------- ---------- -----------------

112 1 776

SELECT b.num_rows, a.num_distinct, a.num_nulls, utl_raw.cast_to_number(high_value) AS high_value, utl_raw.cast_to_number(low_value) AS low_value

, b.num_rows - a.num_nulls AS "NUM_ROWS-NUM_NULLS", utl_raw.cast_to_number(high_value) - utl_raw.cast_to_number(low_value) AS "HIGH_VALUE-LOW_VALUE"

FROM dba_tab_col_statistics a, dba_tables b

WHERE a.owner = b.owner

AND a.table_name = b.table_name

AND a.owner = ‘SCOTT‘

AND a.table_name = upper(‘TEST‘)

AND a.column_name = ‘OBJECT_ID‘

NUM_ROWS

NUM_DISTINCTNUM_NULLSHIGH_VALUELOW_VALUENUM_ROWS-NUM_NULLSHIGH_VALUE-LOW_VALUE

50736507351538202

5073553818

effective index selectivity=(limit-low_value)/(high_value-low_value)

SQL> select (1000-2)/(53820-2) selectivity from dual;

SELECTIVITY

-----------

0.018543982

SQL> SELECT OWNER FROM TEST WHERE OBJECT_ID<1000;

已选择953行。

执行计划

----------------------------------------------------------

Plan hash value: 1810195980

---------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

---------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 941 | 10351 | 19 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 941 | 10351 | 19 |

|* 2 | INDEX RANGE SCAN | IDX_T | 941 | | 4 |

---------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"<1000)

1.回表io=ceil(clustering_factor * effective table selectivity)=19-4=152.blevel +ceil(leaf_blocks *effective index selectivity)

原文：http://blog.csdn.net/hughwang1216/article/details/38627629