-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|