8 Replies Latest reply: Jul 2, 2014 7:50 PM by Frank Kulash RSS

    Order by and ROWNUM interaction

    1172812

      Hi all,

       

      I have a query that has the form

       

      select *

      from (select...

              from...

              where...

              order by 1,2,3)

      where rownum < 300

       

      The problem is that if I do this, the subquery uses a lot of time that this method is highly unusable. Our last resort was to do this:

      select...

      from...

      where...

      and rownum < 300

      order by 1,2,3;

       

      Where in the query is the subquery from the earlier. This works but the ordering of the rows must come first before the limit since rows with the same IDs must all be together and not just those within the first 300 queried.

       

      Is there a better solution to this or would the first method be the only query that will suit our needs?

       

      Thanks in advance!

      Simon

        • 1. Re: Order by and ROWNUM interaction
          odie_63

          See if this performs any better :

          select *

          from (

            select col1

                 , col2

                 , col3

                 , row_number() over(order by col1, col2, col3) rn

            from ...

            where ...

          )

          where rn < 300 ;

          • 2. Re: Order by and ROWNUM interaction
            1172812

            Hi!

             

            I've also tried ROW_NUMBER() but it still only queries up to a specific row before ordering. While it performs better, it's not the totally same answer as I've had with the above query (which lasts 6 mins minimum).

             

            Thank You!

            • 3. Re: Order by and ROWNUM interaction
              Dom Brooks

              The method you state is perfectly usable and can be highly performant but it depends on the execution plan.

              If it's currently too slow, then it's probably not the SQL statement at fault but you need to look at providing more efficient access to get the top N result.

              For more info, see:

              HOW TO: Post a SQL statement tuning request - template posting

              When your query takes too long ...

              • 4. Re: Order by and ROWNUM interaction
                Dom Brooks

                > but it still only queries up to a specific row before ordering

                Not true

                • 5. Re: Order by and ROWNUM interaction
                  Frank Kulash

                  Hi,

                  1172812 wrote:

                   

                  Hi!

                   

                  I've also tried ROW_NUMBER() but it still only queries up to a specific row before ordering. While it performs better, it's not the totally same answer as I've had with the above query (which lasts 6 mins minimum).

                   

                  Thank You!

                  ROW_NUMBER can always be written to do exactly what ROWNUM does.  If you're getting different results with ROW_NUMBER and ROWNUM, then you've made a mistake, and it can be fixed.  It's hard to say what you're doing wrong without knowing what you're doing.  Post your code.

                   

                  As Dom said, post some sample data (CREATE TABLE and INSERT statements) and the results you want from that sample data, as described in the forum FAQ:  https://forums.oracle.com/message/9362002.

                  • 6. Re: Order by and ROWNUM interaction
                    1172812

                    Hi all,

                     

                    Thank you for all the replies. I tried ROW_NUMBER again and it does not return the same as my query using rownum. Furthermore, it also does not query everything first before the ordering since one of the IDs i have should have 4 rows but the ROW_NUMBER variant of the query only retrieves 2. For now, I'm using

                    select *

                    from (select...

                            from...

                      where...

                            order by 1,2,3)

                    where rownum < 300

                     

                    but again, it has serious performance issues since it's trully resource heavy (one of the resources in the inner from is a complex view). I appreciate the help guys. Thanks a lot!    

                    • 7. Re: Order by and ROWNUM interaction
                      rp0428

                      but again, if you want help with code you need to actually post the code you need help with.

                       

                      1. Read the FAQs you were ask to read

                      2. Post the information you were ask to post

                       

                      Ignoring the advice you have already been given only causes further delay in helping you.

                      • 8. Re: Order by and ROWNUM interaction
                        Frank Kulash

                        Hi,

                        1172812 wrote:

                         

                        Hi all,

                         

                        Thank you for all the replies. I tried ROW_NUMBER again and it does not return the same as my query using rownum. Furthermore, it also does not query everything first before the ordering since one of the IDs i have should have 4 rows but the ROW_NUMBER variant of the query only retrieves 2. For now, I'm using

                        select *

                        from (select...

                                from...

                          where...

                                order by 1,2,3)

                        where rownum < 300

                         

                        but again, it has serious performance issues since it's trully resource heavy (one of the resources in the inner from is a complex view). I appreciate the help guys. Thanks a lot!   

                        ROW_NUMBER CAN  produce exactly the same results as ROWNUM, but that's not the same as saying it WILL produce the same results.  You have to use it correctly.  Post your complete code (and your sample data, and results). You may want to simplify a little, e.g., use ROWNUM < 5 instead of ROWNUM < 300, just so you don't have to post so much sample data.  You'll get a solution that works well for either 5 or 300.