Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Different behaviors in queries with and without max

Re: Different behaviors in queries with and without max

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 21 Oct 1999 12:06:47 +0100
Message-ID: <940504074.13889.0.nnrp-07.9e984b29@news.demon.co.uk>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US