Advisors - Tune SQL

[badrinathn]

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

[pss]

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.

[badrinathn]

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

[jmodic]

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,

[badrinathn]

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

[jmodic]

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.

[badrinathn]

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