I have a delete statement to delete from table A for all the records in table B. Query and Execution plan is given below
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)
DELETE STATEMENT 582
TABLE ACCESS FULL ACC_PARTITION 582
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?
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);
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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.