Removing duplicate indexes

Indexes are used to speed up data access by SQL statements, but there is no free lunch as each additional index increases:

  • The time needed to perform DML (Insert/Update/Delete) operation on the table (because additional index entries must be updated).
  • The enqueue time (during DML the corresponding index entries are locked decreasing the ability of parallel updates and causing transactions, issued by another session(s) to wait.
  • The generated UNDO volume.
  • The disk space needed to store the index information.

Our goal is to create only usefull indexes and to mimimize their number. It can be done by removing/dropping duplicate indexes which are not necessary and don't affect SQL statement performance.

Let's define an example of what kind of indexes can be dropped/removed:

  emp_id          NUMBER(8) NOT NULL,
  last_name       VARCHAR2(20) NOT NULL,
  first_name      VARCHAR2(20) NOT NULL );

CREATE INDEX EMP$LN    ON EMP ( last_name );

CREATE INDEX EMP$LN_FN ON EMP ( last_name, first_name );

When we are looking at both indexes definitions we can conclude:
  • The index EMP$LN supports statements with "LAST_NAME = :parameter" or "LAST_NAME LIKE :parameter" conditions in WHERE clause
  • The second index EMP$LN_FN supports the same conditions and additional conditions on FIRST_NAME column.
  • All queries that are using EMP$LN index can use EMP$LN_FN without any performance penalty.
In that example we can DROP the EMP$LN index without any performance related problems and actually to improve the performance of DML statements.

So let me specify a following rule:
If 2 indexes ( I1 and I2 ) exist for a table and
   the number of columns in Index I1 is less or equal to the number of column in index I2 and
   index I1 has the same columns in the same order as leading columns of index I2 
   If index I1 is UNIQUE then
      If index I2 is used to support Foregh Key or for Index Overload then
         Do Nothing
         Index I2 can be DROPPED
      End If
      Index I1 can be DROPPED
   End If
End If

To list all duplicate indexes I use following SQL statement (I had to access the base Oracle tables directly, because I needed to retrieve the number of index columns, which is not exposed in DBA/ALL/USER/_INDEXES views):

   /*+ RULE */ owner, table_name, || '(' || DECODE(bitand(, 1), 0, 'N', 1, 'U', '*') || ')' included_index_name , || '(' || DECODE(bitand(, 1), 0, 'N', 1, 'U', '*') || ')' including_index_name 
FROM  sys.USER$ tab_owner, sys.OBJ$ t, sys.IND$ i1, sys.OBJ$ o1, sys.IND$ i2, sys.OBJ$ o2 
WHERE = AND i1.obj# <> i2.obj# AND i2.cols >= i1.cols AND i1.cols > 0 AND
   i1.cols = ( SELECT /*+ ORDERED */ COUNT(1) FROM sys.ICOL$ cc1, sys.icol$ cc2 
               WHERE cc2.obj# = i2.obj# AND cc1.obj# = i1.obj# AND 
                     cc2.pos# = cc1.pos# AND cc2.COL# = cc1.COL#) AND 
   i1.obj# = o1.obj# AND i2.obj# = o2.obj# AND t.obj# = AND 
   t.owner# = tab_owner.USER# AND LIKE '%' 

You may change the '%' sign to schema name and to retrieve duplicate indexes for any specified schema.
The statement's output for schema 'SYSMAN' on Oracle 10g ( ) shows:
OWNER                    TABLE_NAME              INCLUDED_INDEX_NAME                 INCLUDING_INDEX_NAME
SYSMAN                   MGMT_ECM_SNAPSHOT       SH_PK(U)                            MGMT_ECM_SNAP_GUID_IDX(U)
SYSMAN                   MGMT_PRIVS              MGMT_PRIVS_PK(U)                    MGMT_PRIVS_IDX1(U)

Comparing the index columns for SYSMAN.MGMT_PARAMETERS table show:
MGMT_PARAMETERS_IDX_01   PARAMETER_NAME                        1
MGMT_PARAMETERS_IDX_01   PARAMETER_VALUE                       2

You must be aware that:
  • The statement works correctly for a regular indexes, but it may report duplicates for function or XML based indexes.
  • When using rule based optimizer, dropping an index may affect the optimizer decision what index to use, so in that case you must be especially careful.



If two indexes (I1 and I2) exist for a table and the number of columns in Index I1 is less or equal to the number of column in index I2 and index I1 has the same columns in the same order as leading columns of index I2

If not(index I1 is UNIQUE)
then Index I1 can be DROPPED
If index I1 is UNIQUE
not (index I2 is used to support Foreign Key or for Index Overload)
then Index I2 can be DROPPED

End If

Kevin Meade's picture

Unique constraints (Primary Key and Unique), no longer require an exact index any more. The will use the leading portion of non-unique indexes if these are available. Here is a quick example of this:

SQL> drop table a;

Table dropped.

SQL> create table a (a number not null
2 ,b number not null
3 ,c number not null);

Table created.

SQL> create index a_i1 on a(a,b,c);

Index created.

SQL> alter table a add primary key (b,a);

Table altered.

SQL> select index_name,column_name,column_position
2 from user_ind_columns
3 where table_name = 'A'
4 order by 1,column_position;

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
A_I1                           A                                            1
                               B                                            2
                               C                                            3

SQL> select constraint_name,index_name
2 from user_constraints
3 where table_name = 'A'
4 and constraint_type = 'P'
5 /

------------------------------ -----------------
SYS_C006820                    A_I1

SQL> l
1 select constraint_name,column_name,position
2 from user_cons_columns
3 where constraint_name = 'SYS_C006820'
4* order by position
SQL> /

CONSTRAINT_NAME                COLUMN_NAME                      POSITION
------------------------------ ------------------------------ ----------
SYS_C006820                    B                                       1
SYS_C006820                    A                                       2

Notice the primary key did not create a new index, it uses the non-unique index for enforcement because the leading columns of this non-unique index match the contraint columns. You will notice in fact that the constraint has its columns in a differing order from the actual index definition. This does not matter because the constraint is a logical concept and order of the columns is not material.


This Works! I used it yesterday in my project and I was able to remove 20 Dup indexes