Archive for March 21st, 2009
Oracle 基于成本的优化器 CBO公式汇总
Saturday, March 21st, 2009
操作
Cost公式
Card公式
全表扫描
blocks / db_multi_block_read
num_rows * table selective
索引范围扫描
blevel + leaf_blocks * index selective
index.num_rows * index selective
通过索引rowid访问表
clustering_factor * table selective
table.num_rows * table selective
索引全扫描
leaf_blocks / db_multi_block_read
index.num_rows * index selective
嵌套循环连接
outer card * inner cost
Join Selectivity =
((num_rows(t1) - num_nulls(t1.c1)) / num_rows(t1)) *
((num_rows(t2) - num_nulls(t2.c2)) / num_rows(t2)) /
greater(num_distinct(t1.c1), num_distinct(t2.c2))
Join Cardinality =
Join Selectivity *
filtered cardinality(t1) * filtered cardinality(t2)
哈希连接
cost of access t1 + cost [...]
