Re: Limiting Large Result Sets
- From: steve <steve@xxxxxxx>
- Date: Wed, 30 Aug 2006 06:39:38 +0800
On Wed, 30 Aug 2006 06:04:18 +0800, smartnhandsome@xxxxxxxxx wrote
(in article <1156889058.520922.275610@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>):
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.
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
.
- Follow-Ups:
- Re: Limiting Large Result Sets
- From: Tom Cole
- Re: Limiting Large Result Sets
- References:
- Limiting Large Result Sets
- From: smartnhandsome
- Limiting Large Result Sets
- Prev by Date: Re: Can we display list of held monitors and their holders in JVM?
- Next by Date: Re: how do you call a c library in a web services?
- Previous by thread: Limiting Large Result Sets
- Next by thread: Re: Limiting Large Result Sets
- Index(es):
Relevant Pages
|
Loading
