Re: (corrected) 2 Oracle doubts

On Sun, 27 Jul 2003 21:53:50 +0300, Tanel Poder <tanel@@peldik.com> wrote:

[Excellent post snipped]

A very nice example of working things out from scratch, Tanel. So thank you for that.

Just one minor niggle: You said that

"Note that this is a unique index (which a primary key should normally use..)"

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.

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.

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.

None of which takes anything away from the substance of your post.

~QM Received on Sun Jul 27 2003 - 14:57:11 CDT