Re: solution has been found but till no explanation

Whenever a DBA sees a field in a child table being foreign-keyed to a field in a parent table, he should slap an index on that (child) field without even thinking about it.

[Quoted] [Quoted] Because without such an index, the merest DML on the parent table will lock [Quoted] the entire child table from ALL DML for the duration of the transaction.

Such is taught on the DBA course (see the chapter on constraints) as well as the Performance Tuning course. So it's pretty basic stuff, if truth be told.

If it looks strange, it's because you don't fully appreciate the majesty that is Oracle's locking mechanism. Come to that, I doubt that there is more than one man in the world who really understands the Oracle locking mechanism (and his name is Steve Adams, by the way), so you are in good company.

The mere mortals amongst us therefore come up with workable rules and the 'index any child column involved in a foreign key relationship' is one such.

Regards
HJR "Alexander Chupin" <chupin_at_hotmail.ru> wrote in message news:98ldcm$75p$1_at_soap.pipex.net...
> D.Kravtsov in relcom.comp.dbms.oracle
> advised me to create additional
> index for field a for the child table.
>
> create index d_ind on d(a);
>
> It does work. But till it looks strange.
>
> WBR, Alexander Chupin
>
>
>
Received on Wed Mar 14 2001 - 13:24:15 CET