Skip to Main Content
  • Questions
  • Difference Between Unique Index and Primary Key Index

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Mitul.

Asked: August 08, 2002 - 1:39 pm UTC

Last updated: June 20, 2018 - 12:27 am UTC

Version: 8.06

Viewed 100K+ times! This question is

You Asked


IS fetching row(s) using Primary key Index (in where clause) is better then Fetching row(s) using Unique Index (in where clause)?
Is there any internal difference between those Unique Index and Primary Key Index?


and Tom said...

There is no such thing as a "primary key index".

A primary key is two things:

o assures the values in a column(s) are unique
o assures they are NOT NULL


A primary key under the covers will use either a UNIQUE or NON-UNIQUE index. If the primary key is deferrable it'll use a non-unique index. If the column(s) are already indexed with a non-unique index, the primary key constraint will rely on that index. Otherwise a primary key will happen to create a unique index.

Hence, there is no real difference here. Under the covers there will be either a unique or non-unique index.

By all means -- if something is a primary key CALL IT THAT. Don't use a unique index, use the primary key constraint.

Rating

  (36 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Primary Keys are supposed to be unique aren't they?

Paul Dubar, August 08, 2002 - 8:12 pm UTC

I have been confused by this very thing recently.

Am i right in saying that a PK will just happen to associate itself with any index matching it's column list, and the index has nothing to do with enforcing the constraint? If a PK defines a list of columns that must be unique, why/how would you want to create a non-unique index on that column list?

Would you expect to see a NOT NULL constraint created too or is this implicit in the PK constraint.


Tom Kyte
August 09, 2002 - 8:43 am UTC

Primary keys ARE unique.

The index used to enforce a primary key DOES NOT HAVE TO BE unique.

NOT NULL is implicit.


As for "why" the reasons are threefold (or mayby 4 or 5 or 6 fold, there are lots of reasons, these just popped into my head):

a) you want a DEFERRABLE constraint (see the sql ref for info on that or search this site for deferrable). Here, the primary key is allowed to have dups during the course of your transaction but by the time you commit, the primary key values must be unique. We can only do this with a non-unique index

b) you have an existing non-unique index and the primary key is on the "leading edge" of this index. We will use that index to enforce the uniqueness in the data rather then creating YET ANOTHER index (better performance/space usage)

c) you have a need to enable/disable the primary key (say for large bulk loads in a DW). You don't want to drop the index. If the index is non-unique and we disable the primary key, we won't drop the index. If the index is UNIQUE and we disable the primary key, we drop the index.

and so on....

What is the meaning on UNIQUE and NON UNIQUE

Paul Dubar, August 14, 2002 - 7:15 pm UTC

Thanks for the useful follow up.

Perhaps its terminology that is confusing me here. I understand that if you have a PK defined on a set of columns and an index exists matching that set of columns (or "leading" part thereof), the PK may associate itself with that index and it is the index that enforces the uniqueness. If the index's column list is an exact duplicate of the PK column list, the index's contents are inherently unique, however Oracle allows that index to be defined as NON-UNIQUE to support the additional functionality you describe e.g. in part c of your reply.



Tom Kyte
August 14, 2002 - 8:13 pm UTC

Well -- i don't agree with the semantics of:

the PK may associate itself with that index
and it is the index that enforces the uniqueness.

100%.

The index does not enforce the uniqueness. The constraint does and the constraint makes use of an index (be it a UNIQUE index or an index that allows for duplicates) to make this enforcement go faster.

The index itself does not enforce the uniqueness.

Yes, but I want to keep my index...

Colin Davies, August 14, 2002 - 8:31 pm UTC

>If the index is non-unique and we disable the primary key, we won't drop the index. If the index is UNIQUE and we disable the primary key, we drop the index.<

I just need a little more clarification here. If I first create a UNIQUE INDEX and then enable a PK constraint, Oracle will use that index. Good. However, if I disable or drop the PK constraint, Oracle will also drop my unique index, even though I may wish to keep the index? That doesn't sound right. I created the index independently of the PK.


Tom Kyte
August 15, 2002 - 7:58 am UTC

Doesn't matter, we "kidnapped it".  It fell under the ownership of the constraint if you will.  It goes away.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create UNIQUE index t_idx on t(x);
Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(x);
Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select index_name from user_indexes;

INDEX_NAME
------------------------------
T_IDX

<b>now you see it...</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t drop constraint t_pk;
Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select index_name from user_indexes;
no rows selected

<b>and now you don't</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(x);
Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select index_name from user_indexes;

INDEX_NAME
------------------------------
T_IDX

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(x);
Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select index_name from user_indexes;

INDEX_NAME
------------------------------
T_IDX

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t drop constraint t_pk;
Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select index_name from user_indexes;

INDEX_NAME
------------------------------
T_IDX

ops$tkyte@ORA817DEV.US.ORACLE.COM> 


 

It's all about metadata isn't it?

Adrian Billington, August 15, 2002 - 3:44 am UTC

I would have thought the real difference is metadata. Having a unique index on a column tells Oracle very little about a column and the composition of its data when it comes to making decisions. A unique constraint, however, tells Oracle everything it needs to know about what the composition of the data should be. A primary key constraint takes it a stage further and defines the data's core identifier itself. Surely this all comes into play when we get into query rewrite and such like?

