-
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
-
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.
-
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
-
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.
