Re: Is index used for constraint enforcement?

I think that's a feature that appeared in v9, possibly 9.2

For 8.1, you need to look at the sys tables cdef$, con$ and ind$.

con$ hold the name and con# for the constraint, cdef$ has the con# and a column called enabled. If the constraint is enforced by an index, then the enabled column matches the obj# column of the relevant index in the ind$ table.

I think Tom Kyte has a script that does this check on the asktom site.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Finland__September 22nd - 24th
____Norway___September 25th - 26th
____UK_______December (UKOUG conference)

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message
news:3F54BDE0.E9168F28_at_remove_spam.peasland.com...


> Go back and look at USER_CONSTRAINTS again. If the constraint

requires


> an index, then the INDEX_OWNER, INDEX_NAME columns of

USER_CONSTRAINTS


> will tell you which index that specific constraint uses.

>

> HTH,

> Brian

>

> Marko Kimpel wrote:

> >

> > I'm using Oracle 8.1.7.

> >

> > How can I determine if a given table index is required for

enforcement


> > of a constraint (e.g. unique or pk constraint)? Didn't find

usefull


> > information in USER_INDEXES or USER_CONSTRAINTS.

> >

> > (Assuming the same name of index and constraint does not work

since


> > Oracle may use an existing index when creating an constraint and

thus


> > use different names.)

> >

> > Marko

>

> --

> ===================================================================

>

> Brian Peasland

> dba_at_remove_spam.peasland.com

>

> Remove the "remove_spam." from the email address to email me.

>

>

> "I can give it to you cheap, quick, and good. Now pick two out of

>  the three"