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)

Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *