randyd
asked on
Paging very large result sets.
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.
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.
jrb1
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.
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
void setFetchSize(int rows) throws SQLException is available in all Statement, PreparedStatement, CallableStatement, and ResultSet
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
--> 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.
ASKER
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..
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..
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.
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.