Oracle 2 Indexes on identical columns, but in a different order

Question

I have a table in a production environment that has 2 indexes in a table with the same columns in the index, but in reverse order.

DDL

- CREATE INDEX IND_1 ON ORDERS (STORE_ID,DIST_ID) - CREATE INDEX IND_DL_1 ON ORDERS (DIST_ID,STORE_ID) 

Are these two indices essentially the same. Why would anyone create indexes this way? Does reversing or repositioning a column do something internally?

Answer 1

Indexes are bound to the fields that they index in the order in which they are defined in the index. As long as you use the fields in the index, in their left → in the right order, the index can be used for your query. If you skip fields, the index cannot be used. for example, given the following index:

 CREATE INDEX ind1 ON foo (bar, baz, qux) 

then in cases where offers will be able to use the index:

 WHERE bar=1 WHERE bar=1 AND baz=2 WHERE baz=2 AND bar=1 <--same as before WHERE bar=1 AND baz=2 AND qux=3 

The order in which indexed fields are used in the query does not matter, you just use them. However, the order that they are defined in the index is crucial. In the following sentences do NOT use an index:

 WHERE baz=2 <-- 'bar' not being used WHERE baz=2 AND qux=3 <-- 'bar' again not being used WHERE bar=1 AND qux=3 <-- the index can be partially used to find `bar`, but not qux. 

In your two cases, there is nothing wrong with how they are indexed, but it would be slightly more efficient to index as follows:

 (STORE_ID, DIST_ID) (DIST_ID) 

It makes no sense to index store_id in the second index, because the DBMS can use the first index to process store_id requests. This is not a serious gain, but still ... maintaining the overhead indices for the database, and reducing overhead is always good.

Answer 2

Oracle does not need to touch table segments in cases where all the necessary information is in the indexes. In your case, these indexes can serve as a quick search / translation table STORE_ID => DIST_ID and vice versa.

Look at the exec plan for a query in which you select only STORE_ID, based on DIST_ID, the query will only go through the index and will not touch the table itself.

But maybe the reason is different (if any).