Tom Kyte
August 15, 2002 - 8:13 am UTC

Absolutely dead it -- it is more information. It comes into play with query rewrite and optimization.

A reader, November 26, 2002 - 4:39 pm UTC

Hi Tom,

Answer this...,

"Table in oracle MUST have primary key.Table without primary key is bad design"

Thanks,



Tom Kyte
November 26, 2002 - 6:49 pm UTC

one needs questions in order to give answers.

You made a statement -- statement is half false and half pretty much true.


Tables do not HAVE to have a primary key.
Most tables do (an audit trail might not though for example)

9i to the rescue again...

Connor McDonald, November 27, 2002 - 6:36 am UTC

alter table XXX disable constraint YYY keep index;
***********
hth
Connor

Tom Kyte
November 27, 2002 - 7:45 am UTC

Yeah -- but what is the usefulness here?

If the index was unique and this was a unique/primary key constraint -- you still have the de-facto constraint.

If the index was non-unique (via deferrable constraint for example) and this was a unique/primary key constraint -- you didn't need to do that (keep index was implied).

So -- why disable the unique constraint, yet, keep the unique index??


I didn't say it was useful....

Connor, November 27, 2002 - 9:36 am UTC

....I just responded to the guy who said

"However, if I disable or drop the PK constraint, Oracle will also drop my unique index, even though I may wish to keep the index? That doesn't sound right. I created the index independently of the PK."

:-)

Cheers
Connor

Tom Kyte
November 27, 2002 - 9:55 am UTC

Ahh -- got it, sorry about that (still confused why we added it tho ;)



A reader, November 27, 2002 - 2:19 pm UTC

from the post by adrian billington:
"Having a unique index on a column tells Oracle very little about a column and the composition of its data when it comes to making decisions."

Also you demonstrated above that the primary key don't need a unique index and a non-unique index can't be "kidnapped" like a unique index.

The question is: then what is the utility of a unique index? If my primary key can be supported by a non-unique index (though the data may be unique), I would rather prefer to have a non unique index (because I can maintain it seperate from the primary key). Am I right/wrong?

Tom Kyte
November 27, 2002 - 3:35 pm UTC

Well, maybe -- it could impact the optimizer to a degree which sometimes looks at the index.  You'll want to keep an eye on that.  Consider:

ops$tkyte@ORA920.US.ORACLE.COM> create table t1 ( x int primary key, y int );
Table created.

ops$tkyte@ORA920.US.ORACLE.COM> insert into t1 select rownum, rownum from all_objects;
29374 rows created.

ops$tkyte@ORA920.US.ORACLE.COM> analyze table t1
  2  compute statistics
  3  for table
  4  for all indexes;
Table analyzed.


ops$tkyte@ORA920.US.ORACLE.COM> create table t2 ( x int primary key DEFERRABLE, y int );
Table created.

ops$tkyte@ORA920.US.ORACLE.COM> insert into t2 select rownum, rownum from all_objects;
29374 rows created.

ops$tkyte@ORA920.US.ORACLE.COM> analyze table t2
  2  compute statistics
  3  for table
  4  for all indexes;
Table analyzed.

ops$tkyte@ORA920.US.ORACLE.COM> set autotrace traceonly explain

ops$tkyte@ORA920.US.ORACLE.COM> select * from t1 where x = 5;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 <b>Card=1 Bytes=12</b>)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=12)
   2    1     INDEX (UNIQUE SCAN) OF 'SYS_C004498' (UNIQUE) (Cost=1 Card=100)



ops$tkyte@ORA920.US.ORACLE.COM> select * from t2 where x = 5;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 <b>Card=294 Bytes=3528</b>)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=294 Bytes=3528)
   2    1     INDEX (RANGE SCAN) OF 'SYS_C004499' (NON-UNIQUE) (Cost=1 Card=117)



ops$tkyte@ORA920.US.ORACLE.COM> analyze table t2 compute statistics for <b>all indexed columns;</b>

Table analyzed.

ops$tkyte@ORA920.US.ORACLE.COM> select * from t2 where x = 5;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 <b>Card=1 Bytes=12</b>)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=12)
   2    1     INDEX (RANGE SCAN) OF 'SYS_C004499' (NON-UNIQUE) (Cost=1 Card=1)



ops$tkyte@ORA920.US.ORACLE.COM> set autotrace off

You might have to watch for conditions like that where a non-unique index will "explode" the cardinality out.  Here, it didn't matter much but if that was down in the middle of a big plan and gets multiplied out by something higher up in the plan, it could have an impact.


But overall -- using a non-unique index has many advantages, yes. 

Brilliant

A reader, November 27, 2002 - 5:31 pm UTC


Sagi, November 28, 2002 - 7:04 am UTC

Hi Tom,

I got confussed with the below statement of yours

"The index does not enforce the uniqueness. The constraint does and the 
constraint makes use of an index (be it a UNIQUE index or an index that allows 
for duplicates) to make this enforcement go faster. 

The index itself does not enforce the uniqueness. "

Here is my Statements executed.....

SQL> CREATE TABLE T (X NUMBER) ;

Table created.

SQL> CREATE UNIQUE INDEX IDX_1 ON T(X) ;

Index created.

SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS
  2  WHERE TABLE_NAME='T' ;

