Re: SDriverOracle OffesetStrategy

Yes, I also found this :

http://stackoverflow.com/questions/595123/is-there-an-ansi-sql-alternative-to-the-mysql-limit-keyword

Oracle seems to be one of the worse database regarding this issue.
But the rownum path is not easy in Simpleorm, as it won't fit in the sql
structure that we rely on...


Le vendredi 01 octobre 2010 à 15:01 +0200, Noel Grandin a écrit :
>   
> these 2 links have some solutions:
> 
> http://www.club-oracle.com/forums/limit-clause-for-oracle-sql-t637/
> http://www.delphifaq.com/faq/databases/oracle/f594.shtml
> 
> the basic syntax looks like this:
> 
>   select * 
>   from ( 
>     select rownum rnum, a.* 
>     from (your_query) a 
>     where rownum <= :M ) 
>  where rnum >= :N; 
> 
> in order to get rows n through m from 'your query.'
> 
> -- Noel.
> 
> Franck Routier wrote: 
> > 
> >   
> > Seems I spoke too quickly. Oracle does not seem to support the ansi
> > (2008) "OFFSET x FETCH y ROWS" syntax, nor any proprietary syntax to
> > do
> > that...
> > 
> > Le vendredi 01 octobre 2010 à 13:55 +0200, Franck Routier a écrit :
> > > 
> > > Hi,
> > > 
> > > I am bitten by an oom exception using offset/limit with
> > SDriverOracle.
> > > The driver uses SDriver.OffsetStrategy.JDBC. The was done assuming
> > > (from
> > > various reading but without any evidence) that the Oracle Driver
> > was
> > > smart enought to use a cursor on the server side to handle this.
> > > 
> > > But I definitely get an oom caused by OracleResultCacheImpl eating
> > all
> > > memory.
> > > 
> > > So I searched again and found this, on
> > >
> > http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/resltset.htm#CIHCHBJB
> > > 
> > > "Oracle JDBC Implementation for Result Set Scrollability
> > > 
> > > Because the underlying server does not support scrollable cursors,
> > > (sic!) Oracle JDBC must implement scrollability in a separate
> > layer.
> > > 
> > > It is important to be aware that this is accomplished by using a
> > > client-side memory cache to store rows of a scrollable result set.
> > > 
> > > Important:
> > > Because all rows of any scrollable result set are stored in the
> > > client-side cache, a situation where the result set contains many
> > > rows,
> > > many columns, or very large columns might cause the client-side
> > Java
> > > Virtual Machine (JVM) to fail. Do not specify scrollability for a
> > > large
> > > result set."
> > > 
> > > So it seems that Oracle is not any smarter than Postgresql is, and
> > > that
> > > resorting to a limit clause in the sql
> > (SDriver.OffsetStrategy.QUERY)
> > > would be a better solution for Oracle as well.
> > > 
> > > So I propose to change the SDriverOracle to implement this
> > strategy.
> > > Does it sound ok ?
> > > 
> > > Franck
> > > 
> > > 
> > > 
> > > 
> > > 
> > 
> > -- 
> > Franck Routier 
> > Logo Axège
> > Axège
> > 23,rue Saint-Simon
> > 63000 Clermont-Ferrand
> > Tél: +33 (0)4 63 05 95 40
> > Fax: +33 (0)4 73 70 65 29
> > Email: [email protected]
> > 
> > 
> > 
> > 
> 
> 
> 
> 
> ______________________________________________________________________
> Disclaimer: http://www.peralex.com/disclaimer.html
> 
> 
> 
> 
> 

-- 
Franck Routier 
             Logo Axège
Axège
23,rue Saint-Simon
63000 Clermont-Ferrand
Tél: +33 (0)4 63 05 95 40
Fax: +33 (0)4 73 70 65 29
Email: [email protected]








------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/SimpleORM/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/SimpleORM/join
    (Yahoo! ID required)

<*> To change settings via email:
    [email protected] 
    [email protected]

<*> To unsubscribe from this group, send an email to:
    [email protected]

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/