-- Запрос, который выбирает в схеме UBC теоретически ненужные индексы на одно поле,

-- так как это же поле стоит на первом месте в другом, многополевом индексе.

 

 

SELECT a.table_name, a.index_name not_need, a.typ, b.index_name, a.col1,b.col2,b.col3,a.initial_extent used_place

  FROM   -- в 'а' выбираются однополевые , теоретически ненужные индексы, в 'в' - многополевые инд., куда входят 'a'

(SELECT   a.table_name, pr.typ, a.index_name, nc.cnt_columns num_col,

         c1.column_name col1, c2.column_name col2, c3.column_name col3,

         DECODE (uniqueness, 'UNIQUE', 'Y', ' ') unique_, initial_extent,

         distinct_keys dist, clustering_factor clust

    FROM sys.all_indexes a,

         all_ind_columns c1,

         (SELECT *

            FROM all_ind_columns

           WHERE column_position = 2) c2,

         (SELECT *

            FROM all_ind_columns

           WHERE column_position = 3) c3,

         (SELECT constraint_name NAME, constraint_type typ

            FROM all_constraints) pr,

         -- кол-во полей в индексе

         (SELECT   index_name, COUNT (index_name) cnt_columns

              FROM all_ind_columns

             WHERE table_name IN

                         (SELECT table_name

                            FROM -- таблицы с индексами, количеством больше одного

                                 (SELECT   table_name,

                                           COUNT (index_name) cnt_ind

                                      FROM sys.all_indexes

                                     WHERE owner = 'UBC'

                                  GROUP BY table_name)

                           WHERE cnt_ind > 1)

               AND table_name IN

                         (SELECT table_name

                            FROM -- таблицы у кот.есть многополевые индексы 

                                 (SELECT   table_name,

                                           AVG (column_position) aver

                                      FROM all_ind_columns

                                     WHERE table_owner = 'UBC'

                                  GROUP BY table_name)

                           WHERE aver > 1)

               AND table_name NOT LIKE 'QUEST%'

          GROUP BY index_name) nc

   WHERE nc.index_name = a.index_name

     AND c1.index_name = a.index_name

     AND c1.column_position = 1

     AND c2.index_name(+) = a.index_name

     AND c3.index_name(+) = a.index_name

     AND pr.NAME(+) = a.index_name

     AND a.table_name IN

               (SELECT table_name

                  FROM -- таблицы с индексами, количеством больше одного

                       (SELECT   table_name, COUNT (index_name) cnt_ind

                            FROM sys.all_indexes

                           WHERE owner = 'UBC'

                        GROUP BY table_name)

                 WHERE cnt_ind > 1)) a,

(SELECT   a.table_name, pr.typ, a.index_name, nc.cnt_columns num_col,

         c1.column_name col1, c2.column_name col2, c3.column_name col3,

         DECODE (uniqueness, 'UNIQUE', 'Y', ' ') unique_, initial_extent,

         distinct_keys dist, clustering_factor clust

    FROM sys.all_indexes a,

         all_ind_columns c1,

         (SELECT *

            FROM all_ind_columns

           WHERE column_position = 2) c2,

         (SELECT *

            FROM all_ind_columns

           WHERE column_position = 3) c3,

         (SELECT constraint_name NAME, constraint_type typ

            FROM all_constraints) pr,

         -- кол-во полей в индексе

         (SELECT   index_name, COUNT (index_name) cnt_columns

              FROM all_ind_columns

             WHERE table_name IN

                         (SELECT table_name

                            FROM -- таблицы с индексами, количеством больше одного

                                 (SELECT   table_name,

                                           COUNT (index_name) cnt_ind

                                      FROM sys.all_indexes

                                     WHERE owner = 'UBC'

                                  GROUP BY table_name)

                           WHERE cnt_ind > 1)

               AND table_name IN

                         (SELECT table_name

                            FROM -- таблицы у кот.есть многополевые индексы 

                                 (SELECT   table_name,

                                           AVG (column_position) aver

                                      FROM all_ind_columns

                                     WHERE table_owner = 'UBC'

                                  GROUP BY table_name)

                           WHERE aver > 1)

               AND table_name NOT LIKE 'QUEST%'

          GROUP BY index_name) nc

   WHERE nc.index_name = a.index_name

     AND c1.index_name = a.index_name

     AND c1.column_position = 1

     AND c2.index_name(+) = a.index_name

     AND c3.index_name(+) = a.index_name

     AND pr.NAME(+) = a.index_name

     AND a.table_name IN

               (SELECT table_name

                  FROM -- таблицы с индексами, количеством больше одного

                       (SELECT   table_name, COUNT (index_name) cnt_ind

                            FROM sys.all_indexes

                           WHERE owner = 'UBC'

                        GROUP BY table_name)

                 WHERE cnt_ind > 1)) b

WHERE  a.col1=b.col1

AND a.num_col=1 AND b.num_col>1 AND a.table_name=b.table_name