Check if an index exists by comparing columns

indexoracleoracle-11g-r2

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?

Best Answer

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.

Related Question