Re: SDriverOracle OffesetStrategy

I have set SDriverOracle offsetStrategy to SCAN, ie let Simpleorm drop
records we want to skip. Anyway, this is already what Oracle driver is
doing (but instead of dropping the records, it keeps them in a cache
that eats all memory...).

At least, this will always work.

Franck


Le vendredi 01 octobre 2010 à 15:42 +0200, Franck Routier a écrit :
>   
> 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]
> 
> 
> 
> 
> 

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