| Unique constraint versus unique index [message #159621] |
Mon, 20 February 2006 11:21  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
My application is very insert intensive and I have multiple indexes defined as unique just to ensure the there'll be no duplicate values.
I was wondering in which case the overhead will be less - when defining unique constraint or unique index. Again, the indexes is not in use in the select/update statements.
Thanks a lot for the help, mj
|
|
|
|
|
|
| Re: Unique constraint versus unique index [message #159673 is a reply to message #159638] |
Mon, 20 February 2006 20:56  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
An ENABLED Unique or PK constraint creates a Unique index if one does not already exist. A DISABLED constraint requires no index, but nor is it validated (it is documentation only).
The only differences that I know of are:
- A function-based index cannot be a Unique Key
- A Foreign Key must reference either a Unique or Primary Key - a unique index is insufficient.
If you load data in bulk, you can drop your index, load, and recreate it. Depending on the data volumes, this might be beneficial.
If you bulk load with SQL*Loader, DIRECT PATH load will defer the maintenance of indexes, making it redundant (and slower) to drop and recreate them. Even better, you can pre-sort the data file in index order for an even faster load.
_____________
Ross Leishman
|
|
|
|
| Re: Unique constraint versus unique index [message #159754 is a reply to message #159673] |
Tue, 21 February 2006 04:57 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Ross,
is there a difference performance wise?
As for as i know, NO. There is no difference. CBO will treat both the same. So falling back to the basic concepts, i assert that presence of index is painful for inserts and may be useful for selects. But the OP is not using the indexes for selects.
why have them?
regards
Edit:
Just re-read the post from OP.
>>defined as unique just to ensure the there'll be no duplicate values.
So we cannot avoid having indexes here.
>>I was wondering in which case the overhead will be less - when defining unique constraint or unique index.
There will be no difference.
[Updated on: Tue, 21 February 2006 07:06] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
