Re: SDriverOracle OffesetStrategy

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]








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

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/