Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For example, if I have two indexes:

CREATE INDEX IDX_1 ON MY_TABLE_1
 (ITEM, DATE, LOCATION)
 COMPUTE STATISTICS;

CREATE INDEX IDX_2 ON MY_TABLE_1
 (DATE, LOCATION, ITEM)
 COMPUTE STATISTICS;

Would this make IDX_2 redundant? If not, how do I determine the order of declaring the columns?

Should I be tailoring indexes to regular queries?

share|improve this question
add comment

2 Answers

up vote 12 down vote accepted

Yes, the benefit comes when you want to query on a part of the index. If you put the part-used predicates first the index can be used for queries that involve those predicates but not all of the columns in the index.

Also, unless you have other requirements it can help to put the most selective predicates first, as this can trim down index seek operations faster.

In your case IDX_2 is not necessarily redundant depending on the nature of the queries on the table. However, it may not be necessary to include all of the columns. If, for example, you do a lot of queries by location and date then IDX_2 may be useful to help resolve those queries as IDX_1 is not in the right order to be useful for that. You might, however, find that item is redundant on IDX_2.

From 9i, Oracle introduced a 'skip scan' operator where trailing index columns can be queried more efficiently, which can reduce the need for supplementary indexes of this sort.

In a more specific case, if you are querying item by location and date and don't need any other columns then the query could be completely resolved though the index without having to read anything from the table. You can also build covering indexes that have non-indexed columns attached. If all of the needed columns can be resolved from the covering index the query doesn't need to touch the main table at all.

Finally, in answer to your last question: If you have a set of regularly used queries that are using up a lot of resources and could be tuned using an index then it's certainly worth considering. However, maintaining indexes comes with an overhead on inserts, so you will have to trade query performance off against the overhead that the indexes place on insert or update operations.

share|improve this answer
3  
@ConcernedOfTunbridgeWells: An alternative approach is to use index key compression, and have the less selective (fewer distinct values) columns leading. It helps lead to a smaller index, while still allowing skip-scans to function well. –  Adam Musch Apr 4 '12 at 15:32
add comment

Another thing to take into consideration are columns with a lot of null values.

If these columns have any columns specified after them in the index, the null values have to be indexed. Otherwise, as usual, null values are not indexed (of course, this is assuming you're using a b-tree index).

So, if you have columns with a great number of null values, putting them at the end of the index can save you a significant amount of disk space.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.