| 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 -> Preferred method in creating primary key
Hi Thomas,
>> What is the advantage of first specifying the index, and then
defining the
>> primary key?
>> I thought Oracle will create a unique index on the table anyway when
I define a
>>primary key...
In Oracle 7 and new index was always created to enforce a unique or PK constraint.
From Oracle 8.0 this was not necessarily the case. Any existing index (unique or not) could be chosen and used by Oracle to enforce a unique or PK constraint. The problem is that if you subsequently disable or drop the constraint the index supporting it goes as well - whether Oracle created it or not. Not a good idea.
Now we can create a constraint and specify USING INDEX which will make Oracle create a brand new index to enforce the constraint, or we can use USING INDEX name_of_existing_index and the constraint will be enforced using the named index.
Now a further problems is this, you create your table with columns a, b,
c, d ... and build an index on a,b,c.
You then create a PK constraint on a,b so Oracle will use your onw a.b.c
index to enforce it.
You subsequently drop the constraint (or disable it) and your own a,b,c
index gets dropped quietly.
When you enable it again, there is no suitable index to use, so Oracle
creates one using a,b only. So you have lost your a,b,c index and gained
an a,b index in its place - which may neot be as useful !
Cheers,
Norm.
Tel: 0113 289 6265 Fax: 0113 289 3146 URL: http://www.Lynx-FS.com -------------------------------------Received on Wed Aug 13 2003 - 07:54:15 CDT
 |
 |