OraFAQ Forum: Server Administration » Unique index question

Home » RDBMS Server » Server Administration » Unique index question (General question)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Unique index question [message #331191]

Wed, 02 July 2008 09:36

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 ?

Report message to a moderator

Re: Unique index question [message #331198 is a reply to message #331191]

Wed, 02 July 2008 09:51

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

Report message to a moderator

Re: Unique index question [message #331221 is a reply to message #331191]

Wed, 02 July 2008 11:31

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 ?

Report message to a moderator

Re: Unique index question [message #331222 is a reply to message #331221]

Wed, 02 July 2008 11:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

gkrishn wrote on Wed, 02 July 2008 09:31

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?

Report message to a moderator

Re: Unique index question [message #331223 is a reply to message #331191]

Wed, 02 July 2008 11:39

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.

Report message to a moderator

Re: Unique index question [message #331224 is a reply to message #331221]

Wed, 02 July 2008 11:51

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

Report message to a moderator

Re: Unique index question [message #331461 is a reply to message #331191]

Thu, 03 July 2008 07:41

gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen

Senior Member

Thanks Mike !

Report message to a moderator

Previous Topic:	SYSDBA
Next Topic:	ORA-14450: attempt to access a transactional temp table already in use

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 22:35:37 CST 2024