Re: Creating Table as select...

"KDB" <itzkdb_at_rediffmail.com> wrote in message news:c8ca7368.0402082121.20474a59_at_posting.google.com...
> Hi Daniel,
> I read the following in oracle 10g concepts manual:
>
>

http://download-west.oracle.com/docs/cd/B12037_01/server.101/b10743/schema.htm#sthref893
>
> <Oracle10gConcepts>
>
> Unique and Nonunique Indexes
> Indexes can be unique or nonunique. Unique indexes guarantee that no
> two rows of a table have duplicate values in the key column (or
> columns). Nonunique indexes do not impose this restriction on the
> column values.
>
> Oracle recommends that unique indexes be created explicitly, and not
> through enabling a unique constraint on a table.
>
> </Oracle10gConcepts>
>
> Can you please comment on this!
>

Well, I hope you won't mind me butting in, but I read this to mean 'if you must have unique indexes, create them explicitly yourself... but don't let the creation of a unique constraint cause a unique index to be 'silently' created (which is what happens unless you make the constraint deferrable)'. In other words, I believe that Oracle is NOT recommending that you use unique indexes, but that if you *must* have them, do the job yourself and take control of the process.

Although I have to say that if this is indeed what Oracle means by this statement, they have done a fine job of obfuscating their intentions!

The fact that this is what they are attempting to say is confirmed by their SQL reference guide to creating constraints: "When you specify a unique constraint on one or more columns, Oracle implicitly creates an index on the unique key. If you are defining uniqueness for purposes of query performance, then Oracle Corporation recommends that you instead create the unique index explicitly using a CREATE UNIQUE INDEX statement". The words "if" and "instead" there give the game away.

Regards
HJR

-- 
--------------------------------------------
Oracle Insights: www.dizwell.com
--------------------------------------------