Re: PK vs Unique index

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 16 Jan 2002 05:05:39 -0800
Message-ID: <a23tr302fcs_at_drn.newsguy.com>


In article <kQW08.45721$_p.11054374_at_typhoon.tampabay.rr.com>, "Philip says...
>
>Is there any performance difference is you use a PK constraint vs just
>creating a unique index. I know the PK will create the unique index as
>well, but is there a disadvantage to creating just a index and not a PK
>constraint
>
>Thanks
>Phil
>
>

Yes, there can be.

select count(*) from t -- that can do an index fast full scan of the primary key index where as in general a unique index cannot be (unless you also define at least ONE of the columns in the index as NOT NULL)

query rewrite, especially important in a data warehouse, will not be nearly as functional without a primary key. A unique index is not enough to work with query rewrite -- you need the primary key.

The bottom line is that the primary key is not only "an index", its meta-data. It is information. It is a clue. The existence of a primary key need not even have an index (it can be disabled, its just meta data then).

I would suggest you always use the primary key and if you want to, create the [Quoted] [Quoted] index manually before hand or use the USING INDEX clause. A primary key is much more then a unique index (doesn't even have to use a unique index actually)...

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Wed Jan 16 2002 - 14:05:39 CET

Original text of this message