2 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

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

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.

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.

1
source | link

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.