Home » RDBMS Server » Server Administration » Unique index question (General question)
Unique index question [message #331191] Wed, 02 July 2008 09:36 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Oracle recommends that unique indexes be created explicitly, using CREATE UNIQUE
INDEX. Creating unique indexes through a primary key or unique constraint is not
guaranteed to create a new index, and the index they create is not guaranteed to be a
unique index.


Its reffering to above given text in Oracle document .
When we create a constraint on a table(eg : Primay key) ,it automatically creates an index .now the question is ..

1)What is the purpose of explicitly creating an Index?
2)As mentioned in the statement above, why uniqueness is not guaranteed ?
Re: Unique index question [message #331198 is a reply to message #331191] Wed, 02 July 2008 09:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Its reffering to above given text in Oracle document

Which one? Give a link.

Quote:
When we create a constraint on a table(eg : Primay key) ,it automatically creates an index

Wrong.
It creates an index only if it is needed. That is:
a) The constraint needs to be enforced (not created in disable state)
b) An index is needed to enforce the constraint (not the case for a check constraint)
c) There is no existed index that can already do this

1/ To have the index you want
2/ Because Oracle does not need a unique index to enforce a primary or unique key

Regards
Michel
Re: Unique index question [message #331221 is a reply to message #331191] Wed, 02 July 2008 11:31 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Does it mean all primary key,unique key constraits SHOULD always have an associated index ?
If the index is not existing ,it will create an index on its own(eg name :SYS_C005420) .
If one is there already on that column ,it will just make use of that .

is it correct ?
Re: Unique index question [message #331222 is a reply to message #331221] Wed, 02 July 2008 11:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
gkrishn wrote on Wed, 02 July 2008 09:31
Does it mean all primary key,unique key constraits SHOULD always have an associated index ?
If the index is not existing ,it will create an index on its own(eg name :SYS_C005420) .
If one is there already on that column ,it will just make use of that .

is it correct ?


Why do you ask these questions here & then wait for an answer (which could be wrong);
as opposed to running some simple tests yourself to see the results?
Re: Unique index question [message #331223 is a reply to message #331191] Wed, 02 July 2008 11:39 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
I did couple of test and the concept is confusing me.

if everyone can test and learn everything what is the need for this forum .I see you are always arrogant to every forum users.
Re: Unique index question [message #331224 is a reply to message #331221] Wed, 02 July 2008 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Does it mean all primary key,unique key constraits SHOULD always have an associated index ?

If it is enable.

Regards
Michel
Re: Unique index question [message #331461 is a reply to message #331191] Thu, 03 July 2008 07:41 Go to previous message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Thanks Mike !
Previous Topic: SYSDBA
Next Topic: ORA-14450: attempt to access a transactional temp table already in use
Goto Forum:
  


Current Time: Sat Dec 07 02:38:11 CST 2024