We are currently using ALL_IND_COLUMNS to check if an index exists by its name or the columns it is using. The problem is that we can only check if the index exists with columns in a specific order.

We need to check if a table has an index with any name, but with Column1, Column2 and Column3 in any order. How can we do this?

  • 2
    But column order does matter. Two indexes with same columns but with different order will behave differently. Commented Nov 26, 2014 at 13:30
  • I know that, but will Oracle allow me to create two indexes with the same columns but different order? I don't think so. Commented Nov 26, 2014 at 13:34
  • 1
    Yes, it allows you. Commented Nov 26, 2014 at 13:52
  • It's answered. It can be closed or the comment from @aasim.abdullah can be promoted to the answer. Commented Nov 26, 2014 at 18:17

1 Answer 1


It is possible to check if an index using a number of columns exists.

     index_cols AS (
           COLLECT(column_name) AS column_names
        FROM all_ind_columns
        WHERE table_name = '&table_name'
        GROUP BY
     target_cols AS (
        SELECT '&column_name_1' AS col_name FROM dual
        UNION SELECT '&column_name_2' FROM dual
  FROM index_cols ic
        SELECT COUNT(*)
        FROM target_cols tc
        JOIN TABLE(ic.column_names) icc
           ON (tc.col_name = icc.COLUMN_VALUE)
     ) = (
        SELECT COUNT(*)
        FROM target_cols

Is one (a bit convoluted) way to do that.

But as aasim.abdullah noted in a comment, two indexes on the same set of columns but in different order, aren't the same. And it is possible to have both indexes side by side.

  • The query finds the index, but also gives an error ORA-04043 Commented Nov 27, 2014 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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