Re: Constraints and indexes

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1066689524.574812_at_yasure...
> Luc Gyselinck wrote:
>
> >We are running Oracle 8.1.7.4.0
> >
> >I am in a never ending discussion about how primary keys and unqiue keys
> >should be created. So I need a third opinion.
> >
> >One camp says one should create constraints first with the USING INDEX
> >clause, so the indexes that support the constraints, are created first.
The
> >other indexes are created afterwards.
> >
> >The other camp says that one can create the indexes first and the
> >constraints afterwards. The constraints will (using Tom Kyte's words)
> >'kidnap' an index. Of course, you can end up with constraints that do not
> >use the 'correct' index. Does it matter? Constraints are constraints,
> >indexes are indexes!
> >
> >What if the first camp does an export of the database, followed by an
import
> >into another? Import creates the indexes first and then the constraints,
> >wright?
> >
> >--
> >Luc Gyselinck
> >
> >
> I fail to see any point to the question. A primary key constraint
> consists of a table constraint and an
> associated unique index. A unique constraint consists of a column
> constraint and an associated unique
> index. Why would anyone care which came first ... the chicken or the egg?
>
> If the index doesn't already exist use the USING INDEX clause to define
> the index's storage parameters
> and tablespace. If it does it does ... kidnap it and move on.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>

As an addition to Daniel's comments, it is worth noting that as from Oracle 9i (9.0), the USING INDEX clause can actually specify the index name. This means that you are able to control which index is "kidnapped" (so to speak).

A minor point is that you cannot use a qualifier but then you should not using an index from another schema as a supporting index!

Cheers Received on Mon Oct 20 2003 - 18:15:11 CDT