Unused indexes - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Unused indexes

  1. #11
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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

  2. #12
    Join Date
    Dec 2005
    Posts
    195
    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

    Quote Originally Posted by tamilselvan
    NO.
    Indexes on FK are needed otherwise you will have locking issues.

    Tamil

  3. #13
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Quote 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

  4. #14
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    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
  •  



Click Here to Expand Forum to Full Width