You must be wondering why there is an option to use a non-unique index by primary key or is it beneficial.
First of all if we create index along with PK then oracle always creates unique index on PK columns. But if we create index first and then use it as part of primary key(make sure all primary key columns are part of index but visa versa in not mandatory) , uniqueness is not mandatory.
In fact for deferred primary key constraint, columns can hold non-unique values until we commit.
One common (?) performance method is define non unique index based on columns (A,B) and define primary key constraint on column A.
Now select statements of type:
select B from table where A = ? will only access index.
This is useful e.g. when B is status column.
Also if you use direct path insert on such table where non-unique index used for uniqueness, it will not allow direct path inserts.
There will be a note in execution plan
- Direct Load disabled because unique constraints are enforced by non-unique index
So there are some applications of non-unique index on PK
Note: If both PK and non-unique index is defined on same set of columns and in same order, mostly it's design issue then intended.
