Printable View
One of the Oracle databases I work with has an index on a table that spans 6 attributes. Someone now wants to create another index on the same table that is comprised of 3 of the attributes that are part of the first index.
Does that make any sense? How can that be helpful, couldn't the db simply use the first index to get the performance benefit they are looking for? I'm puzzled by this but don't want to object if there is actually a good reason for doing this.
It all depends on the order of the columns, and the position of the three columns of the proposed second index on the existing first one.Quote:
Originally Posted by OldTimer
Existing index: (ColA, ColB, ColC, ColD, ColE, ColF)
Proposed index #1:
(ColC, ColF, ColA) <== It makes sense to create the index
Proposed index #2:
(ColA, ColB, ColC) <== Most probably it doesn't make sense to create the index.
If the columns are included in the existing index, the existing index is a covering set. It is possible that the optimizer will use the existing index via skip scan, but it also depends on the cardinality of the columns (e.g., fewer values up front, more values towards the end). How do the existing and proposed indexes compare in terms of leading columns? For example, if the first three of the existing match the order of the proposed, it's a no-brainer (waste of time to create the new index). If the proposed happens to be the last three columns of the existing, that is more likely to be a better candidate for a new index. If they are interspersed, test and see.
In 11g, you can use invisible indexes within a session and see right then and there which is better. If you like the results, mark the index as visible.
Extremely helpful replies. Thank you both very much.