On 09/12/2016 09:11 AM, Thierry Florac wrote:
Hi,
I'm working with an Oracle database which, for a reason I can't
understand, doesn't want to use indexes when working on a given table
(which is quite large) with prepared statements!
I know I can use literal columns when running "select" queries (using
the "literal_column" function), so that selects performances are good,
but is there a way to use such literals while performing updates ou
deletes through the ORM using sessions?
Three general stanzas on this:
If we are talking about the UPDATE / DELETE statements within the ORM
flush(), that would be extremely hard to trick the ORM into doing that
correctly; getting the bound parameters to render as literals would be
doable but the ORM really wants to batch those statements together,
which would have to also be turned off and there's no public entryway to
that.
Next, brute force via the do_executemany() / do_execute() dialect events
which give you direct access to the cx_Oracle cursor. You'd need to
break the list of parameters sent to executemany() into multiple single
execute() calls and rewrite all the parameters. This will get you the
effect you want at the expense of much ugliness.
Third. The ORM's UPDATE/DELETE statements only use the primary key in
the WHERE clause, not any other columns. if your Oracle DB can't even
index by primary key on prepared statement that is really something that
is likely fixable on the database side. Your employer (or yourself, if
this is your own company) is likely paying hundreds of thousands of $$
per year to be able to use Oracle in the first place, and if your Oracle
DB can't look up a record by primary key using the index, something is
seriously broken with it. Oracle is pretty bad in this area but this
one should not be the case; I'd advise at least checking on the
cx_Oracle list why your Oracle DB is bypassing indexes for prepared
statements.
Best regards,
Thierry
--
http://www.imagesdusport.com -- http://www.ztfy.org
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
