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:

CREATE TABLE EMP (
  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 
Then
   If index I1 is UNIQUE then
      If index I2 is used to support Foregh Key or for Index Overload then
         Do Nothing
      Else
         Index I2 can be DROPPED
      End If
   Else
      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):

SELECT 
   /*+ RULE */ 
   tab_owner.name owner, t.name table_name, 
   o1.name || '(' || DECODE(bitand(i1.property, 1), 0, 'N', 1, 'U', '*') || ')' included_index_name , 
   o2.name || '(' || DECODE(bitand(i2.property, 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 i1.bo# = i2.bo# 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# = i1.bo# AND 
   t.owner# = tab_owner.USER# AND tab_owner.name LIKE '%' 
ORDER BY 1, 2

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 ( 10.1.0.2.0 ) 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_PARAMETERS         PARAMETERS_PRIMARY_KEY(U)           MGMT_PARAMETERS_IDX_01(N) 
SYSMAN                   MGMT_PRIVS              MGMT_PRIVS_PK(U)                    MGMT_PRIVS_IDX1(U)

Comparing the index columns for SYSMAN.MGMT_PARAMETERS table show:

INDEX_NAME               COLUMN_NAME             COLUMN_POSITION
MGMT_PARAMETERS_IDX_01   PARAMETER_NAME                        1
MGMT_PARAMETERS_IDX_01   PARAMETER_VALUE                       2
PARAMETERS_PRIMARY_KEY   PARAMETER_NAME                        1

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.