On Jun 23, 2010, at 3:50 AM, Ralph Heinkel wrote:
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?
A full history of this feature is here:
http://www.sqlalchemy.org/trac/ticket/536
The rationale is based on the bug described in that ticket, as well as that we
preferred to go with an approach that was recommended by a lead engineer at
Oracle.
The dialect includes an option to add the /*+ FIRST_ROWS(N) */ directive, by
specifying the optimize_limits keyword to create engine - we originally had
that in the query in all cases, until some folks chimed in that we shouldn't
make that decision by default.I don't know if that helps your use case.
The previous system can be restored using a @compiles directive. I have
documented that recipe at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/OracleRowNumberOver .
--
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.