Hi,
we are about upgrading our sqlalchemy library from 0.4.8 to something
newer and during this process we have detected that the LIMIT/OFFSET
support for oracle has been changed, from using “ROW NUMBER OVER...”
to a wrapped subquery approach in conjunction with ROWNUM as described
in
http://www.sqlalchemy.org/docs/reference/dialects/oracle.html#limit-offset-support
Unfortunately this approch is about 10 times slower for large tables
which is mainly related to the fact that the innermost subquery has to
sort the entire table with a plain 'order by'.
Interestingly the
ROW_NUMBER() OVER (ORDER BY some db fields)
is so much more efficient than the normal order by approach.
I don't know the reasons behind the decision for getting rid of the
"row number over" approach, but could it make sense to reimplement
this algorithm again into the current SA as an optional feature?
Any help/feedback is very appreciated.
Thanks,
Ralph
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.