More efficient query than what I have 1

[ctyankeeinok]

I want to know how to most efficiently do the following query:

I need to remove entries in a table if there are no matching entries in two other tables, as well as a recursive look at another column in the same table. What I currently have is this (simplified):

delete from table_a
where table_a.key in
(
select ta.key
from table_a ta,
ref_table rt
where ta.key2 = rt.key2
and rt.end_date < sysdate
and not exists (select tb.key
from table_b tb
where tb.key = ta.key)
and not exists (select tc.key
from table_c tc
where tc.key = ta.key)
and not exists (select ta1.key
from table_a ta1
where ta1.col_fk = ta.key)
)

As you can see I am referencing table_a three times (which I am sure does not help things). This query takes over 2 minutes on about 20K rows in table_a and I have tried various incarnations to no avail. What is the most efficient way to accomplish this objective? I have tried not exists, not in, and 0 = select count.... with no improved performance. Is there a better way to do this?

 

[Thargy]

ct,

it's impossible to improve a query's performance without some basic information. For example, how many rows are involved? You mention 20,000 in your post. Is that a sample or all of the data? What percentage of the data are you trying to delete?

If you're deleting a lot, then I would use a CTAS (Create table as Select) approach.

Please post the explain plan for the query, so that we can see if it's hitting (or missing indexes). What indexes are available?

Have you run DBMS_STATS lately to refresh the optimiser's statistics?

Your post mentions a recursive read of a table. Why is this necessary, how does it contribute?

Can you please post the create table statements necessary to make the tables involved, and some representative data (anonymised if need be) and a clear statement of the expected deletion for the given data.

That log ought to keep you busy for five minutes or so, but I honestly don't see how we can help much without it.

Regards

Tharg

Grinding away at things Oracular

 

[taupirho]

Try replacing your not exists bit with something like
this

and ta.key not in
(
select t1.key
from table_a t1,table_b t2,table_c t3
where not (t1.key != t2.key and t1.key != t3.key
and t1.key != t1.col_fkey))



In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

EXISTS/NOT EXISTS is always more efficient than its "NOT IN" equivalent.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[taupirho]

I think I would have said NOT EXISTS is always at least as efficient as NOT IN. In this case any efficiency gain (if there is one) would not be because of that but via the sub join (i.e one pass of table_a as opposed to three)

The following link has a good discussion - with proofs - on EXISTS v IN and NOT EXISTS v NOT IN

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:953229842074

In order to understand recursion, you must first understand recursion.

 

[Welshbird]

Various people cleverer than I have suggested using MINUS rather than not in or not exist as being more efficient. I have found it to be true!

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SantaMufasa]

And, yes, Fee, I would agree that MINUS (along with the other set operators) is certainly most quickester than NOT IN or NOT EXISTS.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Welshbird]

I have been learning something along the way then!

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[ctyankeeinok]

I tried willif's suggestion on the MINUS and the difference is tremendous (42 sec. to 5 sec.in my test system). I also learned a new trick along the way - my delete was incorrect, as I should have matched on two columns. After some discussions with co-workers, I learned how to handle the two column match on a delete as follows (along with the MINUS):

delete from table_a
where (key1, key2) in
(
select ta.key1, ta.key2
from table_a ta,
ref_table rt
where ta.key2 = rt.key2
and rt.end_date < sysdate
MINUS
select tb.key1, tb.key2
from table_a ta,
table_b tb
where tb.key1 = ta.key1
and tb.key2 = ta.key2
MINUS
select tc.key1, tc.key2
from table_a ta,
table_c tc
where tc.key1 = ta.key1
and tb.key2 = ta.key2
MINUS
select ta1.key1, ta1.key2
from table_a ta,
table_a ta1
where ta1.col_fk = ta.key1
and ta1.col_fk != ta1.key1)
)

Thanks for all the suggestions along the way. You have all been extremely helpful. Kudos all around.

Jim