Anti-join on single table

Printable View

09-27-2007, 10:22 PM
gxangel

Anti-join on single table

Hi,

I need to optimize a SQL query which does an anti-join on one table.

select A from table_t
where A not in
(select distinct A from table_t
where B in ('ABC','DEF') and C ='LS')

I read about using NOT EXISTS in place of NOT IN but i can't seem to implement it into my query as the examples shown uses anti-join on 2 tables.

Is there a way to optimize an anti-join on one table? Will appreciate it if you can show me a link or so.

Thanks a lot!
09-28-2007, 03:22 AM
abhaysk

why do u need to use the table twice?
09-28-2007, 05:28 AM
gxangel

I get you point!

You see, i was trying very hard to avoid using NOT IN/NOT EXISTS but it turns out that the situation is worse now that i scan the table twice.

This is the original query:

select A from table_t
where B not in ('ABC','DEF') and C ='LS'

I guess i just have to live with it.

Thanks abhaysk!
09-28-2007, 09:11 AM
abhaysk

if this the original query.. then the query in ur first post not only uses table twice but also logically not in sync with this query..

live with what you have..
09-30-2007, 09:33 PM
gxangel

Hi abhaysk,

Do you mean that

select A from table_t
where B not in ('ABC','DEF') and C ='LS'

is different from

select A from table_t
where A not in
(select distinct A from table_t
where B in ('ABC','DEF') and C ='LS')
?
10-01-2007, 02:14 AM
abhaysk

Yes..
10-01-2007, 02:36 AM
gxangel

I get what you mean ...

Created a table to see the results.

Supposed i have a table table_t:

ID OCDATE TC

1 9/26/2006 CB
1 9/26/2006 UT
2 9/26/2006 CS
2 9/26/2006 UT
3 9/26/2006 CS
4 9/26/2006 CB
5 9/26/2006 CS
4 9/26/2006 SS
5 9/26/2006 SS
6 9/26/2006 SS
7 9/26/2006 SS

Select id from table_t where
select distinct id from table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and tc not in ('CB', 'CS', 'UT')

ID

6
7
5
4

select id
from
table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')

ID

1
1
2
2
3
4
5
4
5
6
7

select id
from
table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and id not in (
select distinct id from table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and tc not in ('CB', 'CS', 'UT')
)

ID

1
1
2
2
3

Is there a way to avoid 2 table scans on table_t for this query?

select id
from
table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and id not in (
select distinct id from table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and tc not in ('CB', 'CS', 'UT')
)

Thanks abhaysk for pointing out my mistake!
10-01-2007, 06:23 AM
gxangel

WOAH!!!

This is very fast!
Thanks a million!!!
10-01-2007, 06:26 AM
abhaysk

Get me the exact requirement (my earlier post has one logical error & i have deleted that one).
10-01-2007, 10:21 PM
gxangel

Hi abhaysk,

just found out that your previous post doesn't give the correct results.

Anyway, here's my requirements.

Supposed i have a table table_t:

ID OCDATE TC

1 9/26/2006 CB
1 9/26/2006 UT
2 9/26/2006 CS
2 9/26/2006 UT
3 9/26/2006 CS
4 9/26/2006 CB
5 9/26/2006 CS
4 9/26/2006 SS
5 9/26/2006 SS
6 9/26/2006 SS
7 9/26/2006 SS

Output should be:

ID

1
1
2
2
3

My original query:

select id
from
table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and id not in (
select distinct id from table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and tc not in ('CB', 'CS', 'UT')
)

Thanks alot for your time and effort!
10-02-2007, 03:15 AM
gxangel

Typo error in my original query! So sorry about this.
It should be

select id
from
table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and id not in (
select distinct id from table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and tc in ('CBUT', 'CSUT', 'CBSGS')
)

My requirements remains the same:

If i have a table table_t which contains the data:

ID OCDATE TC

1 9/26/2006 CB
1 9/26/2006 UT
2 9/26/2006 CS
2 9/26/2006 UT
3 9/26/2006 CS
4 9/26/2006 CB
5 9/26/2006 CS
4 9/26/2006 SS
5 9/26/2006 SS
6 9/26/2006 SS
7 9/26/2006 SS

