Different behaviors in queries with and without max

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 /

       NUM MAXNUM
---------- ----------

         1        2.9
         2        2.9
         3        2.9
         4        2.9
         5        2.9
         6        2.9
         7        2.9
         8        2.9
         9        2.9
        10        2.9

10 rows selected.

Here's some extra-informations:

v805> select * from t;

       COL

         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

v805> select * from dual;

D
-

X

1 row selected.

And the explain plans:

First statement:

Operation                              Options         Object           Pos


-------------------------------------- --------------- --------------- ----


SELECT STATEMENT
  COUNT                                STOPKEY                            1
    NESTED LOOPS                                                          1
      TABLE ACCESS                     FULL            DUAL               1
      TABLE ACCESS                     FULL            T                  2

Second statement:
Operation                              Options         Object           Pos


-------------------------------------- --------------- --------------- ----


SELECT STATEMENT
  COUNT                                                                   1
    NESTED LOOPS                                                          1
      VIEW                                                                1
        SORT                           AGGREGATE                          1
          TABLE ACCESS                 FULL            DUAL               1
      TABLE ACCESS                     FULL            T                  2

Note the missing the STOPKEY option in the second COUNT operation.

Someone can explain me what happens ?

--

Thanks
Michel Received on Thu Oct 21 1999 - 04:34:43 CDT