Re: Any Ideas

Larry,
Bug at play is very likely (change of execution plan) but even though I presume, since you return something that you provide (24548) that this query is assumed to be an existence check? 
Then, why don't you write it
SELECT shift.shiftid FROM shift 
WHERE shift.shiftid = 24548
    AND exists (select null
FROM job, lkshiftmachinestage, operation WHERE job.actualstartdt <= shift.todt AND job.actualenddt >= shift.fromdt AND lkshiftmachinestage.machinestageid = operation.machinestageid AND job.jobid = operation.jobid AND lkshiftmachinestage.shiftid = shift.shiftid)
I suspect that the DISTINCT erroneously brings the optimizer to rewrite the query in a way which involves a NOT IN with a subquery that returns a NULL value (perhaps that the distribution of data makes sense of inverting the inequality conditions), which can never be true since you never know what a NULL is. 

HTH

Stéphane Faroult

Wolfson Larry - lwolfs wrote:

We have the query below running in a 9.2.0.6 DB
We put the query into a 9208 instance and the Distinct works, looks as
if there's a bug at play
SELECT shift.shiftid FROM job, shift, lkshiftmachinestage, operation WHERE job.actualstartdt <= shift.todt AND job.actualenddt >= shift.fromdt AND lkshiftmachinestage.machinestageid = operation.machinestageid AND job.jobid = operation.jobid AND lkshiftmachinestage.shiftid = shift.shiftid AND shift.shiftid = 24548 

And it returns 255 identical numbers. When I put in a distinct
SELECT DISTINCT shift.shiftid FROM job, shift, lkshiftmachinestage, operation WHERE job.actualstartdt <= shift.todt AND job.actualenddt >= shift.fromdt AND lkshiftmachinestage.machinestageid = operation.machinestageid AND job.jobid = operation.jobid AND lkshiftmachinestage.shiftid = shift.shiftid AND shift.shiftid = 24548 I get "no rows found". Even tried making it a subquery 
select distinct *

 from

 (

 SELECT shift.shiftid

            FROM job, shift, lkshiftmachinestage, operation

           WHERE job.actualstartdt <= shift.todt

             AND job.actualenddt >= shift.fromdt

             AND lkshiftmachinestage.machinestageid =
operation.machinestageid

             AND job.jobid = operation.jobid

             AND lkshiftmachinestage.shiftid = shift.shiftid

             AND shift.shiftid = 24548

 )
And still get nothing :-S


  TIA
        Larry



--
http://www.freelists.org/webpage/oracle-l

Other related posts: