Re: newbies index question?

"Julien Reynier" <julien.reynier_at_free.fr> wrote in message news:3A994ACE.C8EC2405_at_free.fr...
> Hi,
>
> I would like to know if I have to create index before add primary key
> constraint or not? In others words should I create my own indexes or
> should I use auto created indexes generated during primary key
> constraint setting?
>

It's entirely up to you. Obviously, getting Oracle to do the Index work by simply declaring that you want a constraint on a table makes life a bit easier. But having your own indexes first, and then instructing Oracle to use them for constraint enforcement will work fine, too.

There is one particular benefit from creating your own indexes first and then having Oracle use them for the constraint... if you ever disable the constraint (because, say, you are about to do a giant data load), then any indexes which Oracle itself generated to enforce unique and primary key constraints are *dropped*, automatically. You'll then have to sit back and wait for Christmas when you re-enable the constraints, as the indexes are rebuilt from scratch. By contrast, if you created your own indexes and then had Oracle use them for the constraints, disabling the constraint will NOT result in the index being dropped.

Now whether not having indexes disappear automatically on you is a good thing or not, only you can decide. Personally, I prefer to be in charge of when segments go walkabout, and hence I would always arrange for my unique/primary key enforcing indexes to be me-generated.

If you have Oracle 8.0 or above, then the discussion becomes moot, because in 8.0, you can declare a unique or primary key constraint to be 'deferrable initially deferred' or 'deferrable initially immediate' -and in either case, the use of the keyword 'deferrable' means that Oracle will automatically generate the index as before, but it will be of a type that is NOT dropped automatically when the constraint is disabled. So now you have the best of both worlds.

HTH
Regards
HJR
> Thanks...
Received on Sun Feb 25 2001 - 13:01:23 CST