Search code examples
oracleoracle11goracle10gdatabase-performancequery-performance

Oracle 2 Indexes on same columns but different order

I have a table in production environment that has 2 Indexes on a Table with the same columns in the Index but in reversed order.

DDL is

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

Are these two indexes not essentially the same. Why would someone create indexes such a way? Does reversing or changing of column position do something internally?

Solution

  • Indexes are tied to the fields they're indexing, in the order they're being defined in the index. As long as you use the fields in the index, in their left->right order, then index is useable for your query. If you're skipping fields, then the index cannot be used. e.g. given the following index:

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

    then these where clauses 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 you use the indexed fields in the query is not relevant, just that you ARE using them. However, the order they're defined in the index is critical. The following clauses can NOT use the 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.

    For your two cases, there's nothing really wrong with how they're indexed, but it'd be slightly more efficient to index as follows:

    (STORE_ID, DIST_ID)
(DIST_ID)

    There's no point in indexing store_id in the second index, because the DBMS can use the first index to handle the store_id lookups. It's not a major gain, but still... maintaining indexes is overhead for the DB, and reducing overhead is always a good thing.