Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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