-
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
-
Just use the following statement:
[b]delete from acc_partition partition (part02)
where account_number in (select account_number
from account_delete_hlp) [/b]
Sergey.
-
hey pss,
In that case, It does not make use of the index:
I tried it , Here is the explain plan
DELETE ACC_PARTITION
NESTED LOOPS 167
VIEW VW_NSO_1 3
SORT UNIQUE 3
TABLE ACCESS FULL ACCOUNT_DELETE_HLP 1
TABLE ACCESS BY LOCAL INDEX ROWID ACC_PARTITION 2
INDEX RANGE SCAN HLP_LOC_IDX 1
DELETE STATEMENT 167
DELETE ACC_PARTITION
NESTED LOOPS 167
VIEW VW_NSO_1 3
SORT UNIQUE 3
TABLE ACCESS FULL ACCOUNT_DELETE_HLP 1
TABLE ACCESS BY LOCAL INDEX ROWID ACC_PARTITION 2
INDEX RANGE SCAN HLP_LOC_IDX 1
In the recent discussion, I was given to understand that with subquery's the index will not be used and It's better to use joins.
can you clarify
Badrinath
-
Badrinath,
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);
HTH,
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.
Badrinath
-
It is hard to give you a rule of thumb. If you are trying to tell your deletes will ususaly affect 40-60% of your table_A's rows, I will go with "WHERE EXISTS".
In fact, I would choose "WHERE col IN" only if A is extremely large and B is small.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thanks for that,
just depending on the percentage of recs to be deleted, I will call a different procedures (Case A or Case B)
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
|