Explain the Delete process : Mr Halo

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

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.

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!

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

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.

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;

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.

Thank U Tamilselvan