Effects of Adding Extra Columns To an Index

Continuing with highlights from the Oracle Cost Based Optimizer Book of Mr Jonathan Lewis ;

Case : One day you discover that users started to add another filter to the where clauses and you decided to add the newly added filter as an extra column to your index.

Mr Lewis says that this decision must be made with attention because of the side affects to old queries.

CBO uses clustering_factor column from the dba_indexes table to calculate the cost of and index. The calculation formula is below ;

Cost =Blevel+ ceil(effective_index_selectivity*Leaf Blocks)+ceil(effective_table_selectivity*clustering_factor)

When you add an extra column to a well clustered index, Cluster Factor can be change dramatically and when the cluster_factor increased, your cost would increase more than you imagine.

So think twice, test twice before adding another column to an index.

To see how costs go crazy Code Listing 2

Refences Used :Oracle Cost Based Optimizer Fundamentals (Jonathan Lewis) pg 104-106

(All my test are done under Oracle 10.1.0.2 Locally Managed Table without ASSM and CPU costing disabled as the writers test environment)