Advisors - Tune SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Advisors - Tune SQL

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    Hi Advisors,

    I have a delete statement to delete from table A for all the records in table B. Query and Execution plan is given below


    SQL>
    delete from acc_partition partition (part02) a
    where a.account_number in (select b.account_number
    from account_delete_hlp b
    where b.account_number = a.account_number)

    Explain plan:
    DELETE STATEMENT 582
    DELETE ACC_PARTITION
    FILTER
    TABLE ACCESS FULL ACC_PARTITION 582
    FILTER
    INDEX RANGE SCAN ACC_DEL_HLP_IDX 1


    while 582 is the cost of the statement. Is't it a expensive statement.
    The number of records to be deleted in A is 119000
    I have indexes on account number in both A and B
    I am using a 'Large RBS' and altered table A to nologging.


    Delete takes 25 Minutes.
    What else can I change in the sql to make it much faster.
    Can you please suggest me how to tune it further?

    Badrinath

  2. #2
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    Just use the following statement:

    [b]delete from acc_partition partition (part02)
    where account_number in (select account_number
    from account_delete_hlp) [/b]

    Sergey.



  3. #3
    Join Date
    Jan 2001
    Posts
    642
    hey pss,
    In that case, It does not make use of the index:
    I tried it , Here is the explain plan

    DELETE ACC_PARTITION
    NESTED LOOPS 167
    VIEW VW_NSO_1 3
    SORT UNIQUE 3
    TABLE ACCESS FULL ACCOUNT_DELETE_HLP 1
    TABLE ACCESS BY LOCAL INDEX ROWID ACC_PARTITION 2
    INDEX RANGE SCAN HLP_LOC_IDX 1
    DELETE STATEMENT 167
    DELETE ACC_PARTITION
    NESTED LOOPS 167
    VIEW VW_NSO_1 3
    SORT UNIQUE 3
    TABLE ACCESS FULL ACCOUNT_DELETE_HLP 1
    TABLE ACCESS BY LOCAL INDEX ROWID ACC_PARTITION 2
    INDEX RANGE SCAN HLP_LOC_IDX 1


    In the recent discussion, I was given to understand that with subquery's the index will not be used and It's better to use joins.

    can you clarify
    Badrinath

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Badrinath,

    1.) A remark:
    Why do you open new threads for the same question? Keeping it in an original therad is much less confusing for all of us.

    2.) An answer to your question:
    You have table A and table B. You want to delete all rows from table A where matching keys are found in table B. For any row in B (or at least for majority of them) there is a coresponding row in table A to be deleted. B is indexed on the comparising column. In this scenario, forget about using joins.

    Basicaly you have two options.
    a) The number of rows to be deleted from A is much less then the total number of rows in A (say you have 100.000.000 rows in A and you want to delete 50.000). In this case use:
    DELETE FROM a WHERE a.col1 IN (SELECT b.col1 FROM b);

    b) Almost all of the rows in A will be deleted (say you have 11.000.000 rows and 10.000.000 of them will be deleted). In this case use:
    DELETE FROM a WHERE EXISTS (SELECT 1 FROM b WHERE a.col1 = b.col1);

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jan 2001
    Posts
    642
    Is there any thumb rule, which says which method to use when. 'cos, I do these deletions in a batch program and some times the data files will be of varing sizes (40- 60%) of the existing table size.
    Having a thumb rule, I will check for the conditions and use the suitable statement.

    Badrinath

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    It is hard to give you a rule of thumb. If you are trying to tell your deletes will ususaly affect 40-60% of your table_A's rows, I will go with "WHERE EXISTS".

    In fact, I would choose "WHERE col IN" only if A is extremely large and B is small.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jan 2001
    Posts
    642
    Thanks for that,

    just depending on the percentage of recs to be deleted, I will call a different procedures (Case A or Case B)

    Badrinath

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