Anti-join on single table

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.

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.

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

Rgds
Abhay.

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

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?

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!

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

wow.. even me.. lol

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