Subject Re: Stange Query Plan
Author Leonardo Cosmai
--- In, "Ann W. Harrison"
<aharrison@i...> wrote:
> Hmm.. The first thing I'd try is moving the last condition -
> ANA00.ANA0TREC=1 - to a WHERE clause. That may give the optimizer the
> clue it needs. If that doesn't work, I'd hobble that condition by
> changing it to ( ANA00.ANA0TREC=1 or 'A' = 'B' ). The optimizer won't
> try to use the OR condition.
> Regards,
> Ann

Nothing changes if I move the ANA00.ANA0TREC=1 to a WHERE condition.
If I write (ANA00.ANA0TREC=1 or 'A' = 'B') something changes

Elapsed time= 1.13 sec
Cpu = 0.00 sec
Fetches = 89589

but The query with (ANA00.ANA0TREC=PRN10.PRN10FAKE) has these statistics:

Elapsed time= 0.01 sec
Cpu = 0.00 sec
Fetches = 187

The strange thing is that the query uses the PK of ANA00 (ANA0TREC,
ANA0CODE) correctly in both case.

So so strange....