Removing duplicate indexes
Submitted by michael_bialik on Tue, 2006-08-01 04:22
When we are looking at both indexes definitions we can conclude:
So let me specify a following rule:
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):
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:
Comparing the index columns for SYSMAN.MGMT_PARAMETERS table show:
You must be aware that:
articles:
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.
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.
»
- michael_bialik's blog
- Log in to post comments
Comments
delete else
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
Then
If not(index I1 is UNIQUE)
then Index I1 can be DROPPED
If index I1 is UNIQUE
and
not (index I2 is used to support Foreign Key or for Index Overload)
then Index I2 can be DROPPED
End If
Unique constraints are enforceble with nonunique indexes
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>
SQL> create table a (a number not null
2 ,b number not null
3 ,c number not null);
Table created.
SQL>
SQL> create index a_i1 on a(a,b,c);
Index created.
SQL>
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;
SQL> select constraint_name,index_name
2 from user_constraints
3 where table_name = 'A'
4 and constraint_type = 'P'
5 /
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> /
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.
Kevin
This is coool!
This Works! I used it yesterday in my project and I was able to remove 20 Dup indexes
thanks