Re: Creating Table as select...

"Toby Brown" <toby_brown_at_optusnet.com.au> wrote in message news:2a84f50c.0402110443.73ee1d67_at_posting.google.com...

> > 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?

No, he's saying "why create unique indexes at all, when a unique constraint can be enforced perfectly well with a non-unique index" (though see Jonathan's post earlier in this thread for some bug-related caveats to that -although I don't think they change the outcome of the argument in the end). If you create a unique constraint, you will *always* get an index created to help enforce it. The issue is whether that index has to be unique or not (it doesn't), and also whether it's better to create the constraint and get the index created for you, or create your own index first (do the constraint first, is my general advice).

>
> 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 think Daniel had not had his early coffee when he wrote that. See Richard's post earlier in the thread about that. The point is that if you create a unique index on a column, without there necessarily being a unique constraint, then that column must always have uniqueness enforced unless you choose to drop the index. And you can't get uniqueness switched on again unless you rebuild the index. But if you create a unique constraint then you get an index created for you which, under the right circumstances, will be a NON-unique index. Therefore, you can disable or enable the uniqueness constraint, without the need to drop and re-create the nonunique index. A disabled unique constraint won't mind the existence of a non-unique index, and an enabled one can use a non-unique index as effectively as a unique one.

>
> I'm a novice, trying to understand Oracle so you're comments are much
> appreciated.

I've kind of lost track a little bit where this thread started and where it's going, so let me back up a bit and put it in words of few syllables. Never create your own unique indexes. Always make your unique constraints deferrable. "Never" and "Always" are strong words, and some bright spark is bound to pipe up pointing out exceptions. But for novices, I like simple guidelines that are generally true... and those are they.

Regards
HJR

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