 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / General / August 2006Limiting Large Result Sets
Hi, I would be glad if someone could provide solution for this problem. working ENV: Jdeveloper 10.1.3 - Development Oracle 9i - Database Windows XP - OS I want to retrieve about 30 rows at a time . The table contains over 2 million rows. I would like to begin at the first row and drag 30 rows over the network. Then get the next 30 and repeat the process untill all rows are displayed. I made 2 approaches for this: 1) cache based 2) query based Using cache based approach, i was running out of memory error because all millions rows cannt be cached. Thats obvious. This kind of approach was working good for 10,000s of rows. Using query based approach, i was able to retreive all the million rows by limiting the query. I run the query everytime to get the rows. This is working absolutely fine but performance is not really good. And this kind of approach is not acceptable at my work place. So, i need to do work with resultset itself. I tried to set max rows on statement using: prepareStatment.setMaxRows(10); resultset = prepareStatment.executeQuery(); Here, i can obtain first 10 rows but if i need to get from 11- 20 and 20-30 rows and so on.......how do i achieve it???? Any other suggestions are also acceptible. Looking forward for the reply. Thanks. > Hi, > [quoted text clipped - 35 lines] > > Thanks. there are a number of ways. do your self a big favour and ensure you download 10G release 2 drivers 10.2.0.1.0 jdbc http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_1020 1.html you need ojdbc14.jar possibly orai18n.jar 1. by setting up the driver correctly. 2. by reading the docs. http://download-east.oracle.com/docs/cd/B14117_01/java.101/b10979/toc.htm specifically section 17 17.5 Fetch Size By default, when Oracle JDBC executes a query, it receives the result set 10 rows at a time from the database cursor. This is the default Oracle row-prefetch value. You can change the number of rows retrieved with each trip to the database cursor by changing the row-prefetch value (see "Oracle Row Prefetching" for more information). JDBC 2.0 also allows you to specify the number of rows fetched with each database round trip for a query, and this number is referred to as the fetch size. In Oracle JDBC, the row-prefetch value is used as the default fetch size in a statement object. Setting the fetch size overrides the row-prefetch setting and affects subsequent queries executed through that statement object. Fetch size is also used in a result set. When the statement object executes a query, the fetch size of the statement object is passed to the result set object produced by the query. However, you can also set the fetch size in the result set object to override the statement fetch size that was passed to it. (Also note that changes made to a statement object's fetch size after a result set is produced will have no affect on that result set.) The result set fetch size, either set explicitly, or by default equal to the statement fetch size that was passed to it, determines the number of rows that are retrieved in any subsequent trips to the database for that result set. This includes any trips that are still required to complete the original query, as well as any refetching of data into the result set. (Data can be refetched, either explicitly or implicitly, to update a scroll-sensitive or scroll-insensitive/updatable result set. See "Refetching Rows".) Steve Oracle supports SQL LIMIT command: LIMIT <start_row>, <rows_to_return> > Oracle supports SQL LIMIT command: > > LIMIT <start_row>, <rows_to_return> Not my Oracle 9i. For many year rownum has been the answer. Arne >> Oracle supports SQL LIMIT command: >> [quoted text clipped - 5 lines] > > Arne no it has not!! READ the requirement. rownum is for returning a MAXIMUM number of records related to a query. normally for processing , it does not maintain any state or positional information. it is not for allowing a select of say 1,000,000 records and returning X number at a time. steve > On Wed, 30 Aug 2006 11:04:28 +0800, Arne Vajhøj wrote >> For many year rownum has been the answer. [quoted text clipped - 8 lines] > it is not for allowing a select of say 1,000,000 records and returning X > number at a time. http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html browse down to pagination. Arne > working ENV: > Jdeveloper 10.1.3 - Development [quoted text clipped - 5 lines] > over the network. Then get the next 30 and repeat the process untill > all rows are displayed. > Any other suggestions are also acceptible. Try look at rownum ! http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html Arne Thanks for every ones replies to make the question more clear this what i wanted from the code I have a method which can accept a SQL query as input parameter and number of results the user wants public RowSetDynaClass getResults(String sql,int resultNumber) { prepareStatment= conn.prepareStatement(sql.toString()), ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY); prepareStatment.setMaxRows(resultNumber); resultset = prepareStatment.executeQuery(); rowsdc = new RowSetDynaClass(resultset); } So the user gets back a result for the first 10 rows, now the user again requests another set of 10 rows. This method is again called and here is the real problem starts he now wants to get results from 11-20. Simply put the question is how to maintain the state of the results with out modifying the query the user inputs?? > Thanks for every ones replies to make the question more clear this > what i wanted from the code [quoted text clipped - 4 lines] > { > prepareStatment= conn.prepareStatement(sql.toString()), Why are you calling String.toString() ? > ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY); > prepareStatment.setMaxRows(resultNumber); Wouldn't a better name for the second parameter be "maxRows"? > resultset = prepareStatment.executeQuery(); > rowsdc = new RowSetDynaClass(resultset); > > } I noticed you don't actually return anything from this method. > So the user gets back a result for the first 10 rows, now the user > again requests another set of 10 rows. This method is again called and > here is the real problem starts he now wants to get results from 11-20. > Simply put the question is how to maintain the state of the results > with out modifying the query the user inputs?? How about creating an object which maintains its own state and returning that? - Oliver Thanks Oliver its just snippet yes I do return the object ...I did not really get what you mean when you say "object that does its own session management" can you please elaborate > Thanks Oliver its just snippet yes I do return the object ...I did not > really get what you mean when you say "object that does its own session > management" can you please elaborate Return an object which provides an API for the client to read 10 rows at a time. This object will keep track of where the client currently is in the overall result set. This is similar to the behaviour of the object returned by the List.iterator(). - Oliver >> Thanks Oliver its just snippet yes I do return the object ...I did not >> really get what you mean when you say "object that does its own session [quoted text clipped - 8 lines] > > - Oliver there is no need for this complexity, it is all built in. all that it requires is for the poser to actually READ the oracle documentation. it is not as if he is the only person to want to do this, there are thousands of clients with this requirement, and it is all controllable from the JDBC driver. steve Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.