Coskan’s Approach to Oracle

February 7, 2007

Effects of Adding Extra Columns To an Index

Filed under: CBO — coskan @ 1:24 pm

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)

About these ads

1 Comment »

  1. Thanks for sharing this information. Really is pack with new knowledge. Keep them coming.

    Comment by Shopping Goodies — August 18, 2007 @ 10:29 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 203 other followers

%d bloggers like this: