SQL & PL/SQL

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