Re: foreign key constraint and the index_name in user_constraint table

On Thu, 30 Sep 2004 19:51:12 -0400, nn20002_at_netscape.net <nn20002_at_netscape.net> wrote:
> Now, how do I know that the foreign key constraint is using the index or not.

Assuming an update is taking place on one of the tables

*) look for table locks. the parent or child table will be locked

   ( dependant on oracle version) if the index is not used. *) use a 10046 trace. It will show index usage. *) look at the execution plan, either via explain plan or autotrace. I believe the index usage will appear there for an update statement. (though I'm too lazy to check at the moment, you do it ;)

Best thing to do is build parent/child tables and do some testing, so that you know what is going on when the foreign key is used, both with and without an index.

The concepts manual will give you more info.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l