Re: foreign key constraint and the index_name in user_constraint table
From: Jared Still <jkstill@xxxxxxxxx>
To: nn20002@xxxxxxxxxxxx
Date: Thu, 30 Sep 2004 17:44:56 -0700
On Thu, 30 Sep 2004 19:51:12 -0400, nn20002@xxxxxxxxxxxx
<nn20002@xxxxxxxxxxxx> 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
