You Asked
sokrates@11.2 > create table temp as select chr(level+64) letter from dual connect by level<=26;
Table created.
sokrates@11.2 > select /* query a */ letter from temp
2 where rownum<=5;
LETT
----
A
B
C
D
E
sokrates@11.2 > select /* query b */ owner, object_name from all_objects
2 where rownum<=5;
OWNER OBJECT_NAME
------------------------------ ------------------------------
SYS ORA$BASE
SYS DUAL
PUBLIC DUAL
SYS MAP_OBJECT
PUBLIC MAP_OBJECT
so far, so expected.
I can rewrite query b:
sokrates@11.2 > with n as (select level n from dual connect by level<=5)
2 select /* query b' */ owner, object_name from all_objects, n
3 where rownum=n;
OWNER OBJECT_NAME
------------------------------ ------------------------------
SYS ORA$BASE
SYS DUAL
PUBLIC DUAL
SYS MAP_OBJECT
PUBLIC MAP_OBJECT
and retrieve the same result.
However, rewriting query a in the same way results in an unexpected result:
sokrates@11.2 > with n as (select level n from dual connect by level<=5)
2 select /* query a' */ letter from temp, n
3 where rownum=n;
LETT
----
A
A
A
A
A
a. why ?
b. is the result of using rownum in a join condition completely undefined ?
and Tom said...
the behavior is the same as far as I'm concerned.
You have a set of five rows with the numbers 1, 2, 3, 4, 5 in it.
You have another set of some size.
You are doing a cartesian join to it.
Now, we could build a result set by joining the first row if set1 to every row in set2, then the second row in set1 to ever row in set2.
OR we could do it the other around.
We might generate:
a, 1
a, 2
a, 3
a, 4
a, 5
b, 1
b, 2
b, 3 ....
or we might generate
a, 1
b, 1
c, 1
...
z, 1
a, 2
b, 2
....
z, 2
...
If you look at it, you might not even ever get 5 rows. If we built the second result set - you would get 1 row. You could easily get zero if we wanted. There is NO REASON the UNORDERED result set you are working with couldn't become:
a, 5
b, 5
c, 5
...
So yes, beware of rownum in this sort of use case. You don't have any "ordering" information there. The results of this query are not deterministic. There is no reason why we have to return the same answer execution after execution. In fact, with different pga workarea sizes and larger sets, I am quite certain the answers could be different from execution to execution in the same server instance.
Is this answer out of date? If it is, please let us know via a Comment