Paging very large result sets.

Posted on 2005-04-26
Last Modified: 2011-08-18
Hey experts!

I need to page extremely large result sets through to a java client.  if my select statement returns say millions of rows, how can i get teh first set back to the gui without blowing up the client with out of memory errors...

TOAD seems to do this by pagin the results on the server - maybe using snapshots?  can i access the snapshot directly (in 9.2) or is there some other magic i should explore?

thanks for pointers.
Question by:randyd
    LVL 25

    Expert Comment

    Normally what you do (and what TOAD is doing) is open a cursor and then fetch a certain number of rows--and display them.  TOAD keeps the CURSOR open, and if you page through the results, it fetches more rows and adds them to the display.  Depending on your program, you may be able to do that as well.
    LVL 22

    Expert Comment

    by:earth man2
    By default Oracle JDBC has a FetchSize of TEN rows.  That should not break your java program.

    void setFetchSize(int rows) throws SQLException is available in all Statement, PreparedStatement, CallableStatement, and ResultSet
    LVL 3

    Author Comment

    The Ten at a time is not a problem... i just cant expect the client to have enough ram to hold the entire set at once.  but i still want to be able to scroll around the result set in a list for instance...

    there is a cursor that lets you move to PREVIOUS as well as NEXT values.  this is what i need to do i think.  I know this is available in OCI calls and I believe it is available in JDBC2.0 ...   to make my GUI work (nicely) i think i need to know the number of records the cursor contains... but i dont think i will until i page through to the end...  

    any comments on an approach here?
    LVL 47

    Accepted Solution

    This seems to be a design error.
    "if my select statement returns say millions of rows".
    This is bad design. By no means your select statement should return
    "if my select statement returns say millions of rows".

    This is not possible for frontend application - the only reader of these rows
    is a human being. And no numan being can observe, read and accept milions of rows.

    Also the load on the server, application server and net channel is to high.

    So speak with the designer and project lead and sort out why you have
    to select milions of rows.
    LVL 7

    Assisted Solution

    You will get the out of memory errors if you attempt to cache all of the results!  You will at some level even if you use scrollable cursors with Oracle.  See In particular read the section "Because the underlying server does not support scrollable cursors, Oracle JDBC must implement scrollability in a separate layer."

    As to the solution, I would recommend looking into something like Hibernate that has quite a good ability at handling paging using a technique similar to below.

    Anyway, to actually implement paging efficiently in Oracle, it would be preferable to re-call the query each new page the client views (as schwertner pointed out users rarely ever go past the first page if there are too many results, but sometimes they do).  So, in relation to that, here's how to wrap you query using pagination, but there is one slight problem, you cannot guarantee that no-one will change the underlying data between calls and possibly miss entries with each call.

    Required parameters: page & no_per_page, obviously should have some constraints set to ensure page >= 1 and no_per_page >= 1.

    SELECT t2.object_name
    FROM   (
            SELECT t1.object_name,
                   rownum AS row_num
            FROM   (
                    -- This is where your actual query will go...
                    SELECT object_name
                    FROM   user_objects
                    ORDER by object_name
                   ) t1
            WHERE  rownum <= (:page * :no_per_page)
           ) t2
    WHERE  row_num > ((:page - 1) * :no_per_page);
    LVL 4

    Assisted Solution

    Just to add to grim_toaster answer and your assumption: make my GUI work (nicely) i think i need to know the number of records the cursor contains... but i dont think i will until i page through to the end...  

    yes, it is absolutely sure that you can not retrieve the number of all records, without actually read them, which compromises the paging.
    So, you have two approaches:
    1. Either forget about total number of records (pages respectively) and provide "Previous" and "Next" buttons only, or
    2. Get the total number of pages and make it very slowly, especially the first time, when you have to execute
    select count(1) from  ... at least

    I prefer approach 1, but very often some Product Managers promises the 2nd to the customers.

    something about:

    >>>but there is one slight problem, you cannot guarantee that no-one will change the underlying data between calls and possibly miss entries with each call.
    Actually the "paging" query avoids this problem.
    Imagine you are at the 2nd page. Somebody inserts a record which gois in the 1st page. when you click previous, you will start counting from the 1st record and will get exactly the first no_per_page records, including the new one....and so on.

    LVL 7

    Expert Comment

    Two points to that...

    --> Get the total number of pages and make it very slowly, especially the first time...
    Not necessarily, you can do as Google and many other paging operations do, and only see if there are at least X number of pages left, for example:

    SELECT CEIL(count(*) / :no_per_page) AS remaining_pages, count(*) FROM (
    SELECT rownum AS row_num
    FROM   all_objects
    WHERE  rownum <= (:page * :no_per_page) + (:no_per_page * :max_no_pages)
    ) WHERE row_num > (:page * :no_per_page)

    Seeing as you will only be able to show the user that there are X number of available pages, it makes sense to only see if there are those X number of pages left.  And then you can stretch the available pages with each different page viewed.

    -->Actually the "paging" query avoids this problem.
    I was thinking more of if you were viewing page 2, someone deleted an entry on page 1, you then go to page 3 and you will not see what would have otherwise been the first entry on page 3.  To be honest, no-one would really notice, but it's just one of the things that needs to be considered.
    LVL 3

    Author Comment

    hey thanks guys for the lively discussion :)

    schwertner - i realize that it is not practical to return mega-result sets - not usable - not digestable etc...  but unfortunately, I, like TOAD and other apps, am not able (or willing) to restrict the SQL that the user may attempt to execute on the system.  I must beleive that they will say select * from somewhere that has too many rows - whether people do this becuase they are lazy and dont want to think about restricting with a where clause - or dont know the DESC command so they want to see columns, or they simply dont know that there will be a million rows in that pile..  not sure.  but in the end it doesn't amtter - they will issue that query and i need to handle it gracefully... (i don't call that a design error)

    grim_toaster - exactly! the out of memory is a killer - and super-tricky (impossible?) to predict it is about to happen - so can't be avoided - and as an added benefit, app behavior stinks exceptions aren't thrown well etc.  

    plamen73 - i thought about the select count(*) from () method - if that runs in a background thread while the first rows are being returned then the GUI might be ok...

    I was thinking that read-consistent view was important - i might have to give that up in favor of making this thing work...  you might guess - but my other wrinkle is i do not know the SQL statements ahead of time and i don't want to get into parsing random SQL and inserting the paging stuff in the middle...

    thanks so far... still noodling..
    LVL 7

    Expert Comment

    As a question for you, how is the SQL provided to you?  

    If you are actually passed a select statement to execute, where are you executing it?  In a stored procedure via dbms_sql of nds or on the java side via a prepared statement?  Although I must admit I don't like the sounds of it, so many security holes etc.  But if it is a requirement...

    Anyway, if I can attempt to persuade you to use hibernate (which has paging support already provided) to send your custom SQL to would be the simplest way, or if you can download the code and look at the Oracle9Dialect class to give you some ideas as to how to do this.

    But in a nutshell, the only way to do what you are after (from what I understand) is to wrap whatever query they provide with the pagination code, thankfully it can fully wrap the SQL passed (see above example) and just ignore the extra row_num column that would be provided.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now