Home » SQL & PL/SQL » SQL & PL/SQL » Unique Cosntraint Without Unique Index (9.2.0.5 Sun O.S 5.7)
Unique Cosntraint Without Unique Index [message #281407] Fri, 16 November 2007 13:45 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

if we create a Unique Constraint, Oracle itself creates a Unique Index for that column(s).
On a huge tables like 450 millions(+) i don't want that extra Index, Is there a way where we can create only Unique Constraint without Unique index;



thanks

Re: Unique Cosntraint Without Unique Index [message #281409 is a reply to message #281407] Fri, 16 November 2007 13:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle implements the unique constraint using an index.
This is the way it works.

Regards
Michel
Re: Unique Cosntraint Without Unique Index [message #281423 is a reply to message #281407] Fri, 16 November 2007 16:09 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
And if you think about it, how would it enforce a unique constraint without an index?????, by using a full table scan that would take a 1000 times longer?
Re: Unique Cosntraint Without Unique Index [message #281835 is a reply to message #281407] Mon, 19 November 2007 08:14 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
faiz_hyd wrote on Fri, 16 November 2007 14:45

Hi,

if we create a Unique Constraint, Oracle itself creates a Unique Index for that column(s).



Why don't you create a primary key instead. Oracle will use an already existing non-unique index if it can.
Re: Unique Cosntraint Without Unique Index [message #281848 is a reply to message #281835] Mon, 19 November 2007 09:11 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:

Why don't you create a primary key instead. Oracle will use an already existing non-unique index if it can.

Just as it will for a unique constraint. Smile
Previous Topic: passing array to function
Next Topic: Maximum allowed in IN Clause in Select Statement
Goto Forum:
  


Current Time: Sat Dec 03 20:04:23 CST 2016

Total time taken to generate the page: 0.05180 seconds