Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Unanswered: delete query is taking long time

    Hi,
    Simple delete query is taking very long time to delete records.i.e

    "Delete from INVENTORY where ITEM_ID='3190' "

    INVENTORY table is master table for 9 other tables(child).

    All the data with ITEM_ID='3190' is deleted from the child tables.

    Is it possible to make the deletion faster?

    Thanks in Advance
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: delete query is taking long time

    Silly question: I take it there are indexes on the FK columns of all these 9 child tables?

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296

    Re: delete query is taking long time

    Originally posted by andrewst
    Silly question: I take it there are indexes on the FK columns of all these 9 child tables?
    You already know the answer to that Andrew
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Well i think its not silly question. I can not simply remove all the foreign keys, as it is production schema and lot of data is loading to
    all child tables.
    My question : Is it possible to make deletion faster with out removing
    fK's from child table.
    Hope thinks are now clear?

    Thanks
    Pagnint
    (No need to search web before posting new question)

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I wasn't suggesting you remove the foreign keys; I was asking whether for each foreign key there is also defined an index on the same column. For example:

    CREATE TABLE child1( ..., item_id REFERENCES inventory, ... );
    CREATE INDEX child1_idx ON child1 (item_id);

    Without such an index, deletion from the inventory table will perform very badly. So every child table must have an index on the foreign key.

  6. #6
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi Andrew,
    Sorry for replying late. As you said i have created Indexes for all
    the foreign keys tables. But still deletion is slower.
    I didnt get one point even though there are no records with item_id='3190' in all the foreign key tables, those tables are getting lock
    and deletion is much slower. Can you help me on this point.?

    Thanks
    Pagnint
    (No need to search web before posting new question)

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It doesn't matter whether '3190' is referenced by any rows in child tables or not, without an index on the child table's foreign key column Oracle will:
    a) take out a table level lock on the child table
    b) have to perform a full table scan on the child table to determine whether any child exists or not.

    Both these issues will kill your performance. Simple example:

    PHP Code:
    SQLcreate table parentpid integer primary keypname varchar2(10) );

    Table created.

    SQLinsert into parent values (3190'nokids');

    1 row created.

    SQLcreate table childcid integer primary keypid references parent );

    Table created.

    SQLinsert into child values (1null);

    1 row created.

    SQLcommit;

    Commit complete.

    SQLselect from parent;

           
    PID PNAME
    ---------- ----------
          
    3190 nokids

    SQL
    select from child;

           
    CID        PID
    ---------- ----------
             

    Here we have a parent record 3190 that is not referenced by the single child record, and no index on the foreign key. Now let's have 2 users work on the database:

    PHP Code:
    user1delete child where cid=1;

    1 row deleted.

    user2delete parent where pid=3190
    At this point, user2 is blocked because it wants to lock every row in child while it enforced the foreign key constraint. It waits until user1 either commits or rolls back:
    PHP Code:
    user1rollback;

    Rollback complete.
    ...
    1 row deleted.

    user2
    Based on what you have said I am still pretty confident that you have an un-indexed foreign key on one of those child tables!

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Also if you are using CBO, be sure to analyze the indexes ...

    Gregg

  9. #9
    Join Date
    Nov 2003
    Posts
    48

    Alternative solution

    Instead of having indexes on child foreign keys, if you are sure that the records to be deleted are not referenced by any foreign keys, you can also do the following to speed up deletion:

    1. disable all the foreign keys from child tables that referencing the deletion table
    2. do the deletion
    3. re-enable the disabled foreign keys
    Shianmiin

  10. #10
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    Quote Originally Posted by paginit
    Hi,
    Simple delete query is taking very long time to delete records.i.e

    "Delete from INVENTORY where ITEM_ID='3190' "

    INVENTORY table is master table for 9 other tables(child).

    All the data with ITEM_ID='3190' is deleted from the child tables.

    Is it possible to make the deletion faster?

    Thanks in Advance
    Way 1:
    please create index in all those 9 tables on ITEM_ID referenced column. perform delete.

    way 2 :
    disable foreign key constraint on those 9 tables. Perform delete. enable the disabled foreign key constraints.

    though the way 2 is faster but I prefer the way 1
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

  11. #11
    Join Date
    Nov 2003
    Posts
    48
    Quote Originally Posted by hasan_uiu
    Way 1:
    please create index in all those 9 tables on ITEM_ID referenced column. perform delete.

    way 2 :
    disable foreign key constraint on those 9 tables. Perform delete. enable the disabled foreign key constraints.

    though the way 2 is faster but I prefer the way 1
    Some thoughts:
    If it's a one-time thing, I would probably go with way 2. it's faster because it doesn't need to check foreign key constraints. The drawback is the if there are foreign key violations, you will only find it out when you try to re-enable the foreign key and have to resolve that then. Thus it's better do the checking before the actual deletion. Besides, since the disable/enable foreign keys will affect all the concurrent users that access the same set of tables, thus it's more suitable/safer do it when there are no other users accessing those table. e.g. in nightly batch process or offline database cleanup.

    if it's a continuous thing, I think way 1 is better since it does the referential integrity check in the same transaction, and thus safer. Still have to consider the extra work of maintain the indexes when the system is heavy loaded. The good thing is you can just set it up, forget about it and leave it to database to do the work for you.
    Shianmiin

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I doubt OP cares after 5+ years.
    Enjoy the discourse!
    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.

  13. #13
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I may be wrong, but if this is still an issue after 5 years, OP is probably not the DBA in charge

  14. #14
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    I've seen some slow deletes in my time, but 5 years has to be some kind of record!


    PS. How does one actually find a five year old post and start answering it like it were new?

Posting Permissions

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