Check if an index exists by comparing columns

Question

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?

• Aasim Abdullah: But column order does matter. Two indexes with same columns but with different order will behave differently.
• juliano.net: I know that, but will Oracle allow me to create two indexes with the same columns but different order? I don't think so.
• Aasim Abdullah: Yes, it allows you.
• juliano.net: It's answered. It can be closed or the comment from @aasim.abdullah can be promoted to the answer.

Answer 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.

• juliano.net: The query finds the index, but also gives an error ORA-04043