近日偶读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)