Re: Handling Large Resultset in Oracle

Hi Armin=0A=A0=A0=A0=A0=A0=A0=A0 This SQL query we have is not a data only =
from one table, but is a very big SQL query looks like following:=0A=A0=0As=
elect a,b,c from x,y,z where.... union all select a,b,c from x,y,z where ..=
.. union all select a,b,c=A0 from x,y,z=0A=A0=0A=A0=0AThis is a generic SQL=
 written=A0 but the our SQL looks is on similar lines.=0A=A0=0A=A0=0ADo you=
r solutions mentioned of report queries will solve=A0 this kind of issues ?=
 =0A=A0=0A=A0=0AWe might think of 1.5 , depending upon what you say.=0A=A0=
=0AThanks and Regards,=0ASomendra Paul.=0A=A0=0A=0A=A0=0A=0A=0A----- Origin=
al Message ----=0AFrom: Armin Waibel <[email protected]>=0ATo: OJB Users Li=
st <[email protected]>=0ASent: Sunday, September 21, 2008 9:05:06 PM=
=0ASubject: Re: Handling Large Resultset in Oracle=0A=0AHi Paul,=0A=0ASomen=
dra Paul wrote:=0A> Hi All, I am using OJB1.4 against Oracle10g with classe=
s12.jar. We=0A> are trying to export data from the DB, using SQL query whic=
h returns=0A> 600000 records , what we found out that is when we do iterato=
r =3D=0A> query.getIteratorByQuery(),and iterate over the results,=A0 we fi=
nd=0A> that after iterating over 300000 records, the VM grows rapidly and=
=0A> the entire program crashes giving OOM errors, where as when we used=0A=
> simple JDBC program to implement it, we saw that the entire 600K=0A> reco=
rds were extracted using only 160mb of memory , and the ojb=0A> execution t=
akes more than 1.5gb to execute before crashing.=0A> =0A> Do we know how to=
 solve this memory issue when executing large=0A> resultset in Oracle.=0A> =
=0A> One solution that in the OJB archives for ProgressSQL is that to use=
=0A> fetchSize=3D<somevalue> , it will solve this issue ???=0A> =0A=0AThe p=
roblem could be the cache. Dependent on the used cache OJB keep all =0Amate=
rialized objects (or copies of these objects) in memory. Most cache =0Aimpl=
ementations use soft-references (so OOM errors shouldn't occur) but =0Amayb=
e your objects have complex relationships or your layer holds =0Ahard-refer=
ences to the materialized objects.=0AYou can try to evict the cache while i=
terate the result set.=0A=0AIf you don't rely on the materialized java obje=
cts you can use a report =0Aquery=0Ahttp://db.apache.org/ojb/docu/guides/qu=
ery.html#Report+Queries=0Ato iterate over the result set (returns a collect=
ion of arrays). This =0Abypass the cache and should result in a memory-use =
comparable with a =0Aplain jdbc-query.=0A=0AThe upcoming OJB 1.0.5 has enha=
nced query features and supports limit =0Aand pagination of query results (=
Oracle is supported) - 1.0.5rc1:=0Ahttp://www.mail-archive.com/ojb-user%40d=
b.apache.org/msg16078.html=0AThe query-guide of the included documentation =
show how to use this feature.=0A=0Aregards,=0AArmin=0A=0A=0A> =0A> Thanks a=
nd Regards Somendra Paul.=0A> =0A> =0A> =0A> =0A> -------------------------=
--------------------------------------------=0A>=A0 To unsubscribe, e-mail:=
 [email protected] For=0A> additional commands, e-mail: oj=
[email protected]=0A> =0A> =0A=0A----------------------------------=
-----------------------------------=0ATo unsubscribe, e-mail: ojb-user-unsu=
[email protected]=0AFor additional commands, e-mail: [email protected]=
pache.org=0A=0A=0A