-
Thanks Modic.
I got the true picture. I missed the insert part that you demonstrated.
Because you disabled Oracle from doing it the way it normaly does it with your DISABLE TABLE LOCK command. But in reality you will never use that command, would you? So would you have a locking isues if you don't have an index on that foreign key? No, of course not, if you would allow Oracle to do the things as it was deseigned for.
In an OPS environment, I used DISABLE TABLE LOCK, that's different story.
Tamil
-
Tamilselvan,
I understand, it locks the child table record when we delete partent record(ON DELETE CASCADE). Why we need index on FK's? I am not really understand why we need index on FK's. Can you please explain.
Govind
Originally Posted by tamilselvan
NO.
Indexes on FK are needed otherwise you will have locking issues.
Tamil
-
Originally Posted by pranavgovind
Tamilselvan,
I understand, it locks the child table record when we delete partent record(ON DELETE CASCADE). Why we need index on FK's? I am not really understand why we need index on FK's. Can you please explain.
Govind
Please read the post from the beginning.
Modic and I explained. Prior to 9i, Oracle would lock the child table for the duration of the transaction if there is NO FK index, so other transaction waits until the previous transaction commits/rollbacks. As Modic explained, 9i and 10g place shared lock on the child table but do not prevent other transactions to do DML on other rows on the child table.
Tamil
-
they might also be needed for performance reasons as well
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|