Anti-join on single table

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

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

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

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.

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.

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.

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

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.

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

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