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.

Answer 1

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 < ((pageNumber * pageSize) + 1 )
)
WHERE r__ >= (((pageNumber-1) * pageSize) + 1)

Answer 2

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;

Answer 3

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

Answer 4

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

Answer 5

Try the following:

SELECT *
FROM
  (SELECT FIELDA,
    FIELDB,
    FIELDC,
    ROW_NUMBER() OVER (ORDER BY FIELDC) R
  FROM TABLE_NAME
  WHERE FIELDA = 10
  )
WHERE R >= 10
AND R   <= 15;

via [tecnicume]

Answer 6

    In SomeServiceClass 
    using npoco
    
    public async Task<List<SomeModel>> SomeServiceMethod(int pageIndex, int pageSize)
    
                    int lowerLimit;
                    int higherLimit;
    //This would help limit the result to 300 max. If the PageSize is stated as 290
    //we get the 1st to 289th result. If page size is 1845.
    //It returns 1845 - 299 = 1546. 1546th element to 1844th element
    
                    if((pageSize) < 300)
                    {
                        lowerLimit = 1;
                        higherLimit = pageSize;
                    }
                    else
                    {
                        int subtract = 300 - 1;
                        lowerLimit = pageSize - subtract;
                        higherLimit = pageSize;
                    }
    
    //Using Brian Schmitt script

    List<SomeModel> someVariableName = db.Query<SomeModel>(SELECT * FROM
    (
        SELECT a.*, rownum r__
        FROM
        (
            SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
            ORDER BY OrderDate DESC, ShippingDate DESC
        ) a
        WHERE rownum < (@0)
    )
    WHERE r__ >= (@1), higherlimit, lowerlimit).ToList();
    
    
    
    In SomeControllerClass

    [HttpGet]
    [Route("SomeControllerMethod")]
    public async Task<SomeResponseModel> SomeControllerMethod(int pageIndex, int pageSize)
    
    SomeServiceClass ssc = new SomeServiceClass();
    SomeResponseModel srm = new SomeResponseModel();
    
    List<SomeModel> resp = await ssc.SomeServiceMethod(pageIndex, pageSize);
    
    //Paging on API level, solution is from CSharpCorner. This would return a //max of 20 elements per page. 
    //So if you have a result of 300. It would be divided into 15 pages.
    //20 results per page. 

                    int totalRecords = resp.Count();
    
    
                    const int maxPageSize = 20;
                    pageSize = (pageSize > maxPageSize) ? maxPageSize : pageSize;
                    int pageNum = pageIndex;
                    int recordToTake = totalRecords - (pageNum - 1) * pageSize;
                    int CurrentPage = pageNum;
                    int TotalPages = (int)Math.Ceiling(totalRecords / (double)pageSize);
                    var previousPage = CurrentPage > 1 ? "Yes" : "No";
                    var nextPage = CurrentPage < TotalPages ? "Yes" : "No";
                    List<SomeModel> filteredResult = resp.Skip((CurrentPage - 1) * pageSize).Take(pageSize).ToList();
    
    
                    
                    srm.records = totalRecords;
                    srm.previousPage = previousPage;
                    srm.currentPage = $"Page: {CurrentPage} / {TotalPages}";
                    srm.nextPage = nextPage;
                    srm.totalPages = TotalPages;
                    srm.someIEnumerableProperty = filteredResult;
    
    return srm;


//How to use endpoint
//http://localhost:somePort/someControllerClass/SomeControllerMethod?pageIndex={pageIndex}&pageSize={pageSize}

//pageIndex will take value 1 to 15 since we have a max of 300 and 20 results per page.
//pageSize will be used to determine our higherlimit

Answer 7

In my project I used Oracle 12c and java. The paging code looks like this:

 public public List<Map<String, Object>> 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;
    }