9 Replies Latest reply on Jun 8, 2020 2:28 PM by 4264084

    Oracle sql keyset/seek-method pagination with extra where filter

    4264084

      Hello Oracle SQL community,

       

      I have recently run into the need of making a keyset pagination.

       

      I´m thinking the following query:

       

      SELECT T2.ID, T2.LAST_MODIFIED_DATE
      FROM (SELECT T.ID, T.LAST_MODIFIED_DATE
        
      FROM TABLE T
        
      WHERE T.ID > :LAST_ID_PROCESSED
        
      ORDER BY T.ID) T2
      WHERE T2.LAST_MODIFIED_DATE < :LAST_MODIFIED_DATE_FILTER
        
      AND ROWNUM <= 500

       

       

      However, im not sure about two things

      - "WHERE T2.LAST_MODIFIED_DATE < :LAST_MODIFIED_DATE_FILTER" would change the results of the stable pagination and progresive pagination given by keyset pagination with top-n query.

      - Also, can this be combined with PARALLEL optimizer hints without affecting the results?

       

      I did the same question in stackoverflow https://stackoverflow.com/questions/62154231/oracle-sql-keyset-seek-method-pagination-with-extra-where-filter. I leave the question just in case somebody wants some points there.

       

      Thanks from now.

       

       

        • 1. Re: Oracle sql keyset/seek-method pagination with extra where filter
          L. Fernigrini

          You did not mention your version, but on 12c pagination is simpler to implement using OFFSET and FETCH, as explained here:

           

          https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1#paging

           

           

          You should have both conditions on the inner query in order to make it simpler and quicker to process (since you are ordering and then applying the "WHERE T2.LAST_MODIFIED_DATE < :LAST_MODIFIED_DATE_FILTER" filter).

          • 2. Re: Oracle sql keyset/seek-method pagination with extra where filter
            4264084

            I need the pagination to be resilient to inserts and deletes. I belive that with OFFSET and FETCH I might lose some rows if I get some deleted or inserted in the mean time, that´s why Im using keyset/seek-method pagination.

             

            I want this to work "seek" style, wont applying the where inside filter first and then order, why?

            • 3. Re: Oracle sql keyset/seek-method pagination with extra where filter
              BluShadow

              If you want pagination of data, that is resilient to deletes and inserts, then you'll need to run your query for the whole data set and store those results somewhere.

              Then you can apply pagination to that result set, which, let's say you want 500 rows per page, you could apply a grouping to that result set by simply grouping a floored row number.

              Example with 50 rows of data in the results wanting 10 per page...

               

              SQL> ed
              Wrote file afiedt.buf

                1  with t as (select row_number() over (order by level) as rn, level*2 as data from dual connect by level <= 50)
                2  -- end of my example result set
                3  --
                4  -- apply pagination
                5  select data
                6        ,floor((rn-1)/10) as page
                7* from  t
              SQL> /

                    DATA      PAGE
              ---------- ----------
                      2          0
                      4          0
                      6          0
                      8          0
                      10          0
                      12          0
                      14          0
                      16          0
                      18          0
                      20          0
                      22          1
                      24          1
                      26          1
                      28          1
                      30          1
                      32          1
                      34          1
                      36          1
                      38          1
                      40          1
                      42          2
                      44          2
                      46          2
                      48          2
                      50          2
                      52          2
                      54          2
                      56          2
                      58          2
                      60          2
                      62          3
                      64          3
                      66          3
                      68          3
                      70          3
                      72          3
                      74          3
                      76          3
                      78          3
                      80          3
                      82          4
                      84          4
                      86          4
                      88          4
                      90          4
                      92          4
                      94          4
                      96          4
                      98          4
                     100          4

               

              50 rows selected.

               

              So, as long as you've stored your result set somewhere to make it resilient, the data on each page will remain consistent too.

              • 4. Re: Oracle sql keyset/seek-method pagination with extra where filter
                L. Fernigrini

                That's not easy to achieve, as BluShadow already mentioned. You may want to store the result set on some temporary structure.

                 

                Or you may need to play with isolation leves (SERIALIZABLE) to ensure that all the times you request a "page" of your report you get the query executed against the same "source" set of data, ignoring changes made by other sessions.

                 

                 

                The different isolation levels available on Oracle are greatly explained here:

                https://blogs.oracle.com/oraclemagazine/on-transaction-isolation-levels

                • 5. Re: Oracle sql keyset/seek-method pagination with extra where filter
                  4264084

                  I feel I might have failed to explain my use case a little. Please let me add some information.

                   

                  1) I only want to go through the table records in forward direction acording to "asc id".

                  2) I dont mind if I lose an insert done in a "page" or iteration that was already processed.

                  3) I do mind if I lose a row that should be in the next page because of the insert or a delete in a row with id < lastProcessedId.

                  4) I dont want to process thoose with T2.LAST_MODIFIED_DATE < :LAST_MODIFIED_DATE_FILTER

                   

                  I know that without the T2.LAST_MODIFIED_DATE < :LAST_MODIFIED_DATE_FILTER the query should be ok.

                  But Im not sure of the actual effect of T2.LAST_MODIFIED_DATE < :LAST_MODIFIED_DATE_FILTER on the ordering and scanning in forward direction.

                  • 6. Re: Oracle sql keyset/seek-method pagination with extra where filter
                    BluShadow

                    4104f7cb-4c19-4c7f-babb-3a6784aadf95 wrote:

                     

                    I feel I might have failed to explain my use case a little. Please let me add some information.

                     

                    1) I only want to go through the table records in forward direction acording to "asc id".

                    2) I dont mind if I lose an insert done in a "page" or iteration that was already processed.

                    3) I do mind if I lose a row that should be in the next page because of the insert or a delete in a row with id < lastProcessedId.

                    4) I dont want to process thoose with T2.LAST_MODIFIED_DATE < :LAST_MODIFIED_DATE_FILTER

                     

                    I know that without the T2.LAST_MODIFIED_DATE < :LAST_MODIFIED_DATE_FILTER the query should be ok.

                    But Im not sure of the actual effect of T2.LAST_MODIFIED_DATE < :LAST_MODIFIED_DATE_FILTER on the ordering and scanning in forward direction.

                     

                     

                    Unfortunately it's still not clear.

                    It depends what you mean by "lose a row".

                    Are you talking about actually locking rows on the source table to prevent users from removing things that haven't been processed on a "page"? or just that once you've got your query results, you want those results to remain until the page as been viewed/processed in some way, but the underlying data the results came from could still be changed by other users in the meantime?

                    I think we need to understand what you're trying to achieve from a user/interface perspective too, to help us grasp what you're technically after.

                    • 7. Re: Oracle sql keyset/seek-method pagination with extra where filter
                      4264084

                      We have an application that copies the row´s information to other storage every time a row is either inserted, deleted or updated, this is a backend application that copies to another database for analytics. For some time this application had a bug wich made it copy incorrect information in some cases. The bug was fixed and those rows that are being inserted, deleted or updated are getting fixed automatically in the other storage. However, for those rows that haven´t been modified the information remains incorrect in the other storage.

                       

                      What I want to archive with the query is going though the table in batches using SQL and copying again those rows that have not been yet modified and fixed automatically. Ignoring those that have been modified would be done with T2.LAST_MODIFIED_DATE < :LAST_MODIFIED_DATE_FILTER .

                      • 8. Re: Oracle sql keyset/seek-method pagination with extra where filter
                        L. Fernigrini

                        So what happens if you select a row that has not been modified, and while you are preparing to update it on the "storage" another session changes it (and the change is propagated to the storage). When you update it you would be overwriting the changes done in the meanwhile.

                         

                        You may want to lock the entire set of rows and release the lock once you finshed updating the data.

                         

                        Or you may just pick the first 500 (or N) that were not modified after the fix, locking them, update on the storage, and save the IDs of those 500 already updated / fixed. Then pick the next 500 that not modified after the fix and do not exists on the list of "fixed" rows, locking them, update the storage, include the new batch on the list of already updated / fixed.

                         

                        That way you will reduce contention while avoiding skipping rows..

                        • 9. Re: Oracle sql keyset/seek-method pagination with extra where filter
                          4264084

                          Your view of this race condition is highly important and the posible solution great.

                           

                          Hopefully we have that handled at the moment with optimistic locking at the other storage using versioning and retries picking up again the info from the database.