no rows selected

SQL> SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS 
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME='T' ;

INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
IDX_1                          NORMAL                      UNIQUE

SQL> 
SQL> 
SQL> INSERT INTO T VALUES(1) ;

1 row created.

SQL> INSERT INTO T VALUES(1) ;
INSERT INTO T VALUES(1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IDX_1) violated

So if you see from the Statements executed about then you can see that there is no constraint but still I am not able to allowed insert a duplicate value. This is because of UNIQUE INDEX.

So does it mean that Uniquness is enforced here by INDEX even though there is no constraint.

Anticipating your valuable clarrifications and reply.

Thanx in Advance.

Sagi
 

Tom Kyte
November 28, 2002 - 8:07 am UTC

It is a matter of semantics.

You create a constraint to enforce uniqueness. That is the semantically correct thing to do.

Yes, of course you can use a unique index to enforce uniqueness but you've totally lost the semantics. The data dictionary won't be screaming out "this is a unique thing here". Tools won't see it as unique. No foreign keys to it. The query rewrite won't be as smart.

If you have a column(s) that is unique -- use a constraint, do NOT use a unique index.

Lets put it this way -- if you find yourself creating a unique index say "whoops -- I'm doing something very wrong here". (unless you are simply precreating it for a constraint that will be placed on there). You shouldn't have the need to creat a unique index.

A possible use for "KEEP INDEX"

Connor McDonald, December 30, 2002 - 2:17 pm UTC

A light bulb moment for why KEEP INDEX may have been implemented based on some other forum postings...

Scenario:
- Partitioned table
- Unique or PK constraint backed by local (or otherwise partitioned) unique index
- SQLLDR Direct load, some rows violate constraint, so 1 or more index partitions unusable.

At this point you might want to:

- disable or drop constraint KEEP INDEX
- rebuild just those index partitions that are unusable
- enable or add constraint, exceptions into etc

The KEEP INDEX becomes useful because the constraint applies to the whole table, whereas we only want to rebuild those index *partitions* that are unusable. Without KEEP INDEX the entire index would need be to rebuilt/recreated.

(This hypothesis wholly untested btw)

Cheers
Connor

two questions related to unique and primary constraints

Unmesh, April 30, 2003 - 2:42 pm UTC

Thanks to all for very informative discussion. I have two questions -

1. As a summary of above discussion can I conclude that - if "uniqueness" of data is required a unique constraint should be used instead of a unique index.

2. Is there any advantage of creating a unique index first and then creating primary key constraint as compared to creating primary key constraint directly (which inturn automatically creates the underlying index)? Any advantage of one over the other?

i.e.

create table t ( x int );
create UNIQUE index t_idx on t(x);
alter table t add constraint t_pk primary key(x) using index;

Vs.

create table t ( x int );
alter table t add constraint t_pk primary key(x) using index;




Tom Kyte
April 30, 2003 - 7:14 pm UTC

1) yes.

2) well, you could create the index in parallel nologging on a huge table -- then "kidnap it".

The one advantage would be that you know that a unique index is created. If there was an index on t(x,y,z) -- the unique constraint would Kidnap it -- if you really truly want there to be a unique index, that sort of ensures it.

Mirjana, May 23, 2003 - 10:05 am UTC


disabled foreign key constraints

j., August 02, 2003 - 1:30 am UTC

hi tom,

a few years ago I saw an application (7.3.x) with lots of DISABLED foreign key constraints in it. referential integrity was enforced by corresponding triggers though - adding some additional "stuff" (e.g. to handle mutating table issues).

I wonder if the disabled foreign key constrained were just used to "describe" the structure of the data model. is there any advantage apart from that?

Tom Kyte
August 02, 2003 - 8:04 am UTC

I'd betcha that system -- had it enabled the fkeys, would find lots and lots of dirty data.

it is very very hard to do RI in a trigger properly and since they didn't implement their system in a reasonable fashion, I would guess they did't do the triggers right either.

yes, the constraints would have been there for some tool like an ad hoc query builder or whatever to describe the relationships between the objects. so the query builder would know what joins to what.

Space consideration

HS, July 22, 2004 - 4:04 pm UTC

What are the space requirements for unique/primary key indexes. I changed some unique indexes to primary keys and they required more space. Why this behaviour....?

Thanks.

Tom Kyte
July 23, 2004 - 8:19 am UTC

give me a for exmaple.

a primary key index is generally nothing more than a unique index on the columns (eg: there isn't really anything like a "pk index", there are primary key constraints, there are indexes -- a primary key constraint may well issue a create index statement)



so, give us an example of what you did. I can "hypothesize"

a) you had empty tables with unique indexes on sequences or other dense data
b) you inserted data over time
c) the index was very very "packed" over time

d) you dropped the unique index and created a primary key which created an index
e) the newly created index has lots of white space due to "pctfree" -- to reserve room for new rows on those leaf blocks (rows that probably will never appear since I assumed "sequence" in a)



HS, July 23, 2004 - 10:55 am UTC

Performed the following for about 50 unique indexes, and had to add a datafile in the later part of the process when 10+ million row tables were involved. I do not understand why the PK constraint will take more space verses Unique index.

Thanks.

