首页 > 代码库 > 执行计划中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_DISTINCT NUM_NULLSHIGH_VALUELOW_VALUENUM_ROWS-NUM_NULLSHIGH_VALUE-LOW_VALUE
50736 507351538202 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=15
2.blevel +ceil(leaf_blocks *effective index selectivity)
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。