I should get the following output

Output should be:

ID

1
1
2
2
3
10-02-2007, 04:37 AM
gxangel

Ok... this is the CORRECT query... i must have been getting insane looking at this. Am so sorry about all this....

It should be

select id
from
table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and id not in (
select distinct id from table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and tc in ('CB', 'CS', 'UT')
)

My requirements remains the same:

If i have a table table_t which contains the data:

ID OCDATE TC

1 9/26/2006 CB
1 9/26/2006 UT
2 9/26/2006 CS
2 9/26/2006 UT
3 9/26/2006 CS
4 9/26/2006 CB
5 9/26/2006 CS
4 9/26/2006 SS
5 9/26/2006 SS
6 9/26/2006 SS
7 9/26/2006 SS

I should get the following output

Output should be:

ID

1
1
2
2
3
10-02-2007, 06:50 AM
hrishy

Hi

What does the explain plan look like for the query

Code:

select id from table_t where ocdate >= to_date('9/26/2006','mm/dd/yyyy') and ocdate <= to_date('9/27/2007','mm/dd/yyyy') and id not in ( select distinct id from table_t where ocdate >= to_date('9/26/2006','mm/dd/yyyy') and ocdate <= to_date('9/27/2007','mm/dd/yyyy') and tc NOT in ('CB', 'CS', 'UT') )

Please post the plans with the code tag.

Do you have a index on ocdate ?

regards
Hrishy
10-02-2007, 01:19 PM
abhaysk

Ok here is it.. i think it should fit your requirement

Code:

Select Id From ( Select Id, Max(Id_Eliminate) Over (Partition By Id) Id_Eliminate From ( Select Id, Case When tc not in ('CB', 'CS', 'UT') Then Id End Id_Eliminate From table_t Where ocdate >= to_date('9/26/2006','mm/dd/yyyy') and ocdate <= to_date('9/27/2007','mm/dd/yyyy') ) ) Where Id <> Id_Eliminate /

Rgds
Abhay.
10-02-2007, 05:00 PM
OracleDisected

Hi,

It's recommended to use OR/AND instead of IN , whenever its feasible.

When tc not in ('CB', 'CS', 'UT')

becomes

tc <> 'CB' AND tc <> 'CS' AND tc <> 'UT'

It's useful when IN set is small, because it tends to get unreadable above 10 items.

Happens that Oracle fetching is faster and CBO don't consider using LOOP for execution.
10-02-2007, 10:59 PM
gxangel

Explain plan for:

Code:

select id from table_t where ocdate >= to_date('9/26/2006','mm/dd/yyyy') and ocdate <= to_date('9/27/2007','mm/dd/yyyy') and id not in ( select distinct id from table_t where ocdate >= to_date('9/26/2006','mm/dd/yyyy') and ocdate <= to_date('9/27/2007','mm/dd/yyyy') and tc NOT in ('CB', 'CS', 'UT') )

Code:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=ALL_ROWS 191 K 165 M FILTER TABLE ACCESS FULL TABLE_T 191 K 5 M 1720 TABLE ACCESS FULL TABLE_T 2 70 1108

I've checked that there's indeed a index on ocdate but the Explain Plan doesn't use this .

Thank you very much.
10-03-2007, 12:27 AM
hrishy

Hi

You need to post the plan completely with full predicate information and all

How does the query posted by abhay run is it faster then the one you have ?

regards
Hrishy
10-03-2007, 02:41 AM
abhaysk

Hrishy,

There are two things to look

1) why is she using table twice when the requirement cant be achieved by using it only once.

2) after the change even if its performing bad then we need to see what we can do.

I think we should look at point one of how we can better things instead of just getting a fix for now (through indexes or so).. but note you would still be doing more GETS than with table used once.

Rgds
Abhay.
10-03-2007, 02:50 AM
hrishy

Hi

The way i would approach the problem is this

Bench mark the queries and see which runs faster and scales retain that one and deploy it to production.

I dont have any qualms about using the same table twice if i have to ,don,t see issue with it.

If adding indexes does solve a problem i do add indexes after testing dont see a issue with that either.

regards
Hrishy
10-03-2007, 03:19 AM
abhaysk

