Paging with Oracle

Question

I am not as familiar with Oracle as I would like to be   I have some 250k records  and I want to display them 100 per page   Currently I have one stored procedure which retrieves all quarter of a million records to a dataset using a data adapter  and dataset  and the dataadapter Fill dataset  method on the results from the stored proc   If I have  Page Number  and  Number of records per page  as integer values I can pass as parameters  what would be the best way to get back just that particular section   Say  if I pass 10 as a page number  and 120 as number of pages  from the select statement it would give me the 1880th through 1200th  or something like that  my math in my head might be off     I m doing this in  NET with C   thought that s not important  if I can get it right on the sql side  then I should be cool    Update   I was able to use Brian s suggestion  and it is working great   I d like to work on some optimization  but the pages are coming up in 4 to 5 seconds rather than a minute  and my paging control was able to integrate in very well with my new stored procs

User · Answer

In my project I used Oracle 12c and java  The paging code looks like this    public public List lt Map lt String  Object gt  gt  getAllProductOfferWithPagination int pageNo  int pageElementSize  Long productOfferId  String productOfferName        try            if pageNo  1                 do nothing           else              pageNo  pageNo-1  pageElementSize 1                    System out println  algo pageNo      pageNo     pageElementSize     pageElementSize    productOfferId     productOfferId    productOfferName     productOfferName            String sql    SELECT   FROM   SELECT   FROM product offer po WHERE po deleted 0 AND  po product offer id   OR po product offer name LIKE                       ORDER BY po PRODUCT OFFER ID asc  foo OFFSET   ROWS FETCH NEXT   ROWS ONLY            return jdbcTemplate queryForList sql new Object    productOfferId     productOfferName     pageNo-1  pageElementSize           catch  Exception e            System out println e           e printStackTrace            return null

User · Answer

Ask Tom on pagination and very  very useful analytic functions  This is excerpt from that page  select   from       select     first rows 25          object id object name       row number   over      order by object id  rn     from all objects   where rn between  n and  m order by rn

User · Answer

Try the following   SELECT   FROM    SELECT FIELDA      FIELDB      FIELDC      ROW NUMBER   OVER  ORDER BY FIELDC  R   FROM TABLE NAME   WHERE FIELDA   10     WHERE R  gt   10 AND R    lt   15    via  tecnicume

User · Answer

Just want to summarize the answers and comments  There are a number of ways doing a pagination   Prior to oracle 12c there were no OFFSET FETCH functionality  so take a look at whitepaper as the  jasonk suggested  It s the most complete article I found about different methods with detailed explanation of advantages and disadvantages  It would take a significant amount of time to copy-paste them here  so I won t do it   There is also a good article from jooq creators explaining some common caveats with oracle and other databases pagination  jooq s blogpost  Good news  since oracle 12c we have a new OFFSET FETCH functionality  OracleMagazine 12c new features  Please refer to  Top-N Queries and Pagination   You may check your oracle version by issuing the following statement   SELECT   FROM V VERSION

User · Answer

Something like this should work  From Frans Bouma s Blog   SELECT   FROM       SELECT a    rownum r       FROM               SELECT   FROM ORDERS WHERE CustomerID LIKE  A           ORDER BY OrderDate DESC  ShippingDate DESC       a     WHERE rownum  lt    pageNumber   pageSize    1     WHERE r    gt      pageNumber-1    pageSize    1

User · Answer

Something like this should work  From Frans Bouma s Blog   SELECT   FROM       SELECT a    rownum r       FROM               SELECT   FROM ORDERS WHERE CustomerID LIKE  A           ORDER BY OrderDate DESC  ShippingDate DESC       a     WHERE rownum  lt    pageNumber   pageSize    1     WHERE r    gt      pageNumber-1    pageSize    1

User · Answer

Ask Tom on pagination and very  very useful analytic functions  This is excerpt from that page  select   from       select     first rows 25          object id object name       row number   over      order by object id  rn     from all objects   where rn between  n and  m order by rn

User · Answer

In the interest of completeness  for people looking for a more modern solution  in Oracle 12c there are some new features including better paging and top handling   Paging  The paging looks like this   SELECT   FROM user ORDER BY first name OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY    Top N Records  Getting the top records looks like this   SELECT   FROM user ORDER BY first name FETCH FIRST 5 ROWS ONLY   Notice how both the above query examples have ORDER BY clauses  The new commands respect these and are run on the sorted data   I couldn t find a good Oracle reference page for FETCH or OFFSET but this page has a great overview of these new features   Performance  As  wweicker points out in the comments below  performance is an issue with the new syntax in 12c  I didn t have a copy of 18c to test if Oracle has since improved it   Interestingly enough  my actual results were returned slightly quicker the first time I ran the queries on my table  113 million  rows  for the new method    New method  0 013 seconds  Old method  0 107 seconds    However  as  wweicker mentioned  the explain plan looks much worse for the new method    New method cost  300 110 Old method cost  30   The new syntax caused a full scan of the index on my column  which was the entire cost  Chances are  things get much worse when limiting on unindexed data    Let s have a look when including a single unindexed column on the previous dataset    New method time cost  189 55 seconds 998 908 Old method time cost  1 973 seconds 256   Summary  use with caution until Oracle improves this handling  If you have an index to work with  perhaps you can get away with using the new method    Hopefully I ll have a copy of 18c to play with soon and can update

