Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

"rownum=1" returns no rows, "rownum<=1" returns one

719000Aug 24 2009 — edited Sep 25 2009
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 23 2009
Added on Aug 24 2009
9 comments
2,325 views