5 Replies Latest reply: Aug 20, 2009 9:43 AM by Charles Hooper RSS

    rownum=1 v's rownum <2

    337008
      HI

      select * from table where rownum=1 waits for full table scan to complete before returning one row and select * from table where rownum < 2 returns the row straight away. I see this on our 10.2 servers. I do not see this behaviour on our 11.1 servers.

      Have tried both optimizer_mode=FIRST_ROWS and optimizer_mode=ALL_ROWS. Stas exist for the tables.

      Has anyone else experienced this on 10.2.

      Thanks
        • 1. Re: rownum=1 v's rownum <2
          337008
          I have found

          Bug 4513695 Poor performance for SELECT with ROWNUM=1 with literal replacement

          this is fixed in 10.2.0.4.
          • 2. Re: rownum=1 v's rownum <2
            Hoek
            Hi,
            Has anyone else experienced this on 10.2.
            Nope, so it would come in handy if you'd provide the execution plans as well.

            I got the same plan twice using this simple test:
            SQL> create table t as select level col from dual connect by level <= 1000000;
            
            Table created.
            
            SQL> create index t_i on t(col);
            
            Index created.
            
            SQL> exec dbms_stats.gather_table_stats(USER, 'T');
            
            PL/SQL procedure successfully completed.
            
            SQL> set autotrace traceonly explain
            SQL> select * from t where rownum=1;
            
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 2287254946
            
            ---------------------------------------------------------------------------
            | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
            ---------------------------------------------------------------------------
            |   0 | SELECT STATEMENT   |      |     1 |     4 |     2   (0)| 00:00:01 |
            |*  1 |  COUNT STOPKEY     |      |       |       |            |          |
            |   2 |   TABLE ACCESS FULL| T    |     1 |     4 |     2   (0)| 00:00:01 |
            ---------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               1 - filter(ROWNUM=1)
            
            SQL> select * from t where rownum<2;
            
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 2287254946
            
            ---------------------------------------------------------------------------
            | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
            ---------------------------------------------------------------------------
            |   0 | SELECT STATEMENT   |      |     1 |     4 |     2   (0)| 00:00:01 |
            |*  1 |  COUNT STOPKEY     |      |       |       |            |          |
            |   2 |   TABLE ACCESS FULL| T    |     1 |     4 |     2   (0)| 00:00:01 |
            ---------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               1 - filter(ROWNUM<2)
            
            SQL> set autotrace off
            SQL> select banner from v$version
              2  /
            
            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
            • 3. Re: rownum=1 v's rownum <2
              Hoek
              Aha, that explains it! ;)
              • 4. Re: rownum=1 v's rownum <2
                337008
                Thanks for your quick reply hoek.
                • 5. Re: rownum=1 v's rownum <2
                  Charles Hooper
                  User 334005 wrote:
                  HI

                  select * from table where rownum=1 waits for full table scan to complete before returning one row and select * from table where rownum < 2 returns the row straight away. I see this on our 10.2 servers. I do not see this behaviour on our 11.1 servers.

                  Have tried both optimizer_mode=FIRST_ROWS and optimizer_mode=ALL_ROWS. Stas exist for the tables.

                  Has anyone else experienced this on 10.2.

                  Thanks
                  Use of ROWNUM = 1 or ROWNUM < 2 should yield the same execution plan - both should force the optimizer to automatically switch to a FIRST_ROWS(n) optimizer mode, over-riding the default optimizer mode set for the session. See:
                  http://jonathanlewis.wordpress.com/2008/11/11/first_rows_n/
                  Optimizer choosing different plans when ROWNUM filter. [UPDATED: 11.2.0.1]

                  I suspect that if you force a hard parse of both SQL statements by adding a comment, you should see the same execution plan for both queries.

                  Charles Hooper
                  IT Manager/Oracle DBA
                  K&M Machine-Fabricating, Inc.