How to deal with pagination for a large, non materializable resultset in Oracle and Java

Question

I'm dealing with some sort of problem here. A web application built on java stuff calls a stored procedure in oracle which has as out parameters some varchars and a parameter whose type is a ref cursor returning a record type (both explicitly defined). The content of the ref cursor is gathered using a complex query which I guess runs O(n) depending on the number of records in a table.

The idea is to paginate the result in the server because getting all the data causes a long delay (500 records take about 40-50 seconds due to the calculation and the join resolution). I've already rebuilt the query using row_number()

open out_rcvar for
  SELECT *
    FROM ( select a, b, c,..., row_number() over (order by f, g) rn
            from t1, t2,...
            where some_conditions
    ) where rn between initial_row and final_row
    order by rn;

in order to avoid the offset-limit approach (and its equivalence in oracle). But, here's the catch, the user wants a pagination menu like

[first || <<5previous || 1 2 3 4 5 || next5>> || last ]

and knowing the total rows implies counting (hence, "querying") the whole package and taking the whole 50secs. What approach could I use here?

Thanks in advance for your help.

EDIT: The long query should not be setted a s a materialized view because the data in the records is required to be updated as it is requested (the web app does some operations with the data and needs to know if the selected item is "available" or "sold")

possible duplicate of [ResultSet to Pagination](http://stackoverflow.com/questions/1986998/resultset-to-pagination) — Brian Roach, Nov 10 '11 at 17:45
Don't agree to the duplicate assumption as this question does not relate to the pagination itself (which parameters should use and so) but to server pagination of data which is expensive to retrieve — Alfabravo, Nov 10 '11 at 18:00

score 0 · Answer 1 · answered Nov 10 '11 at 17:46

0

What is the performance if you do not select any columns but just a count to determine the rows? Is that acceptable?

And use that as a guide to build the pagination.

Otherwise we have no option without knowing the count to build the number of pages as (1,2,3,45)

The other option is to not show the number of pages, but just show next and previous.

Just my thoughts.

answered Nov 10 '11 at 17:46

r0ast3d

2,639
1
14
18

I used explain to get info on the cost of the queries. For certain values (a given user, some filters, etc), the paginated query has a cost of 11. The full query without pagination and just counting has a cost of 12. So, unless the queries are executed in two separated threads, it'll take +- two times what it takes with just the paginated query? – Alfabravo Nov 10 '11 at 21:52
A difficult answer without considering other ui alternatives or breaking your search condition into simpler search via ui or optimizing the database to increase the response time. Good luck! – r0ast3d Nov 10 '11 at 22:29

score 0 · Answer 2 · answered Nov 10 '11 at 17:50

0

Perhaps you might consider creating temporary table. You might store your results there and then use some paging mechanism. This way the computation will be done once. Then you will only select the data, which will be pretty fast.

There is one catch, in this approach. You have to ensure that you will not break session, since temporary tables are private and exists only for your session. Take a look at this link.

answered Nov 10 '11 at 17:50

Sebastian Łaskawiec

2,667
15
33

If I'm using a connection pool (DBCP for example), would there be a problem using temporary tables associated to a session? Don't know, just asking – Alfabravo Nov 10 '11 at 17:57
Yes, it is possible. You would have to ensure somehow that you will use the same connection for computation and for paging. If you are using connection pool you might need more sophisticated approach. For example create standard table for computation results with additional user (or session) ID. Then when you delete the session bean, perform DB cleanup. – Sebastian Łaskawiec Nov 10 '11 at 18:18

score 0 · Accepted Answer · answered Nov 10 '11 at 17:57

0

You could do something like:

 SELECT *
    FROM ( select  count(*),a, b, c,..., row_number() over (order by f, g) rn
            from t1, t2,...
            where some_conditions
    ) where rn between initial_row and final_row
    order by rn;

This is probably inefficient given your description, but if you find some quicker way to calculate the total rows, you could stick it the inner select, and return it with every row. It's not great, but it works and it's a single select (as opposed to having one for the total row number and a second one for the actual rows).

answered Nov 10 '11 at 17:57

Andres Olarte

4,380
3
24
45

Gonna try this and what r0ast3d said in order to have some more info available. I'll let you know – Alfabravo Nov 10 '11 at 18:02
Tried your approach and it added extra complexity as the query has some columns in the select which are alias associated to decodes and calls to other packages' procedures. And grouping them just adds more to the query... which ends in a query with cost 12 according to explain. – Alfabravo Nov 10 '11 at 22:10
Chosen this one. Works good! Didn't know that it takes the whole set before executing over(), so it counts all the records faster. Thanks – Alfabravo Nov 29 '11 at 13:54

How to deal with pagination for a large, non materializable resultset in Oracle and Java

3 Answers3