13 Replies Latest reply: Mar 28, 2013 8:38 AM by Mohamed Houri RSS

    Is it possible to use ROWNUM in a subquery?

    Laurent Schneider
      I am wondering if it is legal to use rownum (for instance as a row generator) in a subquery. I did not find it is illegal. But it does not seem to work safe

      Working case
      SQL> with v as (select rownum r from (select 1 from dual union all select 2 from dual)) select t1.r t1, t2.r t2 from v t1, v t2
      
              T1         T2
      ---------- ----------
               1          1
               1          2
               2          1
               2          2
      Not working case
      SQL> create table t(x) as select 1 from dual union all select 2 from dual
      Table created.
      SQL> with v as (select rownum r from t) select t1.r t1, t2.r t2 from v t1, v t2
      
              T1         T2
      ---------- ----------
               1          1
               1          2
               3          1
               3          2
      Edited by: Laurent Schneider on Mar 26, 2013 1:12 PM
      version 11.2.0.3 on aix
        • 1. Re: Is it possible to use ROWNUM in a subquery?
          SomeoneElse
          This is what I get on 11.2.0.1:
          SQL> create table t(x) as select 1 from dual union all select 2 from dual;
          
          Table created.
          
          SQL> with v as (select rownum r from t) select t1.r t1, t2.r t2 from v t1, v t2;
          
                            T1                   T2
          -------------------- --------------------
                             1                    1
                             2                    2
                             3                    1
                             3                    2
          
          SQL> select * from v$version;
          
          BANNER
          ----------------------------------------------------------------------------
          Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
          PL/SQL Release 11.2.0.1.0 - Production
          CORE    11.2.0.1.0      Production
          TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
          NLSRTL Version 11.2.0.1.0 - Production
          • 2. Re: Is it possible to use ROWNUM in a subquery?
            BluShadow
            11.2.0.3 on windows...
            SQL> create table t(x) as select 1 from dual union all select 2 from dual;
            
            Table created.
            
            SQL> with v as (select rownum r from t) select t1.r t1, t2.r t2 from v t1, v t2
              2  /
            
                    T1         T2
            ---------- ----------
                     1          1
                     2          2
                     3          1
                     3          2
            
            SQL> select * from v$version;
            
            BANNER
            --------------------------------------------------------------------------------
            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
            PL/SQL Release 11.2.0.3.0 - Production
            CORE    11.2.0.3.0      Production
            TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
            NLSRTL Version 11.2.0.3.0 - Production
            • 3. Re: Is it possible to use ROWNUM in a subquery?
              BluShadow
              I would say there's certainly a glitch in the way Oracle processes it...
              SQL>  with v as (select rownum r from t) select t1.r t1, t2.r t2 from v t1, v t2
                2  /
              
                      T1         T2
              ---------- ----------
                       1          1
                       2          2
                       3          1
                       3          2
              
              SQL>  with v as (select /*+ MATERIALIZE */ rownum r from t) select t1.r t1, t2.r t2 from v t1, v t2
                2  /
              
                      T1         T2
              ---------- ----------
                       1          1
                       1          2
                       2          1
                       2          2
              
              SQL> ed
              Wrote file afiedt.buf
              
                1  select t1.r t1, t2.r t2
                2* from (select rownum r from t) t1, (select rownum r from t) t2
              SQL> /
              
                      T1         T2
              ---------- ----------
                       1          1
                       1          2
                       2          1
                       2          2
              Something to do with the subquery factoring.

              The explain plans look identical, so perhaps a trace would show something up.
              • 4. Re: Is it possible to use ROWNUM in a subquery?
                Frank Kulash
                Hi,

                For the "not working" case, I get
                `       T1         T2
                ---------- ----------
                         1          1
                         1          2
                         2          1
                         2          2
                In versions
                11.1.0.6.0
                10.2.0.3.0
                10.1.0.2.0 and
                9.2.0.6.0

                There seem to be a lot of bugs regarding sub-queries in Oracle 11.2
                • 5. Re: Is it possible to use ROWNUM in a subquery?
                  Solomon Yakobson
                  Laurent Schneider wrote:
                  Not working case
                  It all depends if optimizer materializes the view or not:
                  SQL> set linesize 132
                  SQL> explain plan for
                    2  with v as (select rownum r from (select 1 from dual union all select 2 from dual)) select t1.r t1, t2.r t2 from v t1, v t2
                    3  /
                  
                  Explained.
                  
                  SQL> @?\rdbms\admin\utlxpls
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------
                  Plan hash value: 2685223184
                  
                  --------------------------------------------------------------------------------------------------------
                  | Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
                  --------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT           |                           |     4 |   104 |    10   (0)| 00:00:01 |
                  |   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
                  |   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6631_C996E4 |       |       |            |          |
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------
                  |   3 |    COUNT                   |                           |       |       |            |          |
                  |   4 |     VIEW                   |                           |     2 |       |     4   (0)| 00:00:01 |
                  |   5 |      UNION-ALL             |                           |       |       |            |          |
                  |   6 |       FAST DUAL            |                           |     1 |       |     2   (0)| 00:00:01 |
                  |   7 |       FAST DUAL            |                           |     1 |       |     2   (0)| 00:00:01 |
                  |   8 |   MERGE JOIN CARTESIAN     |                           |     4 |   104 |     6   (0)| 00:00:01 |
                  |   9 |    VIEW                    |                           |     2 |    26 |     2   (0)| 00:00:01 |
                  |  10 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6631_C996E4 |     2 |    26 |     2   (0)| 00:00:01 |
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------
                  |  11 |    BUFFER SORT             |                           |     2 |    26 |     6   (0)| 00:00:01 |
                  |  12 |     VIEW                   |                           |     2 |    26 |     2   (0)| 00:00:01 |
                  |  13 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6631_C996E4 |     2 |    26 |     2   (0)| 00:00:01 |
                  --------------------------------------------------------------------------------------------------------
                  
                  20 rows selected.
                  
                  SQL> drop table t purge;
                  
                  Table dropped.
                  
                  SQL> create table t(x) as select 1 from dual union all select 2 from dual
                    2  /
                  
                  Table created.
                  
                  SQL> explain plan for
                    2  with v as (select rownum r from t) select t1.r t1, t2.r t2 from v t1, v t2
                    3  /
                  
                  Explained.
                  
                  SQL> @?\rdbms\admin\utlxpls
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------
                  Plan hash value: 3470994595
                  
                  ------------------------------------------------------------------------------
                  | Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                  ------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT      |      |     4 |   104 |     9   (0)| 00:00:01 |
                  |   1 |  MERGE JOIN CARTESIAN |      |     4 |   104 |     9   (0)| 00:00:01 |
                  |   2 |   VIEW                |      |     2 |    26 |     3   (0)| 00:00:01 |
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------
                  |   3 |    COUNT              |      |       |       |            |          |
                  |   4 |     TABLE ACCESS FULL | T    |     2 |       |     3   (0)| 00:00:01 |
                  |   5 |   BUFFER SORT         |      |     2 |    26 |     9   (0)| 00:00:01 |
                  |   6 |    VIEW               |      |     2 |    26 |     3   (0)| 00:00:01 |
                  |   7 |     COUNT             |      |       |       |            |          |
                  |   8 |      TABLE ACCESS FULL| T    |     2 |       |     3   (0)| 00:00:01 |
                  ------------------------------------------------------------------------------
                  
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------------------
                  Note
                  -----
                     - dynamic sampling used for this statement (level=2)
                  
                  19 rows selected.
                  
                  SQL> 
                  SY.
                  • 6. Re: Is it possible to use ROWNUM in a subquery?
                    Laurent Schneider
                    Frank Kulash wrote:
                    There seem to be a lot of bugs regarding sub-queries in Oracle 11.2
                    In your opinion a bug then? Or an invalid usage of rownum?

                    Probably both

                    Edited by: Laurent Schneider on Mar 27, 2013 4:29 PM

                    Thanks all :)
                    • 7. Re: Is it possible to use ROWNUM in a subquery?
                      Frank Kulash
                      Hi,
                      Laurent Schneider wrote:
                      Frank Kulash wrote:
                      There seem to be a lot of bugs regarding sub-queries in Oracle 11.2
                      In your opinion a bug then? Or an invalid usage of rownum?
                      It's so easy to blame everything on bugs that I hesitate to do so. But given that the exact same code, using only documented features in the documented fashion, produces different results in different versions, then, in this case, it looks like it really is a bug.
                      Probably both
                      ROWNUM tends to be a quick and dirty way of doing things. In production code, I use ROW_NUMBER instead. One reason is that, if the requirements change, changing ROW_NUMBER is easy. For example, you may not need partitioning now, but someone may ask you for it next month.
                      • 8. Re: Is it possible to use ROWNUM in a subquery?
                        BluShadow
                        Laurent Schneider wrote:
                        Frank Kulash wrote:
                        There seem to be a lot of bugs regarding sub-queries in Oracle 11.2
                        In your opinion a bug then? Or an invalid usage of rownum?

                        Probably both

                        Edited by: Laurent Schneider on Mar 27, 2013 4:29 PM

                        Thanks all :)
                        In my opinion, a bug, caused by some internal optimization not recognizing rownum at the level of the subquery. It shouldn't be generating a value of 3 in any case as there is only 2 rows in the table, though I think it's confusing itself because of the join causing more rows in the final result.... so (just talking off the top of my head)... it would seem that it's losing the scope (seems the best word to describe it) of the psuedocolumn rownum and confusing rownum from the subquery with rownum of the overall query. Hence why the materialize hint is forcing it to generate materialize the values first, so they are no longer pseudo values, in the subquery before joining to give the correct result. Perhaps we should term it a "scope leak" :)
                        • 9. Re: Is it possible to use ROWNUM in a subquery?
                          BluShadow
                          Try anouther pseudocolumn...
                          SQL> ed
                          Wrote file afiedt.buf
                          
                            1  with v as (select ora_rowscn r from t)
                            2  select t1.r t1
                            3        ,t2.r t2
                            4  from v t1
                            5*     ,v t2
                          SQL> /
                          
                                         T1                T2
                          ----------------- -----------------
                              5973202433895
                              5973202433895
                              5973202433895
                              5973202433895
                          :-?
                          • 10. Re: Is it possible to use ROWNUM in a subquery?
                            999557
                            Yes we can use row number in sub queries...
                            • 11. Re: Is it possible to use ROWNUM in a subquery?
                              BluShadow
                              996554 wrote:
                              Yes we can use row number in sub queries...
                              Congratulations on making the most pointless first post I have ever seen.
                              Next time you might actually want to try and read what the issue is.
                              • 12. Re: Is it possible to use ROWNUM in a subquery?
                                Laurent Schneider
                                okay, also buggy with ora_rowscn then...

                                it is -by far- not the first bug I see with subqueries that can be fixed with the undocumented MATERIALIZED hint. I am curious how much of those will be fixed in next release.

                                clearly, row number seems to work more reliably.

                                for the recall,
                                SELECT * FROM (SELECT * FROM EMP ORDER BY ENAME) WHERE ROWNUM<5
                                , the so called "Top-N queries", was introduced in 8i. Not sure how the developers came up with such an ugly syntax.

                                How-often do you see buggy code like this ?
                                SELECT * FROM EMP WHERE ROWNUM<5 ORDER BY ENAME
                                Me quite often, unfortunately. Still this special syntax (order by inside a subquery) and special rules (no possible query unnesting) performs better than row_number
                                • 13. Re: Is it possible to use ROWNUM in a subquery?
                                  Mohamed Houri
                                  I have tested your example in two database release 10.0.2.4 and 11.2.0.1.0 where I did observed two different reactions
                                  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 Solaris: Version 10.2.0.4.0 - Production
                                  NLSRTL Version 10.2.0.4.0 – Production
                                  
                                  SQL> with v as (select rownum r from t) select t1.r t1, t2.r t2 from v t1, v t2;
                                  
                                         T1         T2
                                  --------- ----------
                                          1          1
                                          1          2
                                          2          1
                                          2          2
                                  
                                  -----------------------------------------------------------------------------
                                   Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                                  -----------------------------------------------------------------------------
                                     0 | SELECT STATEMENT      |      |       |       |     9 (100)|          |
                                     1 |  MERGE JOIN CARTESIAN |      |     4 |   104 |     9   (0)| 00:00:01 |
                                     2 |   VIEW                |      |     2 |    26 |     3   (0)| 00:00:01 |
                                     3 |    COUNT              |      |       |       |            |          |
                                     4 |     TABLE ACCESS FULL | T    |     2 |       |     3   (0)| 00:00:01 |
                                     5 |   BUFFER SORT         |      |     2 |    26 |     9   (0)| 00:00:01 |
                                     6 |    VIEW               |      |     2 |    26 |     3   (0)| 00:00:01 |
                                     7 |     COUNT             |      |       |       |            |          |
                                     8 |      TABLE ACCESS FULL| T    |     2 |       |     3   (0)| 00:00:01 |
                                  -----------------------------------------------------------------------------
                                  
                                  Note
                                  ----
                                    - dynamic sampling used for this statement
                                  
                                  SQL> with v as (select /*+ materialize */ rownum r from t) select t1.r t1, t2.r t2 from v t1, v t2;
                                  
                                          T1         T2
                                  ---------- ----------
                                           1          1
                                           1          2
                                           2          1
                                           2          2
                                  
                                  SQL> select * from table(dbms_xplan.display_cursor);
                                  
                                  ----------------------------------------------------------------------------------------------------------
                                  | Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
                                  ----------------------------------------------------------------------------------------------------------
                                  |   0 | SELECT STATEMENT           |                             |       |       |     9 (100)|          |
                                  |   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
                                  |   2 |   LOAD AS SELECT           |                             |       |       |            |          |
                                  |   3 |    COUNT                   |                             |       |       |            |          |
                                  |   4 |     TABLE ACCESS FULL      | T                           |     2 |       |     3   (0)| 00:00:01 |
                                  |   5 |   MERGE JOIN CARTESIAN     |                             |     4 |   104 |     6   (0)| 00:00:01 |
                                  |   6 |    VIEW                    |                             |     2 |    26 |     2   (0)| 00:00:01 |
                                  |   7 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9DA8BF_C8EEEA14 |     2 |    26 |     2   (0)| 00:00:01 |
                                  |   8 |    BUFFER SORT             |                             |     2 |    26 |     6   (0)| 00:00:01 |
                                  |   9 |     VIEW                   |                             |     2 |    26 |     2   (0)| 00:00:01 |
                                  |  10 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9DA8BF_C8EEEA14 |     2 |    26 |     2   (0)| 00:00:01 |
                                  ----------------------------------------------------------------------------------------------------------
                                  
                                  Note
                                  -----
                                     - dynamic sampling used for this statement
                                  Either materialized or not the result is correct in this release

                                  Let’s then see the other one 11.2.0.1.0
                                  mohamed@mhouri> with v as (select rownum r from t) select t1.r t1, t2.r t2 from v t1, v t2;
                                  
                                          T1         T2
                                  ---------- ----------
                                           1          1
                                           2          2
                                           3          1
                                           3          2
                                  
                                  mohamed@mhouri> select * from table(dbms_xplan.display_cursor);
                                  
                                  ------------------------------------------------------------------------------
                                  | Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                                  ------------------------------------------------------------------------------
                                  |   0 | SELECT STATEMENT      |      |       |       |     9 (100)|          |
                                  |   1 |  MERGE JOIN CARTESIAN |      |     4 |   104 |     9   (0)| 00:00:01 |
                                  |   2 |   VIEW                |      |     2 |    26 |     3   (0)| 00:00:01 |
                                  |   3 |    COUNT              |      |       |       |            |          |
                                  |   4 |     TABLE ACCESS FULL | T    |     2 |       |     3   (0)| 00:00:01 |
                                  |   5 |   BUFFER SORT         |      |     2 |    26 |     9   (0)| 00:00:01 |
                                  |   6 |    VIEW               |      |     2 |    26 |     3   (0)| 00:00:01 |
                                  |   7 |     COUNT             |      |       |       |            |          |
                                  |   8 |      TABLE ACCESS FULL| T    |     2 |       |     3   (0)| 00:00:01 |
                                  ------------------------------------------------------------------------------
                                  
                                  Note
                                  -----
                                     - dynamic sampling used for this statement (level=2)
                                  
                                  
                                  25 rows selected.
                                  
                                  mohamed@mhouri> with v as (select /*+ materialize */ rownum r from t) select t1.r t1, t2.r t2 from v t1, v t2;
                                  
                                          T1         T2
                                  ---------- ----------
                                           1          1
                                           1          2
                                           2          1
                                           2          2
                                  
                                  mohamed@mhouri> select * from table(dbms_xplan.display_cursor);
                                  
                                  ---------------------------------------------------------------------------------------------------------
                                  | Id  | Operation                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
                                  ---------------------------------------------------------------------------------------------------------
                                  |   0 | SELECT STATEMENT           |                            |       |       |     9 (100)|          |
                                  |   1 |  TEMP TABLE TRANSFORMATION |                            |       |       |            |          |
                                  |   2 |   LOAD AS SELECT           |                            |       |       |            |          |
                                  |   3 |    COUNT                   |                            |       |       |            |          |
                                  |   4 |     TABLE ACCESS FULL      | T                          |     2 |       |     3   (0)| 00:00:01 |
                                  |   5 |   MERGE JOIN CARTESIAN     |                            |     4 |   104 |     6   (0)| 00:00:01 |
                                  |   6 |    VIEW                    |                            |     2 |    26 |     2   (0)| 00:00:01 |
                                  |   7 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6609_2152332 |     2 |    26 |     2   (0)| 00:00:01 |
                                  |   8 |    BUFFER SORT             |                            |     2 |    26 |     6   (0)| 00:00:01 |
                                  |   9 |     VIEW                   |                            |     2 |    26 |     2   (0)| 00:00:01 |
                                  |  10 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6609_2152332 |     2 |    26 |     2   (0)| 00:00:01 |
                                  ---------------------------------------------------------------------------------------------------------
                                  
                                  Note
                                  -----
                                     - dynamic sampling used for this statement (level=2)
                                  Something went wrong during the upgrade

                                  However, one has to be aware that selecting rownum in a query block prevents the optimizer from merging that query block with its parent query and will force oracle to do not materialize the query block which is selecting rownum.

                                  In addition, and in a different context that has nothing do with the actual one, but which is worth remembering as far as we are dealing with ROWNUM, the presence of the rownum in a where clause will silently make the CBO working under the corresponding FIRST_ROWS_N mode (N being the value against which the ROWNUM is compared).

                                  For example a where clause like
                                  WHERE ROWNUM <= 1
                                  Will make the CBO runing under the buggy FIRST_ROWS mode


                                  Best regards
                                  Mohamed Houri
                                  www.hourim.wordpress.com