Re: Different behaviors in queries with and without max

Yes you're right i use RBO and with your hint i get the correct result.

The explain plan shows a little difference with the previous one: there is a FILTER operation more between the NESTED LOOPS and the COUNT.

explain plan for
select /*+ use_nl (t) */ rownum num, maxnum from t,

     ( select max(15.9) maxnum from dual ) where rownum < maxnum
/

Operation                              Options         Objet            Pos
-------------------------------------- --------------- --------------- ----
SELECT STATEMENT                                                          2
  COUNT                                                                   1
    FILTER                                                                1
      NESTED LOOPS                                                        1
        VIEW                                                              1
          SORT                         AGGREGATE                          1
            TABLE ACCESS               FULL            DUAL               1
        TABLE ACCESS                   FULL            T                  2

Thank you.

--
Have a nice day
Michel

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> a écrit dans le message : 940504074.13889.0.nnrp-07.9e984b29_at_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:46:03 CDT