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.
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.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
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.
Reply With Quote
Originally Posted by OldTimer
Bookmarks