User · Answer

Try the following   SELECT   FROM    SELECT FIELDA      FIELDB      FIELDC      ROW NUMBER   OVER  ORDER BY FIELDC  R   FROM TABLE NAME   WHERE FIELDA   10     WHERE R  gt   10 AND R    lt   15    via  tecnicume

User · Answer

In my project I used Oracle 12c and java  The paging code looks like this    public public List lt Map lt String  Object gt  gt  getAllProductOfferWithPagination int pageNo  int pageElementSize  Long productOfferId  String productOfferName        try            if pageNo  1                 do nothing           else              pageNo  pageNo-1  pageElementSize 1                    System out println  algo pageNo      pageNo     pageElementSize     pageElementSize    productOfferId     productOfferId    productOfferName     productOfferName            String sql    SELECT   FROM   SELECT   FROM product offer po WHERE po deleted 0 AND  po product offer id   OR po product offer name LIKE                       ORDER BY po PRODUCT OFFER ID asc  foo OFFSET   ROWS FETCH NEXT   ROWS ONLY            return jdbcTemplate queryForList sql new Object    productOfferId     productOfferName     pageNo-1  pageElementSize           catch  Exception e            System out println e           e printStackTrace            return null

User · Answer

Something like this should work  From Frans Bouma s Blog   SELECT   FROM       SELECT a    rownum r       FROM               SELECT   FROM ORDERS WHERE CustomerID LIKE  A           ORDER BY OrderDate DESC  ShippingDate DESC       a     WHERE rownum  lt    pageNumber   pageSize    1     WHERE r    gt      pageNumber-1    pageSize    1

User · Answer

Just want to summarize the answers and comments  There are a number of ways doing a pagination   Prior to oracle 12c there were no OFFSET FETCH functionality  so take a look at whitepaper as the  jasonk suggested  It s the most complete article I found about different methods with detailed explanation of advantages and disadvantages  It would take a significant amount of time to copy-paste them here  so I won t do it   There is also a good article from jooq creators explaining some common caveats with oracle and other databases pagination  jooq s blogpost  Good news  since oracle 12c we have a new OFFSET FETCH functionality  OracleMagazine 12c new features  Please refer to  Top-N Queries and Pagination   You may check your oracle version by issuing the following statement   SELECT   FROM V VERSION

User · Answer

Ask Tom on pagination and very  very useful analytic functions  This is excerpt from that page  select   from       select     first rows 25          object id object name       row number   over      order by object id  rn     from all objects   where rn between  n and  m order by rn

User · Answer

Ask Tom on pagination and very  very useful analytic functions  This is excerpt from that page  select   from       select     first rows 25          object id object name       row number   over      order by object id  rn     from all objects   where rn between  n and  m order by rn

User · Answer

In the interest of completeness  for people looking for a more modern solution  in Oracle 12c there are some new features including better paging and top handling   Paging  The paging looks like this   SELECT   FROM user ORDER BY first name OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY    Top N Records  Getting the top records looks like this   SELECT   FROM user ORDER BY first name FETCH FIRST 5 ROWS ONLY   Notice how both the above query examples have ORDER BY clauses  The new commands respect these and are run on the sorted data   I couldn t find a good Oracle reference page for FETCH or OFFSET but this page has a great overview of these new features   Performance  As  wweicker points out in the comments below  performance is an issue with the new syntax in 12c  I didn t have a copy of 18c to test if Oracle has since improved it   Interestingly enough  my actual results were returned slightly quicker the first time I ran the queries on my table  113 million  rows  for the new method    New method  0 013 seconds  Old method  0 107 seconds    However  as  wweicker mentioned  the explain plan looks much worse for the new method    New method cost  300 110 Old method cost  30   The new syntax caused a full scan of the index on my column  which was the entire cost  Chances are  things get much worse when limiting on unindexed data    Let s have a look when including a single unindexed column on the previous dataset    New method time cost  189 55 seconds 998 908 Old method time cost  1 973 seconds 256   Summary  use with caution until Oracle improves this handling  If you have an index to work with  perhaps you can get away with using the new method    Hopefully I ll have a copy of 18c to play with soon and can update

User · Answer

Something like this should work  From Frans Bouma s Blog   SELECT   FROM       SELECT a    rownum r       FROM               SELECT   FROM ORDERS WHERE CustomerID LIKE  A           ORDER BY OrderDate DESC  ShippingDate DESC       a     WHERE rownum  lt    pageNumber   pageSize    1     WHERE r    gt      pageNumber-1    pageSize    1

[sql] Paging with Oracle

Examples related to sql

Examples related to oracle

Examples related to stored-procedures