Anti-join on single table

[abhaysk]

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

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..

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.

[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.

[abhaysk]

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

Rgds
Abhay.

[hrishy]

Hi

What happens if you do

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

[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?

[gxangel]

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!

[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

[abhaysk]

wow.. even me.. lol

[gxangel]

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