Explain the Delete process : Mr Halo
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Explain the Delete process : Mr Halo

  1. #1
    Join Date
    Jan 2001
    Posts
    642

    I am a bit confused the way the delete statements work.
    For egs :

    1) delete from account ;
    2) delete from account a
    where account_number in
    (select distinct account_number from another_table);

    3) delete from account a
    where a.account_number = (select .account_number
    from another_table b
    where a.account_number = b.account_number)

    Please clarify
    Badrinath

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    In such a case, assuming (which is usually the case) that all account numbers are distinct, all stmts will do the same thing, which is empty out the whole damn table.

  3. #3
    Join Date
    Nov 2000
    Posts
    344
    1) Delete from account;
    This will delete all records from the account table. If you are going to use this and you know you arent' going to roll it back, you should use 'truncate table account' It will be MUCH faster if the account table is big because it does not generate redo. Also, it will reset the highwatermark on the table which is an important thing

    2) delete from account a where account_number in (select distinct account_number from another_table)
    this will delete all records in the account table that have a matching account number in another_table.

    3) delete from account a
    where a.account_number = (select b.account_number
    from another_table b
    where a.account_number = b.account_number)

    This query will do the same thing as #2, but it will give errors if there is more than one row to delete!


  4. #4
    Join Date
    Jan 2001
    Posts
    642
    Hi,
    My concern is how does #2 and #3 work, does it read each record in "another_table" and delete from account or how does it work


  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    The 2nd DELETE statement is called uncorrelated subquery.
    The inner query is executed only once, and for each row in the outer query the account is checked, if it matches, then oracle deletes the row, otherwise it reads the next row in the outer query.

    The 3rd DELETE statement is called correlated subquery. The inner query is executed as many times as the number of rows obtained in the outer query. This will take long time to process.

    I hope you now understand the sub query concept in oracle.

  6. #6
    Join Date
    Jan 2001
    Posts
    642
    Thanks for clarifing,
    When do you use the co-related sub querys, is it for bulk transactions or for small ones.. I fell it's for small transactions. Am I correct;

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    When I use correlated ( or uncorrelated) sub query depends my business requirements.

    One more correction: there is NO co-related sub query, it is called correlated sub query.

  8. #8
    Join Date
    Jan 2001
    Posts
    642
    Thank U Tamilselvan

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