Oracle Logbook

May 23, 2012

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

Filed under: Uncategorized — heliodias @ 8:19 pm

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.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: