What's the Better way?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: What's the Better way?

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

    I have few updations to do daily morning. I get a list of account numbers which needs to be deleted from the database. Currently deletion takes a very long time sometimes as long as 4-5 hours. The number of accounts to be deleted from the production database is around 1/2 Million
    The steps I follow is
    1) I load the data from the text file into a temp table (using sqlloader).

    2) set the rollback segment to a bigger segment.

    3) delete from main_account where
    del_row in (select del_row from temp_table)


    I would like to know , in the step 3, instead of using sub query, Could I use the Joins
    delete from main_account a , temp_table b
    where a.acc_num = b.acc_num.

    . Will there be any improvement.
    (Theoritically, which is better, subquery or joins?& why)

    Badrinath

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Basically you have a wrong application design.
    Instead of deleting rows ( 1/2 M !!!) daily, have a DELETED_FLAG column in the table. Update this column with value "Y" when ever you want to delete a A/C number. Include this column in all other SQL statements where clause.

    When there is a low usage level ( Saturday night ), physically delete the rows from the table.

    For your question I would prefer JOIN over SUBQUERY.

  3. #3
    Join Date
    Jan 2001
    Posts
    642
    The application was designed a long time back and now we are required to improve the updation time to the possible extents. As suggested by u , the changes to the database to incorparate a del_flag is very appropriate, while would take some time as all the applications using this data base need to be checked an changed for the del flag.

    Can you please tell me , what happens with Joins vis-a-vis the sub query.

    Badrinath

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    JOIN always produce efficient execution path.
    How ever to improve your deletion time, create an index on A/C number on the temp table. I hope you have an index on the A/c number in the original table. You may evaluate various option. Probably, a HASH join will be better option and full table scan on the original table may yield better response time.

    Also remember that after deletion, there might be a lot of holes in the indexes. Hence, you must recreate the indexes on the original table.

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