Statement.setMaxRows

[N Naveen]

Hi,
I have a few doubts.
I have some sql queries which fetch a lot of data from a oracle DB.
I wish to use the statement object's setMaxRows() method to restrict
the number of records fetched.
I wish to know the following.
If the query fetches more than say 200 records set through the
setMaxRows method,will the truncation of the excess records happen
at the database server or the java application/app server ???
Also,will the query fetch more than the no of records set ???
(of course the query fetches more than 200 records in oracle )
Pls reply to this asap.
Thanks in advance.

[Paul Sturrock]

All thats doing is (potentially) reducing the size of the ResultSet object. It won't affect the speed of the query. setMaxRows doesn't change the actual SQL - using top/limit/rownum e.g. - so it doesn't change the work the DB does. The query will return more results than your limit if there are more results to return, then truncate them to fit your ResultSet. This is also how top/limit/rownum work anyway.

[N Naveen]

Thanks for the reply.
Where does the truncation happen ?
Does it happen at the Application/App Server
or at the database server ?

[Paul Sturrock]

Its of the ResultSet object. So it its done in your Java application.

[Rahul Babbar]

I was just curious...
Why doesn't it happen at the database level....
ie what does ResultSet stand to gain by fetching all the records and then truncating to maxRows, isn't it better that it changes the sql and modifies its rownum and gets only those number of records which are fetched...