Hi Advisors,

I have a delete statement to delete from table A for all the records in table B. Query and Execution plan is given below


SQL>
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)

Explain plan:
DELETE STATEMENT 582
DELETE ACC_PARTITION
FILTER
TABLE ACCESS FULL ACC_PARTITION 582
FILTER
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?

Badrinath