Are there any benefits in certain ordering of columns when defining indexes

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?

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.

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.