Re: Different behaviors in queries with and without max

It looks like you're running the rule based optimiser. I vaguely recall seeing a bug about situations like this (aggregate views and counts going wrong) about 3 years ago.

When I tried your example using CBO, I got a completely different access path with the correct answer.

I then put in the hint - /*+ use_nl (t) */ and got exactly your access path, also with the correct answer.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Michel Cadot wrote in message <7ummr7$470$1_at_oceanite.cybercable.fr>...
>I have a strange difference in the behavior of my two queries:
>
>v805> select rownum num, maxnum
> 2 from t,
> 3 ( select 2.9 maxnum from dual )
> 4 where rownum < maxnum
> 5 /
>
> NUM MAXNUM
>---------- ----------
> 1 2.9
> 2 2.9
>
>2 rows selected.
>
>v805> select rownum num, maxnum
> 2 from t,
> 3 ( select MAX(2.9) maxnum from dual )
> 4 where rownum < maxnum
> 5 /
>
Received on Thu Oct 21 1999 - 06:06:47 CDT