5

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. – aasim.abdullah Nov 26 '14 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. – Juliano Nunes Silva Oliveira Nov 26 '14 at 13:34
  • 1
    Yes, it allows you. – aasim.abdullah Nov 26 '14 at 13:52
  • It's answered. It can be closed or the comment from @aasim.abdullah can be promoted to the answer. – Juliano Nunes Silva Oliveira Nov 26 '14 at 18:17
1

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

  WITH
     index_cols AS (
        SELECT
           table_name,
           index_name,
           COLLECT(column_name) AS column_names
        FROM all_ind_columns
        WHERE table_name = '&table_name'
        GROUP BY
           table_name,
           index_name
     ),
     target_cols AS (
        SELECT '&column_name_1' AS col_name FROM dual
        UNION SELECT '&column_name_2' FROM dual
     )
  SELECT
     ic.*
  FROM index_cols ic
  WHERE 
     (
        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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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