Dropping composite index to improve performance

My table has a composite primary key on five columns, so indexes also have five columns. If I want to improve performance can I drop the composite index and build an index on each of the five columns individually? Would this help me in improving performance?

    Requires Free Membership to View

    Login

If you have defined a Primary Key constraint on a table and the PK constraint is composed of five columns, then Oracle will create an index on those five columns, in the order you specified in your PK constraint definition. You will not be able to drop this composite index without dropping the PK constraint, so you'll have to weigh that decision before you take action. Typically, one does not drop the PK constraint on a table.

I cannot determine if indexing the five columns individually will help improve performance. There are too many variables. It all depends on your data size, distribution and access patterns. The only way to know for sure if your performance will improve is to measure your performance with the composite index and then measure the performance with individual indexes. Then see if performance improved, deteriorated or stayed the same.

This was first published in January 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top