Re: Unique contraints vs. Unique indexes

"Robert Fazio" <rfazio_at_home.com.nospam> wrote in message news:%Yje7.104174$EP6.26776382_at_news1.rdc2.pa.home.com...
> --
> Robert Fazio
> Senior Technical Analyst
> dbabob_at_yahoo.com
>
> "Howard J. Rogers" <howardjr_at_www.com> wrote in message
> news:3b78fa2d_at_usenet.per.paradox.net.au...
> >
> > Personally, I think you'd be utterly mad NOT to create them deferrable.
 If
> > those constraints are enforced with a unique index, then the index is
> > dropped the second you disable the constraint, and thus will need
 complete
> > re-creation.
>
> I agree with that on very large tables, which is what we are talking
 about,
> but enlighten me on two items.
>
> One. I personally can't think of a reason where I would really want to
 wait
> until commit to check the uniqueness of my values. Can you give me an
> example?

The classic example is a self-referencing foreign key. Employees have managers. All managers must be employees.

If employee 1000 Howard is managed by employee 9000 Larry, and I create a foreign key relationship between manager_id and employee_id, how can I ever enter the employee howard, manager Larry, unless the constraint is either disabled (dodgy, because potential duff data could be inserted), or deferred.... provided I enter '1000, Howard, 9000.... 9000, Larry, NULL' and *then* hit the commit button, the load will commit fine with a deferred constraint.

There are all sorts of other examples I can think of where you enter data which temporarily violates every constraint known to Man, but you know damn well that by the time it comes to commit the thing will have been fixed up.

Bulk loads with deferred constraints also go faster.

>
> Two. Realizing that the values will actually be unique, even though the
> index isn't, an explain plan will of course show a range scan rather than
 a
> unique scan. The cost associated with a range scan is higher, and I can
> guess that index might be skipped over in some rare instances. And along
> the same lines, I don't believe it to be the case, but is there additional
> overhead with the non-unique index?
>

There is no performance degradation with non-unique indexes at all that I have ever been able to measure (or, slightly more convincingly, that Steve Adams has been able to measure -and if he can't do it, I doubt anyone else can).

I believe Jonathan Lewis posted the other day that the optimiser is canny enough to work out that even though it appears as a non-unique index, it knows it is being used to supposrt a unique or primary key constraint, and can thus be treated as a unique.

> > But who said being a DBA was a walk in the park?!
>
> Hey don't make fun of what I do all day.
>
> Actually lately you have been making me feel like that is all that I do.

I certainly don't mean to. I actually enjoy this stuff *nearly*as much as a walk in the park anyway! Guess that makes me a sad git.

> Guess I have been spending too much time doing project management rather
> than hitting the books.
>

Get a nice bottle of red instead. Far more satisfying!

Regards
HJR
>
Received on Wed Aug 15 2001 - 05:51:26 CDT