Re: Constraints and indexes

Luc Gyselinck wrote:

>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)
>>
>>
>>

Personally I think the entire concept of kidnapping an index nothing but a demonstration that
someone didn't spend enough time working on application design before banging code.

Consider this: The first thing you should design are your tables and their constraints. Indexes
should be created in a secondary step as indexes only serve the purpose of speeding up access
and that which needs to be sped up is not something necessarily known when the logical and
physical design is created.

So if an index exists before the constraint, at least to me, that means someone gave inadequate
attention to the design. And as I like my constraint and index names to match I always use the
USING INDEX syntax.

-- 
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)