Re: (corrected) 2 Oracle doubts

Hi!

> A primary key shouldn't 'normally' use a unique index. On the contrary,
all
> Primary and Unique Key constraints should 'normally' be made deferrable,
> which causes non-unique indexes to be used. This should not affect your
> test, though the precise details of the execution plans produced would
> vary.

You got good point here, especially with huge indexes ... It's just my "mileage", that when I know a constraint shouldn't ever be disabled I don't hesitate to make unique index for it, thus physically enforcing uniqueness as well. But yep I agree with you, that there's always some exceptions when a non-unique index might come handy.

>
> Unique indexes are dropped when the constraint is disabled. Non-unique
> indexes are retained. Having large indexes silently disappear on you,
> without a word of warning and without seeking confirmation, is not a good
> idea IMHO. In earlier versions, this advice would have caused its own
> problems (such as, when you are a doing a massive SQL Loader run, you
> probably *want* the index to be dropped, and now you have to remember
> yourself to do that as a seaprate exercise). But even that goes in 9iR2
> because you can now say 'alter table X disable constraint Y drop index',
> thus getting the drop done at the same time as the disabling.

Btw, there's also "keep index" clause which keeps even system generated non-unique indexes when constraint is disabled.

>
> Rules of thumb are always tricky things, I agree. But if there have to be
> some, deferrable constraints are a better way to go than non-deferrable or
> unique index-enforced one, simply because of the administrative
> consequences of choosing the latter.

Yep, there shouldn't be any performance penalty as long the uniqueness is defined on constraint level, why not keep your options open...

Cheers,
Tanel. Received on Sun Jul 27 2003 - 17:30:35 CDT