Original statement for an index:
CREATE UNIQUE INDEX IDX1 ON TAB1
(
col1 ASC,
col2 ASC
)
PCTFREE 5
INITRANS 2
MAXTRANS 255
TABLESPACE tbsits
STORAGE (
INITIAL 737280
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/

Dropped the above and created as follows:
ALTER TABLE tab1 ADD
CONSTRAINT idx1
PRIMARY KEY
(col1 ,
col2 ) ;


Tom Kyte
July 23, 2004 - 3:57 pm UTC

and "how" did you do that. script/plsql/what was the *process*

i do see you had a tablespace on the create index, but didn't have on the add constraint.

rest assured, an index is an index, is an index, is an index. consider:


big_table@ORA9IR2> create unique index foobar on big_table(id,object_id);
Index created.


big_table@ORA9IR2> exec show_space( 'FOOBAR', user, 'INDEX' );
Free Blocks............................. 0
Total Blocks............................ 12,288
Total Bytes............................. 100,663,296
Total MBytes............................ 96
Unused Blocks........................... 886
Unused Bytes............................ 7,258,112
Last Used Ext FileId.................... 11
Last Used Ext BlockId................... 158,985
Last Used Block......................... 138

PL/SQL procedure successfully completed.

big_table@ORA9IR2> drop index foobar;
Index dropped.

big_table@ORA9IR2> alter table big_table add constraint foobar primary key(id,object_id);
Table altered.

big_table@ORA9IR2> exec show_space( 'FOOBAR', user, 'INDEX' );
Free Blocks............................. 0
Total Blocks............................ 12,288
Total Bytes............................. 100,663,296
Total MBytes............................ 96
Unused Blocks........................... 886
Unused Bytes............................ 7,258,112
Last Used Ext FileId.................... 11
Last Used Ext BlockId................... 158,985
Last Used Block......................... 138

PL/SQL procedure successfully completed.

big_table@ORA9IR2> select count(*) from big_table;

COUNT(*)
----------
4000000

didn't matter if we created a unique index OR we had a unique index created for us.


Index space

HS, July 23, 2004 - 4:18 pm UTC

I used a script. We will do the same activity in production and I am planning to get a before/after snapshot of the space usage.

Ok got it

HS, July 23, 2004 - 4:30 pm UTC

Tom
You are right. The original index was dropped from the index tablespace and the primary key index was created in the data tablespace.

We have 9i, I am assuming there is a clause in the alter table statement that will create the PK index in the index tablespace?

Thanks.

Tom Kyte
July 23, 2004 - 5:18 pm UTC

yes, the alter table command documents the "using index" clause for the primary key constraint.

foreign key on table with unique constraint

A reader, April 19, 2005 - 5:17 am UTC

Now only I came to know that, Foreign key can be created for table with unique constraint.
And when you create, you should mention the field name in reference table.
(see below - otherwise this error will come ORA-02268: referenced table does not have a primary key)

1* create table t1 (slno number primary key)
riz@ORCL> /
Table created.
1* create table t2 (slno number unique )
riz@ORCL> /
Table created.

1* create table t3 (slno number )
riz@ORCL> /
Table created.

1* create unique index uk_ind on t3(slno)
riz@ORCL> /
Index created.


1* alter table t3 add constraint fk1 foreign key(slno) references t2(slno)
riz@ORCL> /
Table altered.

riz@ORCL> ed
Wrote file afiedt.buf
1 create table t4 (slno number,
2* constraint fk2 foreign key(slno) references t2 )
riz@ORCL> /
constraint fk2 foreign key(slno) references t2 )
*
ERROR at line 2:
ORA-02268: referenced table does not have a primary key

ems0405@ORCL> ed
Wrote file afiedt.buf

1 create table t4 (slno number,
2* constraint fk2 foreign key(slno) references t1 )
ems0405@ORCL> /

Table created.

1 create table t5 (slno number,
2* constraint fk3 foreign key(slno) references t2(slno) )
riz@ORCL> /
Table created.


Tom Kyte
April 19, 2005 - 7:42 am UTC

<quote>
Now only I came to know that, Foreign key can be created for table with unique
constraint.
</quote>

unique or primary key.

I would suggest always being explicit and listing the columns you are pointing from and to.

Foreign key constraint

A reader, May 17, 2005 - 5:13 pm UTC

Tom,
I would your opinion on the advantages and disadvantages of creating a foreign key constraint.
Should it be
a) from the PK of the parent table to the child table or
b) from the unique constraint (unique index) of the parent table to the child table?

Would appreciate it if you can list the advantages/disadvantges of both.

Thank you


Tom Kyte
May 17, 2005 - 6:29 pm UTC

you can only answer that.

either one works, they are functionally equivalent, only a unique constraint allows for NULLs and a primary key does not.

if you have a primary key, that is the most natural one to use, since we would never update a primary key!

Foreign key constraint

A reader, May 17, 2005 - 7:02 pm UTC

Thank you

Your comments/opinions are what I always go with.

What?

A reader, May 18, 2005 - 7:34 am UTC

Hi Tom,
Regarding:
"c) you have a need to enable/disable the primary key (say for large bulk loads
in a DW). You don't want to drop the index. If the index is non-unique and we
disable the primary key, we won't drop the index. If the index is UNIQUE and we
disable the primary key, we drop the index.
"
Don't you think that doing large bulk loads with index enabled (usable) could bring load to just creep?
Regards,

