Learn Oracle CBO 1

Introduce
去年select count 和  select data 留下的问题
优化器如何选择最佳路径 ？  如何计算代价 ？

正文
i/o  一般是大头  cpu 一般比较小

表内
full table scan
   = blocks
   = mbrc(multi-bolck read count)
blocks* mreadtime /(mbrc*sreadtim)

ioseektim iotfrspeed cpuspeednw

index scan (note : more columns index)
index full scan
index fast full scan

重点 讲解
=#levels + #leaf blocks
|
V
= #levels + (#leaf blocks *filter factor)
|
V
= #levels + (#leaf blocks *filter factor) + (clustering factor * filter fator)

表间
nested loop
cost out access + #outer * cost iner access

sort merge (memory for two tables)
cost out access + sort outer cost + cost iner access + sort inner cost

hash join(memory only for one table)
cost out access + hash join cost + cost iner access