Detecting duplicate Oracle indexes

Detecting Oracle indexes with duplicate columns

Un-used indexes waste space and have overhead for DML, and there are alternatives to running scripts to detect un-used indexes.

One common misconception by developers is that every predicate in a SQL where clause should be indexed, but this is not true.  Because Oracle supports multi-column indexes, it's easy to accidently create ?duplicate? indexes, indexes that add overhead to DML and do not aid in speeding-up SQL execution.    

Management commonly hires clandestine experts to review the technical ability of a DBA.  One sure-fire way to get fired is to waste computing resources by allowing your database to have duplicate indexes!

Now that we understand how important it is to remove duplicate indexes, let's look at some common methods for finding indexes that can be safely dropped without effecting SQL execution.  

You can query the dba_ind_columns view to quickly locate indexes with duplicate columns, but the real challenge comes when you need to decide which index to drop:   This script looks for indexes on tables with the same leading column, then for indexes with the same two leading columns. 

This duplicate index column report provides a good starting point for trying to reduce redundancy in indexes.  This sophisticated script to detect Oracle duplicate index columns from my book Oracle Tuning: The Definitive Reference.

While this query does not provide every possible duplicate column, it does give you the general idea on how to locate redundant index columns and it serves as a good starting point for identifying index redundancy.

Here is another sophisticated script to detect duplicate index columns by Younes Naguib.  He claims that his script will locate all Oracle indexes with duplicate columns.

select /*+ rule */
   a.table_owner,
   a.table_name,
   a.index_owner,
   a.index_name,
   column_name_list,
   column_name_list_dup,
   dup duplicate_indexes,
   i.uniqueness,
   i.partitioned,
   i.leaf_blocks,
   i.distinct_keys,
   i.num_rows,
   i.clustering_factor
from
  (
   select
      table_owner,
      table_name,
      index_owner,
      index_name,
      column_name_list_dup,
      dup,
      max(dup) OVER
       (partition by table_owner, table_name, index_name) dup_mx
   from
      (
       select
          table_owner,
          table_name,
          index_owner,
          index_name,
          substr(SYS_CONNECT_BY_PATH(column_name, ','),2)  
          column_name_list_dup,
          dup
       from
          (
          select
            index_owner,
            index_name,
            table_owner,
            table_name,
            column_name,
            count(1) OVER
             (partition by
                 index_owner,
                 index_name) cnt,
             ROW_NUMBER () OVER
               (partition by
                  index_owner,
                  index_name
                order by column_position) as seq,
             count(1) OVER
               (partition by
                  table_owner,
                  table_name,
                  column_name,
                  column_position) as dup
   from
      sys.dba_ind_columns
   where
      index_owner not in ('SYS', 'SYSTEM'))
where
   dup!=1
start with seq=1
connect by prior seq+1=seq
and prior index_owner=index_owner
and prior index_name=index_name
)) a,
(
select
   table_owner,
   table_name,
   index_owner,
   index_name,
   substr(SYS_CONNECT_BY_PATH(column_name, ','),2) column_name_list
from
(
select index_owner, index_name, table_owner, table_name, column_name,
count(1) OVER ( partition by index_owner, index_name) cnt,
ROW_NUMBER () OVER ( partition by index_owner, index_name order by column_position) as seq
from sys.dba_ind_columns
where index_owner not in ('SYS', 'SYSTEM'))
where seq=cnt
start with seq=1
connect by prior seq+1=seq
and prior index_owner=index_owner
and prior index_name=index_name
) b, dba_indexes i
where
    a.dup=a.dup_mx
and a.index_owner=b.index_owner
and a.index_name=b.index_name
and a.index_owner=i.owner
and a.index_name=i.index_name
order by
   a.table_owner, a.table_name, column_name_list_dup;