Tom Kyte
May 18, 2005 - 9:12 am UTC

if you have 100,000,000 rows in the table

and you need to disable the constraint for whatever reason

to load 1,000,000 new rows

no -- the time to maintain the index vs rebuild the entire index would be less probably (assuming a direct path load)

it all depends on

a) the size of the existing data set you are loading into
b) the amount of data to be loaded.

import stuff

A reader, May 30, 2005 - 6:15 pm UTC

I noticed import first creates a unique index on the PK columns, than alters the table to add the PK (same name as the unique index).

Will adding the PK be any faster with the unique index already in place? Just wondering.

Tom Kyte
May 30, 2005 - 8:10 pm UTC

it should not be, are you sure about that? what did you do to see this.

To see it..

A reader, May 30, 2005 - 9:29 pm UTC

I exported a table, rows=N.

Did something bad, "strings" on the .dmp file to see what's in it. There it was in plain text.

Per the examples I've seen (and tried) you can alter table and create a PK with the same name as the Unique index.

Tom Kyte
May 31, 2005 - 7:36 am UTC

I know it can be "done", but that isn't the way exp/imp does it by default.  This is why I'll need a test case from you:

ops$tkyte@ORA9IR2> <b>create table t ( x int primary key, y int );</b>
 
Table created.
 
ops$tkyte@ORA9IR2> <b>!exp userid=/ tables=t</b>
Export: Release 9.2.0.5.0 - Production on Tue May 31 07:32:43 2005
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
 
About to export specified tables via Conventional Path ...
. . exporting table                              T          0 rows exported
Export terminated successfully without warnings.
 
ops$tkyte@ORA9IR2> <b>!imp userid=/ full=y show=y</b>
Import: Release 9.2.0.5.0 - Production on Tue May 31 07:32:44 2005
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
 
Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
 "CREATE TABLE "T" ("X" NUMBER(*,0), "Y" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 "
 "INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1"
 ") TABLESPACE "USERS" LOGGING NOCOMPRESS"
. . skipping table "T"
 <b>
 "ALTER TABLE "T" ADD  PRIMARY KEY ("X") USING INDEX PCTFREE 10 INITRANS 2 MA"
 "XTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "
 ""USERS" LOGGING ENABLE"</b>
Import terminated successfully without warnings.
 
ops$tkyte@ORA9IR2> <b>!strings expdat.dmp</b>
EXPORT:V09.02.00
DOPS$TKYTE
RTABLES
8192
                                   Tue May 31 7:32:44 2005expdat.dmp
#C##
#C##
-04:00
BYTE
INTERPRETED
TABLE "T"
CREATE TABLE "T" ("X" NUMBER(*,0), "Y" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS
INSERT INTO "T" ("X", "Y") VALUES (:1, :2)<b>
ALTER TABLE "T" ADD  PRIMARY KEY ("X") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING ENABLE</b>
TABLE "T"
ENDTABLE
EXIT
EXIT
 
ops$tkyte@ORA9IR2>


<b>consistent in 8i, 9i, 10g...</b>

 

one more thing.

A reader, May 30, 2005 - 9:39 pm UTC

The reason I am even asking, is to see if it's possible to enable a PK with minimal table locking while the actual constraint key is being created/enabled.

Here's where I'm going.
Create unique index "online". I know that won't guarantee you won't lock the table if you get a lot of dml against the table.
Alter table and add PK. Seems like at the least, it would fast full scan the index, since it's already unique, to verify no nulls exist.

Tom Kyte
May 31, 2005 - 7:43 am UTC

create index online will 100% absolutely lock the table twice

a) at the very beginging
b) at the very end

and if you want to, after succesfully creating a unique index, you can add the primary key constraint with "rely"


ops$tkyte@ORA9IR2> create table t ( x int, y int );
 
Table created.
 
ops$tkyte@ORA9IR2> create unique index t_idx  on t(x) online;
 
Index created.
 
ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(x) rely novalidate ;
 
Table altered.


Now, you have the NOT NULL problem to deal with, the column is known to be NOT NULL for subsequently added data, but not for existing data and the not null cannot be enabled with RELY.  What this means is that X would not be "NOT NULL" and that can affect some classes of query plans

ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T', numrows=> 1000000000, numblks=> 10000000);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> set linesize 121
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select count(*) from t;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1517739 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=1517739 Card=1000000000)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> alter table t modify x not null;
 
Table altered.
 
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select count(*) from t;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'T_IDX' (UNIQUE) (Cost=5 Card=1000000000)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off
 

Mike, May 31, 2005 - 4:16 am UTC

We've just noticed a very strange thing on our production system. A Unique Key on a crucial linking table was replaced with a Primary Key & performance improved dramatically!

Assuming PK == UK this is inexplicable.

Could Oracle be treating PKs differently to UKs when choosing what data to age out of the buffers so that the PK is more "persistant" and doesn't keep needing to be reloaded?

Tom Kyte
May 31, 2005 - 8:13 am UTC

well, a unique key <> primary key.

a primary key is

a) a unique key PLUS
b) not null constraint which allows the index to be used more often


I can hypothesize at least two things:

