Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2015
    Posts
    2

    Unanswered: Unindexed Foreign Key

    Per Oracle documentation if there is no index on a foreign, the child table will be locked by Delete/update.

    I have two tables in Oracle 11g database as below

    Store
    (Store_id varchar2(10), --- primary Key
    Store_name Varchar2(30),
    Store_desc Varchar2(60)
    )

    Store_item
    (Store_id varchar2(10), --- primary Key seq 1
    Item_id varchar2(10, --- primary Key seq 2
    Item_qty number(10,2)
    )

    alter table Store_item add constraint idx_store_item_fk foreign key (Store_id) references STORE(Store_id);

    Though Store_id is the first column of the primary key index in table STORE_ITEM, we observed a table lock when one session is trying delete rows from table STORE and another session is trying to insert rows into STORE_ITEM.

    This is happening by two jobs running in parallel and there is no way to have similar records per our validation. both jobs are part different line of business but using the same tables.
    Can you someone please hightlight possible cause of this contention?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can you someone please hightlight possible cause of this contention?

    Oracle does not know or care that based upon your implementation the two jobs won't interfere with each others records.
    The problem is that when you are about to delete any record from the parent table, without an INDEX on the FK, the whole child table must be locked so Oracle can ensure that no orphan child record exists after the parent record get deleted.
    It is your choice if the FK is indexed or not, but without the index you'll pay the price of having the Child table lock as needed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2015
    Posts
    2
    Quote Originally Posted by anacedent View Post
    >Can you someone please hightlight possible cause of this contention?

    Oracle does not know or care that based upon your implementation the two jobs won't interfere with each others records.
    The problem is that when you are about to delete any record from the parent table, without an INDEX on the FK, the whole child table must be locked so Oracle can ensure that no orphan child record exists after the parent record get deleted.
    It is your choice if the FK is indexed or not, but without the index you'll pay the price of having the Child table lock as needed.
    Sokun-----------

    If you see the table structure, foreign is part of the index (primary key) and it is the first column in the index. Do we need to create a separate index for the foreign key?
    Thanks in advance...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > Do we need to create a separate index for the foreign key?
    Why do you ask here instead of simply doing some testing to actually see for yourself what works & what does not work for you?
    Since you are getting undesirable results, you have 1 of 2 choices.
    1) Live with what you have now.
    2) Find a better solution.
    So what are you waiting for? A Written invitation to do some individualized learning?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You want to do a cascade delete on the foreign key
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •