Re: Index no needed in child table in Oracle9i?

I think Howard Rogers and Tom Kyte went through this one a short time ago.

Under Oracle 9:
When you update the parent value, or delete the parent row, Oracle tries to take out a share lock on the child table. If there are any outstanding updates on the child other transactions, this lock has to wait for them to commit, and whilst it is waiting it blocks other transactions that want to update the child.

When the lock is acquired, Oracle updates the parent as required, and releases the child lock immediately. Consequently other session may continue to execute transactions against the child table, and don't have to wait for the first transaction to commit.

This may be sufficient on a system with a relatively low rate of activity, and only occasionally hacks on the parent table, to avoid the need for the child index.

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

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

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

Author of:
Practical Oracle 8i: Building Efficient Databases


Anonymous wrote in message
<8ee5d7c9.0201012313.645968e0_at_posting.google.com>...


>Hi all,

>

>    I remember in Oracle8i, I need to create index on child table (to

>prevent entire child table locked by parent table DML). I heard that

>in Oracle9i, no need to create index on those columns. Is that true?

>And how Oracle resolves this locking problem in Oracle9i?

>

>Thanks,

>David