Know more about CBO Index Cost

近日偶读Joze Senegacnik(他是一名ACE)在OOW 2011上做的《Getting The Best From The Cost Based Optimizer》Presentation(这里可以下载)，发现他总结的索引Unique Scan和Range Scan成本计算公式总结地很不错，贴出来共享：

Index Unique Scan Cost
INDEX UNIQUE SCAN COST = (BLEVEL (1-(OIC/100)) + 1) * (OICA/100)

Index Range Scan Cost
INDEX RANGE SCAN COST = (BLEVEL + FF*LFBL)*(1-(OIC/100))+ FF*CLUF)* (OICA/100)

formula does not include the CPU cost

• BLEVEL = number of branch levels in index
• add +1 for leaf block
• FF = filtering factor – selectivity
• LFBL = number of leaf blocks
• CLUF = index clustering factor
• OIC = optimizer_index_caching(default 0)
• OICA = optimizer_index_cost_adj parameter(default=100)