On one client's Oracle server, I am not getting any results back when I specify "rownum=1" for simple queries. The query plan appears the same as for "rownum<=1", but the results are different. I did not see this as a platform-specific bug in 10.2.0.4, but maybe I missed it. Or is there a parameter set somewhere that's causing this behavior?
SQL> select * from v$version where rownum=1;
no rows selected
SQL> select * from v$version where rownum<=1;
BANNER
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SQL> select * from v$version;
BANNER
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
SQL> set autotrace on explain
SQL> select sysdate from dual where rownum = 1;
no rows selected
Execution Plan
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~--
Plan hash value: 1917149222
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-
Predicate Information (identified by operation id):
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---
1 - filter(1=ROWNUM)
SQL> select sysdate from dual where rownum <= 1;
SYSDATE
~~~~~~~~-
24-AUG-09
Execution Plan
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~--
Plan hash value: 1917149222
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-
Predicate Information (identified by operation id):
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---
1 - filter(1>=ROWNUM)
SQL>
Thanks!
Mike
Edited by: user1769327 on Aug 24, 2009 3:16 PM