Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Performance impact of Primary Key supported by a non-unique index

1052085Mar 29 2016 — edited Apr 7 2016

Hi,

We've a table wherein a PK constraint is supported by Composite Non-Unique index.

I want to understand if there is performance impact of this while fetching the data. Will a primary key index perform better than non-unique index during data fetch? What are the advantages and disadvantages of having this approach? I encountered a scenario where the sql was not using the non-unique index. I was wondering if dropping the non-unique index and enforcing a primary key constraint with primary key index will help.

SQL> create table demo (col1 number(4), col2 number(4), col3 number(4), col4 varchar2(10), col5 varchar2(10));

Table created.

SQL> create index demo_ix on demo (col1, col2, col3);

Index created.

SQL> alter tableĀ  demo add constraint demo_pk primary key (col1, col2, col3) using index demo_ix;

Table altered.

Thanks

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 5 2016
Added on Mar 29 2016
23 comments
3,881 views