Re: SDriverOracle OffesetStrategy

Hello Franck,

It amazes me that Oracle does not support scrollable cursors in the database, but there you are.  Could you please add a short comment to the change explaining why you did it.

There never has been a standard way to do this in traditional SQL.   The Oracle ROWNUM is actually more general than than Limit/Offset -- eg. you could retrieve every 10th row for sampling, or use it to detect duplicate rows, pick the "best" etc.  If someone is interested it would not be that hard to wrap our queries with SELECT * FROM <original query> WHERE ROWNUM.... However, it would not be trivial.

SQL 2003 implements a very magic CURRENT_ROW() function which is supported by Oracle and MSSQL, but not much else that I can tell.  I would avoid it as it is complex and therefore more likely to have issues.  (It is amazing that the incredibly complex SQL 92 & 99 did not address these very basic issues!)

(Before MSSQL introduced CURRENT_ROW it was just about impossible to get at any row number, eg. to remove duplicate rows.)

Thanks for the fix,

Anthony

At 02:21 AM 2/10/2010, Franck Routier wrote:
>  
>
>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>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.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>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: <mailto:franck.routier%40axege.com>[email protected]
>> > > 
>> > > 
>> > > 
>> > > 
>> > 
>> > 
>> > 
>> > 
>> > __________________________________________________________
>> > Disclaimer: <http://www.peralex.com/disclaimer.html>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: <mailto:franck.routier%40axege.com>[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: <mailto:franck.routier%40axege.com>[email protected]
>
>


Spreadsheet Detective,
Southern Cross Software Queensland Pty Limited
54 Gerler Street
Bardon, Queensland 4065, Australia.

Email: [email protected]
www.SpreadsheetDetective.com
Ph: +61 427 830248 (Australian Eastern Standard Time)

"If the model seems correct only because the numbers look right, 
then why build the model in the first place?"



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

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/