When dropping a primary key index could lead us to performance improvement in Oracle

Many people forget that Oracle doesn´t need a unique index to reinforce the uniqueness of the primary key.

So if you have another index , that among other columns have also the columns of your primary key, this new index

is a strong candidate to replace the “original” primary key index.

The query below intend to spot the primary keys that can have your index dropped.

SELECT T1.OWNER,T1.TABLE_NAME,T1.CONSTRAINT_NAME,T2.INDEX_NAME
FROM DBA_CONSTRAINTS T1,DBA_INDEXES T2
WHERE CONSTRAINT_TYPE=’P’
AND T1.INDEX_NAME<>T2.INDEX_NAME
AND T1.STATUS=’ENABLED’
AND T2.STATUS=’VALID’
AND T1.OWNER=T2.OWNER
AND T1.TABLE_NAME=T2.TABLE_NAME
AND T1.OWNER NOT IN (‘SYS’,’SYSTEM’,’WKSYS’)
AND NOT EXISTS
   (SELECT NULL FROM DBA_CONS_COLUMNS T3 WHERE
    T1.OWNER=T3.OWNER
    AND T1.TABLE_NAME=T3.TABLE_NAME
    AND T1.CONSTRAINT_NAME=T3.CONSTRAINT_NAME
    AND NOT EXISTS (
             SELECT NULL FROM DBA_IND_COLUMNS T4 WHERE
   T3.OWNER=T4.INDEX_OWNER AND T3.TABLE_NAME=T4.TABLE_NAME
       AND T3.COLUMN_NAME=T4.COLUMN_NAME
       AND T4.INDEX_NAME=T2.INDEX_NAME))
group by T1.OWNER,T1.TABLE_NAME,T1.CONSTRAINT_NAME,T2.INDEX_NAME
order by 1,2,3;

IMPORTANT: In order to drop your primary key index, you should first recreate the primary key constraint with the keyword (USING INDEX) and the new index.