Limiting Large Result Sets

[smartnhandsome]

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.

 

[steve]

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

 

[Tom Cole]

Oracle supports SQL LIMIT command:

LIMIT <start_row>, <rows_to_return>

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

Oracle supports SQL LIMIT command:

LIMIT <start_row>, <rows_to_return>

Not my Oracle 9i.

For many year rownum has been the answer.

Arne

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

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.

Any other suggestions are also acceptible.

Try look at rownum !

http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

Arne

 

[smartnhandsome]

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??

 

[Oliver Wong]

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()),

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

 

[smartnhandsome]

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

 

[Oliver Wong]

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

 

[steve]

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

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

 

[steve]

Not my Oracle 9i.

For many year rownum has been the answer.

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

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

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.

http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

browse down to pagination.

Arne