I'd try to see on the cx_oracle list if you can produce a self-contained
cx_Oracle-only test case and illustrate that indexes are not being
used...I think this comes up a lot over there it's like an OCI thing.
On 09/12/2016 03:20 PM, Thierry Florac wrote:
Hi Mike,
I really agrea with you on all the points you mentionned. And I won't
add anything about the cost of an Oracle database license and
administrators compared with their performances! But I actually don't
have the choice... :-/
My problem is clearly a database problem at first! But as my DBA can't
find any solution to it, I'm just trying to find a bypass on the ORM side.
I definitively agree with SA ORM's way of handling all this. I was quite
sure in fact that there was no easy way to bypass the ORM...
Thanks anyway,
Thierry
2016-09-12 20:53 GMT+02:00 Mike Bayer <mike...@zzzcomputing.com
<mailto:mike...@zzzcomputing.com>>:
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%2bunsubscr...@googlegroups.com>
<mailto:sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy%2bunsubscr...@googlegroups.com>>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>>.
Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<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
<mailto:sqlalchemy%2bunsubscr...@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
<https://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
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.
