1 2 Previous Next 24 Replies Latest reply: May 16, 2012 4:15 AM by 431338 RSS

    question about rownum

    431338
      My oracle version is 10.2.0.4.0.
      I have a complex sql without rownum, it returns 330 rows. When I add rownum limit, it appears different:

      1. without rownum: returns 330 rows.
      2. with rownum<=3000: returns 0 rows
      3. rownum<=1100: returns 170 rows
      4. rownum<=1180: returns 160 rows

      The base sql is:

      SELECT n.*
      FROM t1 p1,
      t2 pb1,
      t2 pb,
      t5 nd,
      t4 l,
      t4 CITY,
      t5 nd1,
      t6 ps,
      t3 n1,
      t8 c,
      t1 p,
      t7 lk,
      t6 ps1,
      t3 n
      WHERE 1 = 1
      AND CITY.NAME = L.NAME
      AND c.LINKID = lk.LINKID
      AND ((p1.PORTID = lk.LINK2DESTPORT AND p.PORTID = lk.LINK2SOURCEPORT) OR (p1.PORTID = lk.LINK2SOURCEPORT AND p.PORTID = lk.LINK2DESTPORT))
      AND n1.NODEID = p1.PORT2NODE
      AND p1.PORTID = pb1.PORTID
      AND n.NODE2LOCATION = l.LOCATIONID
      AND p.PORTID = pb.PORTID
      AND n.NODE2NODEDEF = nd.NODEDEFID
      AND n1.NODE2NODEDEF = nd1.NODEDEFID
      AND p.PORT2PROVISIONSTATUS = ps.PROVISIONSTATUSID
      AND n.NODE2PROVISIONSTATUS = ps1.PROVISIONSTATUSID
      AND n.NODEID = p.PORT2NODE
      AND lk.LINK2LINKTYPE = 1900730001
      AND N.NAME LIKE '%XXXX%';

      Can anyone tell me why?
        • 1. Re: question about rownum
          Himanshu Binjola
          Are you adding ROWNUM as AND clause?

          Result Set without ROWNUM = 330. Now add below condition
          AND ROWNUM <= &rows_output
          rows_output <=330 - Always return count equal to rows_output

          rows_output > 330 - Always return 330
          SELECT n.*
          FROM   t1 p1,
                 t2 pb1,
                 t2 pb,
                 t5 nd,
                 t4 l,
                 t4 city,
                 t5 nd1,
                 t6 ps,
                 t3 n1,
                 t8 c,
                 t1 p,
                 t7 lk,
                 t6 ps1,
                 t3 n
          WHERE  1 = 1
          AND    city.name = l.name
          AND    c.linkid = lk.linkid
          AND    ((p1.portid = lk.link2destport AND p.portid = lk.link2sourceport) OR
                (p1.portid = lk.link2sourceport AND p.portid = lk.link2destport))
          AND    n1.nodeid = p1.port2node
          AND    p1.portid = pb1.portid
          AND    n.node2location = l.locationid
          AND    p.portid = pb.portid
          AND    n.node2nodedef = nd.nodedefid
          AND    n1.node2nodedef = nd1.nodedefid
          AND    p.port2provisionstatus = ps.provisionstatusid
          AND    n.node2provisionstatus = ps1.provisionstatusid
          AND    n.nodeid = p.port2node
          AND    lk.link2linktype = 1900730001
          AND    n.name LIKE '%XXXX%'
          AND    ROWNUM <= &rows_output;
          Edited by: Himanshu Binjola on May 10, 2012 12:45 AM
          • 2. Re: question about rownum
            HuaMin Chen
            Limiting Result Sets

            ROWNUM is a magic column in Oracle Database that gets many people into trouble. When you learn what it is and how it works, however, it can be very useful. I use it for two main things:

            To perform top- N processing. This is similar to using the LIMIT clause, available in some other databases.
            To paginate through a query, typically in a stateless environment such as the Web. I use this technique on the asktom.oracle.com Web site.



            I'll take a look at each of these uses after I review how ROWNUM works.

            How ROWNUM Works

            ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N , where N is the number of rows in the set ROWNUM is used with. A ROWNUM value is not assigned permanently to a row (this is a common misconception). A row in a table does not have a number; you cannot ask for row 5 from a table—there is no such thing.

            Also confusing to many people is when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:

            select *
            from t
            where ROWNUM > 1;



            Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1. Consider a query with this structure:

            select ..., ROWNUM
            from t
            where <where clause>
            group by <columns>
            having <having clause>
            order by <columns>;
            • 3. Re: question about rownum
              BluShadow
              pe**** wrote:
              My oracle version is 10.2.0.4.0.
              I have a complex sql without rownum, it returns 330 rows. When I add rownum limit, it appears different:

              1. without rownum: returns 330 rows.
              2. with rownum<=3000: returns 0 rows
              3. rownum<=1100: returns 170 rows
              4. rownum<=1180: returns 160 rows

              Can anyone tell me why?
              Not really. You've not provided a test case for us to reproduce the issue, and there's no indication of you using rownum in the SQL you've provided.

              You say that's the "base" SQL, but is that based around a subquery where you're including the rownum?

              Without data, and the real SQL, we'll have to polish off our crystal balls and seek spiritual guidance, and to be honest I've got more important work to be getting on with than to waste time guessing what your issue might be if you can't give us sufficient details.

              {message:id=9360002}
              • 4. Re: question about rownum
                696547
                Great Explanation by HuaMin, I think its utmost important to understand why a certain feature is available in Oracle.

                Code snippet can resolve a particular query, but concept will resolve everything around that feature that can go wrong in future because of wrong implementation.

                Regards,
                Ankit Rathi
                http://theoraclelog.blogspot.in
                • 5. Re: question about rownum
                  431338
                  Hi all, the sql:

                  1. without rownum: returns 330 rows(correct).
                  SELECT n.*
                  FROM t1 p1,
                  t2 pb1,
                  t2 pb,
                  t5 nd,
                  t4 l,
                  t4 CITY,
                  t5 nd1,
                  t6 ps,
                  t3 n1,
                  t8 c,
                  t1 p,
                  t7 lk,
                  t6 ps1,
                  t3 n
                  WHERE 1 = 1
                  AND CITY.NAME = L.NAME
                  AND c.LINKID = lk.LINKID
                  AND ((p1.PORTID = lk.LINK2DESTPORT AND p.PORTID = lk.LINK2SOURCEPORT) OR (p1.PORTID = lk.LINK2SOURCEPORT AND p.PORTID = lk.LINK2DESTPORT))
                  AND n1.NODEID = p1.PORT2NODE
                  AND p1.PORTID = pb1.PORTID
                  AND n.NODE2LOCATION = l.LOCATIONID
                  AND p.PORTID = pb.PORTID
                  AND n.NODE2NODEDEF = nd.NODEDEFID
                  AND n1.NODE2NODEDEF = nd1.NODEDEFID
                  AND p.PORT2PROVISIONSTATUS = ps.PROVISIONSTATUSID
                  AND n.NODE2PROVISIONSTATUS = ps1.PROVISIONSTATUSID
                  AND n.NODEID = p.PORT2NODE
                  AND lk.LINK2LINKTYPE = 1900730001
                  AND N.NAME LIKE '%XXXX%';


                  2. with rownum<=3000: returns 0 rows
                  SELECT n.*
                  FROM t1 p1,
                  t2 pb1,
                  t2 pb,
                  t5 nd,
                  t4 l,
                  t4 CITY,
                  t5 nd1,
                  t6 ps,
                  t3 n1,
                  t8 c,
                  t1 p,
                  t7 lk,
                  t6 ps1,
                  t3 n
                  WHERE 1 = 1
                  AND CITY.NAME = L.NAME
                  AND c.LINKID = lk.LINKID
                  AND ((p1.PORTID = lk.LINK2DESTPORT AND p.PORTID = lk.LINK2SOURCEPORT) OR (p1.PORTID = lk.LINK2SOURCEPORT AND p.PORTID = lk.LINK2DESTPORT))
                  AND n1.NODEID = p1.PORT2NODE
                  AND p1.PORTID = pb1.PORTID
                  AND n.NODE2LOCATION = l.LOCATIONID
                  AND p.PORTID = pb.PORTID
                  AND n.NODE2NODEDEF = nd.NODEDEFID
                  AND n1.NODE2NODEDEF = nd1.NODEDEFID
                  AND p.PORT2PROVISIONSTATUS = ps.PROVISIONSTATUSID
                  AND n.NODE2PROVISIONSTATUS = ps1.PROVISIONSTATUSID
                  AND n.NODEID = p.PORT2NODE
                  AND lk.LINK2LINKTYPE = 1900730001
                  AND N.NAME LIKE '%XXXX%'
                  AND rownum <= 3000;

                  3. rownum<=1100: returns 170 rows
                  SELECT n.*
                  FROM t1 p1,
                  t2 pb1,
                  t2 pb,
                  t5 nd,
                  t4 l,
                  t4 CITY,
                  t5 nd1,
                  t6 ps,
                  t3 n1,
                  t8 c,
                  t1 p,
                  t7 lk,
                  t6 ps1,
                  t3 n
                  WHERE 1 = 1
                  AND CITY.NAME = L.NAME
                  AND c.LINKID = lk.LINKID
                  AND ((p1.PORTID = lk.LINK2DESTPORT AND p.PORTID = lk.LINK2SOURCEPORT) OR (p1.PORTID = lk.LINK2SOURCEPORT AND p.PORTID = lk.LINK2DESTPORT))
                  AND n1.NODEID = p1.PORT2NODE
                  AND p1.PORTID = pb1.PORTID
                  AND n.NODE2LOCATION = l.LOCATIONID
                  AND p.PORTID = pb.PORTID
                  AND n.NODE2NODEDEF = nd.NODEDEFID
                  AND n1.NODE2NODEDEF = nd1.NODEDEFID
                  AND p.PORT2PROVISIONSTATUS = ps.PROVISIONSTATUSID
                  AND n.NODE2PROVISIONSTATUS = ps1.PROVISIONSTATUSID
                  AND n.NODEID = p.PORT2NODE
                  AND lk.LINK2LINKTYPE = 1900730001
                  AND N.NAME LIKE '%XXXX%'
                  AND rownum <= 1100;

                  4. rownum<=1180: returns 160 rows
                  SELECT n.*
                  FROM t1 p1,
                  t2 pb1,
                  t2 pb,
                  t5 nd,
                  t4 l,
                  t4 CITY,
                  t5 nd1,
                  t6 ps,
                  t3 n1,
                  t8 c,
                  t1 p,
                  t7 lk,
                  t6 ps1,
                  t3 n
                  WHERE 1 = 1
                  AND CITY.NAME = L.NAME
                  AND c.LINKID = lk.LINKID
                  AND ((p1.PORTID = lk.LINK2DESTPORT AND p.PORTID = lk.LINK2SOURCEPORT) OR (p1.PORTID = lk.LINK2SOURCEPORT AND p.PORTID = lk.LINK2DESTPORT))
                  AND n1.NODEID = p1.PORT2NODE
                  AND p1.PORTID = pb1.PORTID
                  AND n.NODE2LOCATION = l.LOCATIONID
                  AND p.PORTID = pb.PORTID
                  AND n.NODE2NODEDEF = nd.NODEDEFID
                  AND n1.NODE2NODEDEF = nd1.NODEDEFID
                  AND p.PORT2PROVISIONSTATUS = ps.PROVISIONSTATUSID
                  AND n.NODE2PROVISIONSTATUS = ps1.PROVISIONSTATUSID
                  AND n.NODEID = p.PORT2NODE
                  AND lk.LINK2LINKTYPE = 1900730001
                  AND N.NAME LIKE '%XXXX%'
                  AND rownum <= 1180;

                  5. add hint,rownum<=3000:return 330 rows(correct)
                  SELECT /*+ordered use_nl(n,l) use_nl(l,city) use_nl(n,p) use_nl(p, pb) use_nl(pb,lk) use_nl(lk, c) use_nl(c,p1) use_nl(p1,pb1) use_nl(pb1,n1)
                  use_nl(n1,l)
                  */
                  n.*
                  FROM t3 n,
                  t4 l,
                  t4 CITY,
                  t1 p,
                  t2 pb,
                  t7 lk,
                  t8 c,
                  t1 p1,
                  t2 pb1,
                  t3 n1,
                  t5 nd,
                  t5 nd1,
                  t6 ps,
                  t6 ps1
                  WHERE 1 = 1
                  AND CITY.NAME = L.NAME
                  AND c.LINKID = lk.LINKID
                  AND ((p1.PORTID = lk.LINK2DESTPORT AND p.PORTID = lk.LINK2SOURCEPORT) OR (p1.PORTID = lk.LINK2SOURCEPORT AND p.PORTID = lk.LINK2DESTPORT))
                  AND n1.NODEID = p1.PORT2NODE
                  AND p1.PORTID = pb1.PORTID
                  AND n.NODE2LOCATION = l.LOCATIONID
                  AND p.PORTID = pb.PORTID
                  AND n.NODE2NODEDEF = nd.NODEDEFID
                  AND n1.NODE2NODEDEF = nd1.NODEDEFID
                  AND p.PORT2PROVISIONSTATUS = ps.PROVISIONSTATUSID
                  AND n.NODE2PROVISIONSTATUS = ps1.PROVISIONSTATUSID
                  AND n.NODEID = p.PORT2NODE
                  AND lk.LINK2LINKTYPE = 1900730001
                  AND N.NAME LIKE '%XXXX%'
                  AND rownum <= 3000;
                  • 6. Re: question about rownum
                    Marwim
                    Could it be, that one of your tables has a column named ROWNUM?
                    drop table test_rownum;
                    create table test_rownum (
                    "ROWNUM" varchar2(100)
                    );
                    
                    insert into test_rownum values ('100');
                    insert into test_rownum values ('110');
                    insert into test_rownum values ('300');
                    insert into test_rownum values ('3100');
                    insert into test_rownum values ('8100');
                    
                    select * from test_rownum where "ROWNUM" <= '1';
                    
                    ROWNUM                                                                                               
                    ------
                    
                    select * from test_rownum where "ROWNUM" < '2';
                    
                    ROWNUM                                                                                               
                    ------
                    100                                                                                                  
                    110
                    
                    select * from test_rownum where "ROWNUM" < '10';
                    
                    ROWNUM                                                                                               
                    ------
                    Regards
                    Marcus
                    • 7. Re: question about rownum
                      431338
                      These tables have no columns named ROWNUM.

                      I found CONCATENATION step in explain plan, that generated by condition "((p1.PORTID = lk.LINK2DESTPORT AND p.PORTID = lk.LINK2SOURCEPORT) OR (p1.PORTID = lk.LINK2SOURCEPORT AND p.PORTID = lk.LINK2DESTPORT))". That means the sql is split into two parts and then executes concatenation operation.

                      If I change the sql into below, everything is ok, the sql returns 160 rows(correct):

                      SELECT n.*
                      FROM t1 p1,
                      t2 pb1,
                      t2 pb,
                      t5 nd,
                      t4 l,
                      t4 CITY,
                      t5 nd1,
                      t6 ps,
                      t3 n1,
                      t8 c,
                      t1 p,
                      t7 lk,
                      t6 ps1,
                      t3 n
                      WHERE 1 = 1
                      AND CITY.NAME = L.NAME
                      AND c.LINKID = lk.LINKID
                      AND *((p1.PORTID = lk.LINK2DESTPORT AND p.PORTID = lk.LINK2SOURCEPORT))*
                      AND n1.NODEID = p1.PORT2NODE
                      AND p1.PORTID = pb1.PORTID
                      AND n.NODE2LOCATION = l.LOCATIONID
                      AND p.PORTID = pb.PORTID
                      AND n.NODE2NODEDEF = nd.NODEDEFID
                      AND n1.NODE2NODEDEF = nd1.NODEDEFID
                      AND p.PORT2PROVISIONSTATUS = ps.PROVISIONSTATUSID
                      AND n.NODE2PROVISIONSTATUS = ps1.PROVISIONSTATUSID
                      AND n.NODEID = p.PORT2NODE
                      AND lk.LINK2LINKTYPE = 1900730001
                      AND N.NAME LIKE '%XXXX%';
                      AND rownum <= 3000;

                      The below sql returns 170 rows(correct):

                      SELECT n.*
                      FROM t1 p1,
                      t2 pb1,
                      t2 pb,
                      t5 nd,
                      t4 l,
                      t4 CITY,
                      t5 nd1,
                      t6 ps,
                      t3 n1,
                      t8 c,
                      t1 p,
                      t7 lk,
                      t6 ps1,
                      t3 n
                      WHERE 1 = 1
                      AND CITY.NAME = L.NAME
                      AND c.LINKID = lk.LINKID
                      AND *((p1.PORTID = lk.LINK2SOURCEPORT AND p.PORTID = lk.LINK2DESTPORT))*
                      AND n1.NODEID = p1.PORT2NODE
                      AND p1.PORTID = pb1.PORTID
                      AND n.NODE2LOCATION = l.LOCATIONID
                      AND p.PORTID = pb.PORTID
                      AND n.NODE2NODEDEF = nd.NODEDEFID
                      AND n1.NODE2NODEDEF = nd1.NODEDEFID
                      AND p.PORT2PROVISIONSTATUS = ps.PROVISIONSTATUSID
                      AND n.NODE2PROVISIONSTATUS = ps1.PROVISIONSTATUSID
                      AND n.NODEID = p.PORT2NODE
                      AND lk.LINK2LINKTYPE = 1900730001
                      AND N.NAME LIKE '%XXXX%';
                      AND rownum <= 3000;
                      • 8. Re: question about rownum
                        Sudhakar_B
                        Hi,
                        Seems like Optimizer is applying the AND predicates (including the ROWNUM) after applying the ROWNUM predicate.
                        Most consistent way to limit number of rows using ROWUM, I have seen is to wrap the query (inline it)
                        select t.* from
                        (
                        SELECT n.*
                        FROM t1 p1,
                        t2 pb1,
                        t2 pb,
                        t5 nd,
                        t4 l,
                        t4 CITY,
                        t5 nd1,
                        t6 ps,
                        t3 n1,
                        t8 c,
                        t1 p,
                        t7 lk,
                        t6 ps1,
                        t3 n
                        WHERE 1 = 1
                        AND CITY.NAME = L.NAME
                        AND c.LINKID = lk.LINKID
                        AND ((p1.PORTID = lk.LINK2DESTPORT AND p.PORTID = lk.LINK2SOURCEPORT) OR (p1.PORTID = lk.LINK2SOURCEPORT AND p.PORTID = lk.LINK2DESTPORT))
                        AND n1.NODEID = p1.PORT2NODE
                        AND p1.PORTID = pb1.PORTID
                        AND n.NODE2LOCATION = l.LOCATIONID
                        AND p.PORTID = pb.PORTID
                        AND n.NODE2NODEDEF = nd.NODEDEFID
                        AND n1.NODE2NODEDEF = nd1.NODEDEFID
                        AND p.PORT2PROVISIONSTATUS = ps.PROVISIONSTATUSID
                        AND n.NODE2PROVISIONSTATUS = ps1.PROVISIONSTATUSID
                        AND n.NODEID = p.PORT2NODE
                        AND lk.LINK2LINKTYPE = 1900730001
                        AND N.NAME LIKE '%XXXX%';
                        )
                        where ROWNUM < 3000
                        ;
                        Hope this helps.
                        vr
                        • 9. Re: question about rownum
                          Marwim
                          I found CONCATENATION step in explain plan,
                          Can you post the explain plan?
                          • 10. Re: question about rownum
                            rp0428
                            This response appears to plagiarize Tom Kyte's material as it seems to be a copy of this article
                            http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

                            While it is ok to use excerpts from copyrighted material in answering questions you always need to provide the source of the material and attribute it to the author.

                            As far as I can tell every word in your reply came from Tom's article.

                            Please edit your post to indicate the true source of the material you posted and do the same in all future replies.
                            • 11. Re: question about rownum
                              rp0428
                              >
                              Great Explanation by HuaMin
                              >
                              Tom Kyte gets the credit for that explanation.

                              Read this post and see if you don't agree.
                              http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
                              • 12. Re: question about rownum
                                431338
                                select * from (select n.* from ... where ...) where rownum<3000 still return 0 rows.
                                • 13. Re: question about rownum
                                  431338
                                  I'll post the explain plan tomorrow morning.

                                  Edited by: pe**** on 2012-5-10 上午10:01
                                  • 14. Re: question about rownum
                                    431338
                                    How can I post the code with courier new font?
                                    1 2 Previous Next