a) the addition of the not null constraint changed query plans, allowing the index to be used and this was of positive benefit.

b) the rebuilding of the index was beneficial.

But unless you have information from before the change (plans, IO's, etc) we probably won't be able to definitively say (well, of course if you put it back to a unique key then we could rule out the NOT NULL constraint)

To Mike

A reader, May 31, 2005 - 7:30 am UTC

> Assuming PK == UK this is inexplicable.

PK should be more like UK + Not NULL constraint.

Were the values with the unique index nullable?

Import Stuff - Test Case

Greg W, May 31, 2005 - 11:18 am UTC

Tom,
here is ONE WAY of getting the "create index" statement then the add primary key constraint during import:

create table t (col1 number);
Table created.
SQL> alter table t add constraint prim_key primary key (col1);
Table altered.

exp /

Export: Release 10.1.0.4.0 - Production on Tue May 31 10:54:09 2005

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3

Export table data (yes/no): yes > no

Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > t

. . exporting table                              T
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Export terminated successfully without warnings.
bash-2.03$ strings exp* |pg
...
METRICST
TABLE "T"
CREATE TABLE "T" ("COL1" NUMBER)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
CREATE UNIQUE INDEX "PRIM_KEY" ON "T" ("COL1" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING
ALTER TABLE "T" ADD  CONSTRAINT "PRIM_KEY" PRIMARY KEY ("COL1") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE
--------------------------------------
So - it depends on how the primary key is created.  If the prim key is specified at table create time, then there is My question:  is the end result the same?  Our database create scripts have the primary keys added after the tables are created, does it matter?

Thanks,
Greg

 

Tom Kyte
May 31, 2005 - 6:32 pm UTC

is the end result the same -- yes, you'll have a table with a primary key constraint on it.

Same for my test case..

A reader, May 31, 2005 - 8:11 pm UTC

Here it is.. sorry for the delays, looks like someone did the same thing I did. FWIW, the PK enabled on a 10Mil+ row table in under a second with the unique index already in place. Maybe not recommended, but an option to know if you get into a bind.

SQL>desc test_tab


 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 POSN                                                              NOT NULL VARCHAR2(12)
 SEQ_NUM                                                                    NUMBER(10)
 OSN                                                                        VARCHAR2(12)
 FSN                                                                        VARCHAR2(12)
 PRODUCT                                                                    NUMBER(10)
 TIMESTAMP                                                                  DATE
 PROD_DETAIL                                                                VARCHAR2(5)
 TEXT                                                                       LONG
 DUE_DATE                                                                   DATE
 LAST_NAG_LEVEL                                                             VARCHAR2(1)
 COMPLETION_IND                                                             VARCHAR2(1)
 INFO_ID                                                                    VARCHAR2(12)
 CONTACT_ID                                                                 VARCHAR2(12)
 ORDER_OPTIONS                                                              VARCHAR2(10)

SQL>select table_name, index_name, uniqueness from user_indexes where table_name = 'TEST_TAB';


TABLE_NAME                     INDEX_NAME                     UNIQUENESS                                               
------------------------------ ------------------------------ ---------------------------                               
TEST_TAB                  IDX_TEST2               NONUNIQUE                                                   
TEST_TAB                  IDX_TEST_COMP           NONUNIQUE                                                    
TEST_TAB                  IDX_TEST_TIMER          NONUNIQUE                                                    


SQL>select table_name, constraint_name, constraint_type from user_constraints where table_name = 'TEST_TAB';

TABLE_NAME                     CONSTRAINT_NAME                C                                                         
------------------------------ ------------------------------ -                                                         
TEST_TAB                  SYS_C0017187                   C                                                         

SQL>alter table test_tab add constraint PK_TEST_TAB primary key (POSN) using index tablespace INDEX_LMT_256M;

Table altered.

SQL>select table_name, index_name, uniqueness from user_indexes where table_name = 'TEST_TAB';

TABLE_NAME                     INDEX_NAME                     INDEX_TYPE                                                
------------------------------ ------------------------------ ---------------------------                               
TEST_TAB                  IDX_TEST2               NONUNIQUE                                                    
TEST_TAB                  IDX_TEST_COMP           NONUNIQUE                                                 
TEST_TAB                  IDX_TEST_TIMER          NONUNIQUE                                                   
TEST_TAB                  PK_TEST_TAB             UNIQUE                                                 

SQL>select table_name, constraint_name, constraint_type from user_constraints where table_name = 'TEST_TAB';

TABLE_NAME                     CONSTRAINT_NAME                C                                                         
------------------------------ ------------------------------ -                                                         
TEST_TAB                  SYS_C0017187                   C                                                         
TEST_TAB                  PK_TEST_TAB                    P   



exp


Export: Release 8.1.7.4.0 - Production on Tue May 31 14:22:54 2005

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Username: tester/testerpass

Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
Enter array fetch buffer size: 4096 >  

Export file: expdat.dmp > test_tab

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3

Export table data (yes/no): yes > n 

Compress extents (yes/no): yes > n

Export done in US7ASCII character set and US7ASCII NCHAR character set
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > test_tab

. . exporting table                  TEST_TAB
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > 

Export terminated successfully without warnings.


EXPORT:V08.01.07
DTESTER
RTABLES
1024
                                      Tue May 31 14:23:8 2005test_tab.dmp
#G#G
#G##
+00:00
8.1.6
TABLE "TEST_TAB"
CREATE TABLE "TEST_TAB" ("POSN" VARCHAR2(12) NOT NULL ENABLE, "SEQ_NUM" NUMBER(10, 0), "OSN" VARCHAR2(12), "FSN" VARCHAR2(12), "
PRODUCT" NUMBER(10, 0), "TIMESTAMP" DATE, "PROD_DETAIL" VARCHAR2(5), "TEXT" LONG, "DUE_DATE" DATE, "LAST_NAG_LEVEL" VARCHAR2(1), "COM
PLETION_IND" VARCHAR2(1), "INFO_ID" VARCHAR2(12), "CONTACT_ID" VARCHAR2(12), "ORDER_OPTIONS" VARCHAR2(10))  PCTFREE 0 PCTUSED 0 INITR
ANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROU
PS 1 BUFFER_POOL DEFAULT) TABLESPACE "TOOLS"
GRANT INSERT ON "TEST_TAB" TO "APPUSER"
GRANT UPDATE ON "TEST_TAB" TO "APPUSER"
GRANT DELETE ON "TEST_TAB" TO "APPUSER"
GRANT SELECT ON "TEST_TAB" TO "APPSEL"
GRANT SELECT ON "TEST_TAB" TO "APPSCUM"
CREATE UNIQUE INDEX "PK_TEST_TAB" ON "TEST_TAB" ("POSN" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 5242880 FREELIS
TS 1 FREELIST GROUPS 1) TABLESPACE "INDEX_LMT_256M" LOGGING
CREATE INDEX "IDX_TEST_TIMER" ON "TEST_TAB" ("DUE_DATE" , "LAST_NAG_LEVEL" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(
INITIAL 131072 FREELISTS 2 FREELIST GROUPS 1) TABLESPACE "INDEX_LMT_1M" LOGGING
CREATE INDEX "IDX_TEST2" ON "TEST_TAB" ("OSN" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 5242880 FREELISTS 2 F
REELIST GROUPS 1) TABLESPACE "INDEX_LMT_256M" LOGGING
CREATE INDEX "IDX_TEST_COMP" ON "TEST_TAB" ("COMPLETION_IND" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 104857
6 FREELISTS 2 FREELIST GROUPS 1) TABLESPACE "INDEX_LMT_10M" LOGGING
ALTER TABLE "TEST_TAB" ADD  CONSTRAINT "PK_TEST_TAB" PRIMARY KEY ("POSN") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STOR
AGE(INITIAL 5242880 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "INDEX_LMT_256M" ENABLE 
 

Mike, May 31, 2005 - 9:51 pm UTC

Thanks for the follow up. Yes the UK was not null as well. so functionally it should be identical to the replacement PK.

We have a pre-production environment where the change from UK->PK has not been done so I can compare the explain plan results which are similar in pre-prod and prod. However I can't be sure whether the cost difference (18->35) can be attributed to the different analysis used in pre-prod or the differences in the data (pre-prod is out of date) or even the different load on the database.

how to find that this index is used by pk ?

A reader, August 24, 2006 - 1:32 pm UTC

how to find that which index is used by pk ?

Tom Kyte
August 27, 2006 - 8:06 pm UTC

ops$tkyte%ORA10GR2> create table t ( x int constraint t_pk primary key );

Table created.

ops$tkyte%ORA10GR2> select constraint_name, index_name from user_constraints where table_name = 'T';

CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------
T_PK                           T_PK

 

Yes, but I want to keep my index... -- 10gR2.

Rajeshwaran, Jeyabal, November 24, 2009 - 11:40 am UTC

scott@10G> CREATE TABLE T(x NUMBER);

Table created.

scott@10G> CREATE UNIQUE INDEX T_IND ON T(x);

Index created.

scott@10G> ALTER TABLE T  ADD CONSTRAINT T_PK PRIMARY KEY (x);

Table altered.

scott@10G> SELECT INDEX_NAME 
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME ='T';

INDEX_NAME
------------------------------
T_IND

scott@10G> ALTER TABLE T DROP CONSTRAINT T_PK;

Table altered.

scott@10G> SELECT INDEX_NAME 
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME ='T';

INDEX_NAME
------------------------------
T_IND       <<<<======= Index Still Remains there, not get dropped.


Tom,
With reference to your above example (you tried in 8.1.7), I Tried this but even after Dropping the PRIMARY KEY Constraint the UNIQUE INDEX Still exists there.
Is this behaviour changed in Oralce 10gR2? I am not able to reproduce as you did above?

Thanks,
Rajesh.
Tom Kyte
November 24, 2009 - 12:18 pm UTC

yes, this changed in the 9i to 10g timeframe.

ops$tkyte%ORA9IR2> CREATE TABLE T(x NUMBER);

Table created.

ops$tkyte%ORA9IR2> CREATE UNIQUE INDEX T_IND ON T(x);

Index created.

ops$tkyte%ORA9IR2> ALTER TABLE T  ADD CONSTRAINT T_PK PRIMARY KEY (x);

Table altered.

ops$tkyte%ORA9IR2> SELECT INDEX_NAME
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME ='T';

INDEX_NAME
------------------------------
T_IND

ops$tkyte%ORA9IR2> ALTER TABLE T DROP CONSTRAINT T_PK;

Table altered.

ops$tkyte%ORA9IR2> SELECT INDEX_NAME
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME ='T';

no rows selected



and then...

ops$tkyte%ORA10GR1> CREATE TABLE T(x NUMBER);

Table created.

ops$tkyte%ORA10GR1> CREATE UNIQUE INDEX T_IND ON T(x);

Index created.

ops$tkyte%ORA10GR1> ALTER TABLE T  ADD CONSTRAINT T_PK PRIMARY KEY (x);

Table altered.

ops$tkyte%ORA10GR1> SELECT INDEX_NAME
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME ='T';

INDEX_NAME
------------------------------
T_IND

ops$tkyte%ORA10GR1> ALTER TABLE T DROP CONSTRAINT T_PK;

Table altered.

ops$tkyte%ORA10GR1> SELECT INDEX_NAME
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME ='T';

INDEX_NAME
------------------------------
T_IND




however, you can:

ops$tkyte%ORA10GR1> ALTER TABLE T  ADD CONSTRAINT T_PK PRIMARY KEY (x);

Table altered.

ops$tkyte%ORA10GR1> ALTER TABLE T DROP CONSTRAINT T_PK drop index;

Table altered.

ops$tkyte%ORA10GR1> SELECT INDEX_NAME
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME ='T';

no rows selected


A reader, June 12, 2011 - 7:16 am UTC

I have a doubt. Primary key create an index. Then why we need to create normal index. Whether Primary key index is not improve the perfomance? Why we didnt use that index?

Please help me.
Tom Kyte
June 17, 2011 - 10:13 am UTC

Primary key constraints MIGHT create an index, they might not.


The primary key will use an index - it could be an existing index.



You don't need to create a 'normal' index (whatever that is), if you want - you can just let the primary key constraint create an index if no suitable index exists.


Not really sure what you mean in this one.

Payal, July 17, 2016 - 9:58 am UTC

Hi Tom,

I work in an application that does not use primary key or foreign key. Everything is being achieved using unique index or triggers, i.e., we have unique index instead of primary key and have triggers which does the job similar to foreign key.

Would you say that is a very bad decision of developers part.
Connor McDonald
July 17, 2016 - 5:53 pm UTC

A primary key is essentially a unique constraint that has to be not null. The main difference is you can only have one primary key/table. But many unique keys. So while I'd prefer to use PKs, (not null) UCs could work.

Using triggers to maintain FKs is a bad idea though. Most people get these wrong. Especially when it comes to concurrent activity. So not only does this make more work, it introduces bugs.

Avoid all this and use foreign keys!

Chris

difference in primary key constraint & unique constraint

Ashish Agarwal, February 02, 2018 - 2:41 am UTC

what can be possible differences between primary key and unique constraint?
Chris Saxon
February 02, 2018 - 11:27 am UTC

The columns of a unique constraint can be null. The primary key columns must be not null. The database will add this constraint for you if you don't specify it.

You can have more than one unique constraint on a table. But there can be at most one primary key per table.

create table t (
  x int null primary key,
  y int null unique,
  z int null unique
);

desc t

Name Null?    Type       
---- -------- ---------- 
X    NOT NULL NUMBER(38) 
Y             NUMBER(38) 
Z             NUMBER(38) 

alter table t add primary key (z, y);

ORA-02260: table can have only one primary key

primary key on nonunique index

AndrE, June 19, 2018 - 7:53 am UTC

Hi, Tom

You have wrote: "b) you have an existing non-unique index and the primary key is on the "leading edge" of this index. We will use that index to enforce the uniqueness in the data rather then creating YET ANOTHER index (better performance/space usage)".
I have table and nonunique index for it with fields
config_field_id,key_value,value_char,program_id and primary key on this index with config_field_id,key_value.
A height of index is 4.
I run query like
"select key_value from cvchar
where config_field_id = 100162839 and
key_value = 100141360 and
value_char like 'VVV%' and
program_id = 1"
and have plan like
INDEX RANGE SCAN INDEX01_CVC (cr=5... - 5 blocks - does Oracle read auxiliary leaf block for stop key?
But if I will make primary key on config_field_id,key_value,value_char, program_id and same unique index I will have
INDEX UNIQUE SCAN CVC_PK (cr=4 - 4 blocks - one leaf block only!
Is a non-unique index bad choice here?
And where is "better performance" for non-unique index?


Thanks





Connor McDonald
June 20, 2018 - 12:27 am UTC

does Oracle read auxiliary leaf block for stop key?

Maybe yes, maybe no. We have to read to make sure we do not encounter a second value for the same "unique" value - that may be on the same leaf, it may be on an additional leaf. But yes, we are doing a little bit of extra work. Whether that is a good or a bad thing depends on your application requirements. For example, if you need a deferrable key - then you don't have the choice, it must be non-unique.

But what if say your app did heaps and heaps of:

select col1
from   my_table
where  primary_key_col = ...


Then it might be better to have an index as: (primary_key_col,col1) even though you only need 'primary_key_col', because in this way, we don't need to visit the table.

The reference to "better performance" was if you have 2 indexes instead of 1, then the cost of adding/updating/deleting rows goes up with each additional index.