Well yes if you have to use it twice then use it, but when you can achive the same logic by using only once then why not use once?

just going on adding indexes for particular problem may solve that particular problem however this may pose higher problem at DB level or in long run.

efficiently writing the query (and ofcourse with proper stats and good design of the table/application) will seldom end in such problems.

rgds
abhay
10-03-2007, 03:40 AM
abhaysk

Quote:

Originally Posted by abhaysk

Ok here is it.. i think it should fit your requirement

Code:

Select Id From ( Select Id, Max(Id_Eliminate) Over (Partition By Id) Id_Eliminate From ( Select Id, Case When tc not in ('CB', 'CS', 'UT') Then Id End Id_Eliminate From table_t Where ocdate >= to_date('9/26/2006','mm/dd/yyyy') and ocdate <= to_date('9/27/2007','mm/dd/yyyy') ) ) Where Id <> Id_Eliminate /

I had forgot to put a NVL, so he is the corrected query..

Code:

Select Id From ( Select Id, Max(Id_Eliminate) Over (Partition By Id) Id_Eliminate From ( Select Id, Case When tc not in ('CB', 'CS', 'UT') Then Id End Id_Eliminate From table_t Where ocdate >= to_date('9/26/2006','mm/dd/yyyy') and ocdate <= to_date('9/27/2007','mm/dd/yyyy') ) ) Where Id <> nvl(Id_Eliminate, -99999999999999) / or you could use Select Id From ( Select Id, Max(Id_Eliminate) Over (Partition By Id) Id_Eliminate From ( Select Id, Case When tc not in ('CB', 'CS', 'UT') Then Id End Id_Eliminate From table_t Where ocdate >= to_date('9/26/2006','mm/dd/yyyy') and ocdate <= to_date('9/27/2007','mm/dd/yyyy') ) ) Where Id_Eliminate is null

Rgds
Abhay.
10-03-2007, 06:09 AM
gxangel

Hi,

This query took approximately 5 sec to execute.
My original query (which scans the table twice) hangs for more than 5 mins so it's a very BIG improvement.
10-03-2007, 06:21 AM
abhaysk

the old query was not using HASH ANTI JOIN, else it would have come in 15-20 secs.

Rgds
Abhay.
10-03-2007, 07:06 AM
hrishy

Hi

What happens if you do

Code:

select id from table_t where ocdate >= to_date('9/26/2006','mm/dd/yyyy') and ocdate <= to_date('9/27/2007','mm/dd/yyyy') and id not in ( select distinct id from table_t where ocdate >= to_date('9/26/2006','mm/dd/yyyy') and ocdate <= to_date('9/27/2007','mm/dd/yyyy') and tc NOT in ('CB', 'CS', 'UT') and id is not null ) and id is not null

regards
Hrishy
10-03-2007, 07:56 AM
abhaysk

It may start using hash anti join (ofcourse with one more id is not null in main query), & may give results in may be 15 secs.. but my question still stands, when you can do a job by scanning x blocks once why do you want to make it 2x?
10-03-2007, 10:26 PM
gxangel

Code:

select id from table_t where ocdate >= to_date('9/26/2006','mm/dd/yyyy') and ocdate <= to_date('9/27/2007','mm/dd/yyyy') and id not in ( select distinct id from table_t where ocdate >= to_date('9/26/2006','mm/dd/yyyy') and ocdate <= to_date('9/27/2007','mm/dd/yyyy') and tc NOT in ('CB', 'CS', 'UT') and id is not null ) and id is not null

This takes approximately 3 sec.. :)
Thanks alot!
10-04-2007, 12:15 AM
hrishy

Hi gxangel

Yesterday i could not test and bench mark these queries but on my home comp i tested and benched marked both abhays queries and mine.

My version of database was 10.1.2.3 and the query written by Abhay is the one you should be using as it is far more efficient way of doing what you are trying to do.

regards
Hrishy

P.S

Surprisngly at work i dont have acesses to oracle you gotaa trust me on this one :-( and hence couldnt test all my earlier posts
10-04-2007, 03:30 AM
abhaysk

wow.. even me.. lol
10-04-2007, 09:18 PM
gxangel

I will! Thanks alot hrishy and abhaysk!
Thanks for all your help!