Re: Creating Table as select...

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1076312809.350235_at_yasure>...
> KDB wrote:
>
> > 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!
> >
> > --KDB
>
> Don't mind at all Howard. But my answer will be a bit less kind.
>
> Whoever wrote that has little or no experience in the real-world. They
> are an Oracle employee creating documentation and likely have never had
> the experience of creating or managing applications in the real-world.
>
> If you are going to create a unique index by all means follow the above
> advice. A better question is why create one at all? You gain nothing.
> You lose a lot.
>
> BTW: The question of unique index vs. unique constraint is one I
> routinely ask during job interviews. Those that don't know the
> difference don't get hired.

Daniel, I'm a bit confused about your comment (in your 3rd paragraph) where you said "Why create one at all", referring to a unique index. Are you saying that there is a choice of not having any index (including unique) when you define a unique constraint?

Also, one of the benefits you described in your earlier thread about creating constraints instead of explicit unique indexes was that you could disable/enable the constraint but not a unique index, which would only have to be dropped and re-created. But doesn't disabling and enabling a unique constraint drop and re-create the associated unique index, which in *effect* would then be the same as explicitly dropping and re-creating a unique index?

I'm a novice, trying to understand Oracle so you're comments are much appreciated. Received on Wed Feb 11 2004 - 06:43:01 CST