Home » SQL & PL/SQL » SQL & PL/SQL » Adding column to existing index (Oracle 11)
Adding column to existing index [message #615005] Thu, 29 May 2014 06:34 Go to next message
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
Member
I have a composite index of 3 columns and wish to add a 4th column to it which dramatically improves the performance of my new query. I am worried about other existing queries in the system that currently use the index but only reference one or more of the old 3 columns. (not the new one). I'm assuming that the change won't make these queries slower using the index - can anyone confirm whether this is the case?

Report message to a moderator

Re: Adding column to existing index [message #615007 is a reply to message #615005] Thu, 29 May 2014 06:44 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
It depends on the order of the columns in an index. Depending on the clustering factor, the leading columns in a composite index will have a significant role to play. Try all the possible combinations, and check for the performance. Keep in mind the driving factor should be the clustering factor.

A good read http://richardfoote.wordpress.com/2008/02/13/its-less-efficient-to-have-low-cardinality-leading-columns-in-an-index-right/

[Updated on: Thu, 29 May 2014 06:45]

Report message to a moderator

Previous Topic: View on synonym
Next Topic: Need help in this SQL logic
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 17 19:36:59 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.