9 Replies Latest reply: Sep 13, 2013 12:33 AM by Billy~Verreynne RSS

    Pagination through Sql in Oracle

    960593

      Hi ,

       

      I have a big table which has to be used for pagination.Currently we are using the below functionality

       

      SELECT * FROM (SELECT *  FROM code WHERE code_id >100 ORDER BY code_id)

      where rownum < 100;

       

      Explaination:In the first iteration  we are not passing any where condition from Ui the query will be like  below

       

      SELECT * FROM (SELECT *  FROM code ORDER BY code_id)

      where rownum < 100;

      but in the second iteration we will take the maximum id from the result set and passed in the query like below if the maximum code_id is 100 then the query will be like  for 100 rows pagination

       

      SELECT * FROM (SELECT *  FROM code WHERE code_id >100 ORDER BY code_id)

      where rownum < 100;

       

      But there are cavets for the above query like if there are 10 records in the code_id 100 and in the previous iteration if few are selected we not selecting the others in the next iteration as the condition SELECT *  FROM code WHERE code_id >100 ORDER BY code_id will be false and missing these values.

       

      For this we have implemented row_number  but row_number usage has slow response.

       

      Please suggest any body has any better solutions.

       

      I am using  Oracle 11g

        • 1. Re: Pagination through Sql in Oracle
          BluShadow

          you could just use rownum...

           

          select *

          from (

                SELECT rownum rn

                      ,x.*

                FROM (SELECT * FROM code ORDER BY code_id) x

               )

          where rn between 101 and 200;

          • 2. Re: Pagination through Sql in Oracle
            Billy~Verreynne

            Sorting data, in order to grab a specific subset from it, will be slow. And the bigger the set to sort (in order to create a sequence to identify and access a subset), the slower performance.

             

            Pagination through a large data set is flawed IMO. This means the user does not know what he/she wants to see.. and is now browsing through mass data in the hope of spotting something which could be of interest.

             

            That is not how robust, performant and flexible reporting and analysis systems are built.

            • 3. Re: Pagination through Sql in Oracle
              Pacmann

              Hi,

               

              Upgrade to 12c and you'll be allowed to write this

               

              SELECT *

              FROM code

              ORDER BY code_id

              OFFSET 100

              FETCH NEXT 100 ROWS ONLY

              • 4. Re: Pagination through Sql in Oracle
                Pacmann

                @BillyVerreynne for example someone reading a forum ?

                If sort column is indexed, it might perform correctly.

                • 5. Re: Pagination through Sql in Oracle
                  Billy~Verreynne

                  Indexes and 12c SQL features are technical solutions to technical problems.

                   

                  I am saying that pagination of large data sets is not a technical problem, but a functional one. As I do not believe that is how reporting systems should be designed, irrespective of whether or not there are technical features that can be used to implement it.

                   

                  The basic question is what is the business purpose of a user paging through a large data set? What is the business requirement? As it is a waste of business resources and time for an employee to look for "something" in a huge pile of "stuff".

                   

                  Identify the actual need, and the functional requirement is known and can be evaluated within the business context - after which the requirement can become part of the project scope, design and development.

                  • 6. Re: Pagination through Sql in Oracle
                    960593

                    Thanks Billy for the reply and the analysis. Already we have thought of this solution but it is rejected by the Performance engineering team as this is taking more time

                     

                    select *

                    from (

                          SELECT rownum rn

                                ,x.*

                          FROM (SELECT * FROM code ORDER BY code_id) x

                         )

                    where rn between 101 and 200;



                    Is it possible to achieve this by Procedure


                    Regards,

                    Papi

                    • 7. Re: Pagination through Sql in Oracle
                      Hoek

                      Have you tried/tested with the FIRST_ROWS hint?

                      Ask Tom: On Top-n and Pagination Queries

                      • 8. Re: Pagination through Sql in Oracle
                        BluShadow

                        960593 wrote:

                         

                        Thanks Billy for the reply and the analysis. Already we have thought of this solution but it is rejected by the Performance engineering team as this is taking more time

                         

                        select *

                        from (

                              SELECT rownum rn

                                    ,x.*

                              FROM (SELECT * FROM code ORDER BY code_id) x

                             )

                        where rn between 101 and 200;



                        Is it possible to achieve this by Procedure


                        Regards,

                        Papi

                         

                        If you have a "Performance engineering team" why aren't they suggesting the solution?

                         

                        Why do you think a procedure would make things go any faster?  Never do in PL/SQL what can be achieved in SQL... you will only make it slower.

                         

                        I do agree with Billy though.  The requirements shouldn't be to just let users browse through thousands of records.  They should be able to search and restrict for the information they want.

                        • 9. Re: Pagination through Sql in Oracle
                          Billy~Verreynne

                          A procedure will only make it slower.

                           

                          The problem is not the SQL. The problem is not even with procedures.

                           

                          The problem is poor implementation of a silly (unsubstantiated) business requirement on a mass data volume.

                           

                          It always will be slow. It always will be unable to scale.