Re: Constraints and indexes

One can use the following query to view the connection between the constraints and the indexes used to check the constraint:

        select  --+ rule
            o.owner as index_owner
            , o.object_name as index_name
            , n.name as constraint_name
        from    sys.cdef$ c
            , dba_objects o
            , sys.con$ n
        where c.enabled = o.object_id
        and c.con# = n.con#
        and n.owner# = uid
        /

The first camp makes a problem when a constraint (PK or UK) on columns a,b is checked with an index on columns b,a or a,b,c or b,a,c or ..., but not the index on a,b. I would say, who cares, my indexes are for performance issues, my constraints are impossed by the business! (I am from the second camp)

"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)
>
Received on Mon Oct 20 2003 - 18:07:22 CDT