| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> RE: diff. between Primary-Key and unique index
I think our good friend was asking if there is a difference in
performances... as we all know some basics of the relational theory, or at
least figure out to know.
As far as can see in my pieces of code, the UNIQUE INDEX SCAN doesn't perform faster if it a PK index or a UNIQUE index. There is however a big difference, if you define the PK as the organization index for an IOT (not possible with a mere unique index). There, you can have a really big diffence.
Regards,
Frederic.
-----Original Message-----
From: =?ISO-8859-1?Q?"DI_Karl_Heinz_H=F6rmann"_
[mailto:kh.hoermann_at_penta.at]
Sent: 25. januar 2000 16:28
To: comp.databases.oracle.server_at_list.deja.com
Subject: Re: diff. between Primary-Key and unique index
Message from the Deja.com forum:
comp.databases.oracle.server
Your subscription is set to individual email delivery
> Andreas Michler <Andreas.Michler_at_adicom.de> a écrit dans l'article
> <388819AE.547713F0_at_adicom.de>...
> Can anyone tell me the differences between a table with primary key
> and
> a table with no primary key, but a unique index on some fields
> ????????
we shouldn't mess things up, therefore I try to explain:
the 'primary key' in the relational theory is a set of attributes of a tuple, which are discovered to be 'discriminant' for a given tuple in the analysis phase - this means
given a tuple carrying the attributes (a, b, c, d, e, f, g) and the analysi=
s
yields that
a, b -> c, d, e, f, g we call a and b the 'discriminant columns' of the
tuple, this property is equivalent to be a 'candidate primary key' -
since these 'discriminant columns' describe the tuple they cannot be null -
because null means - not currently known - ex falso sequitur quodlibet ...
so what could you derive from 'i dunno' ??
and have to be unique - of course ...
but you don't really need a pk in theory - because any tuple is per se
unique by definition -
in the technical implementation and in a consequence of the data modelling
we start using the primary key to speed up access and enforce uniqueness fo=
r
the discriminant columns -
by use of the primary key constraint -
this does three things for us:
1) in the repository, our discriminant columns become known for the
table - so you can reference the tables pk with a foreign key constraint
later on from other tables -
2) these columns are per definition set to be NOT NULL
3) in anticipation of heavy access using these columns, and to enforce th=
e
uniqueness of this set of columns, ORACLE just puts a unique index upon
them - automatically -
so far for the primary key
unique indexing is
a) a tool to ease the load of enforcing uniqueness upon a (set of)
column(s)
b) opening a fast access path into a table via a (set of) column(s)
it doesn't either imply 'discriminant properties' nor 'not null' thats it - no more
hope I could help
Sent via Deja.com http://www.deja.com/ Before you buy. Received on Tue Feb 01 2000 - 14:23:15 CST
 |
 |