FWIW, this sounds similar to the problems you and I had (separately) a couple of years ago: https://groups.google.com/forum/#!topic/sqlalchemy/kv7BqWZr9KQ https://groups.google.com/forum/#!topic/sqlalchemy/k_9ZGI-e85E
On Monday, September 12, 2016 at 5:39:37 PM UTC-4, Thierry Florac wrote: > > I don't think it's so easy to produce a test case... > Generaly speaking, indexes are used in a normal way! > There's only in a single case, for a single table, that they are not used > even for common operations. Maybe it's a statistics problem or something > else I don't understand; and AFAIK the problem didn't occured at start > while table was containing fewer records... > > 2016-09-12 22:00 GMT+02:00 Mike Bayer <mik...@zzzcomputing.com > <javascript:>>: > >> 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 <mik...@zzzcomputing.com >>> <javascript:> >>> <mailto:mik...@zzzcomputing.com <javascript:>>>: >>> >>> >>> >>> >>> 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+...@googlegroups.com <javascript:> >>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com <javascript:>> >>> <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:> >>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com <javascript:> >>> >>. >>> To post to this group, send email to sqlal...@googlegroups.com >>> <javascript:> >>> <mailto:sqlal...@googlegroups.com <javascript:>> >>> <mailto:sqlal...@googlegroups.com <javascript:> >>> <mailto:sqlal...@googlegroups.com <javascript:>>>. >>> 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+...@googlegroups.com <javascript:> >>> <mailto:sqlalchemy%2bunsubscr...@googlegroups.com <javascript:>>. >>> To post to this group, send email to sqlal...@googlegroups.com >>> <javascript:> >>> <mailto:sqlal...@googlegroups.com <javascript:>>. >>> 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+...@googlegroups.com <javascript:> >>> <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. >>> To post to this group, send email to sqlal...@googlegroups.com >>> <javascript:> >>> <mailto:sqlal...@googlegroups.com <javascript:>>. >>> 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+...@googlegroups.com <javascript:>. >> To post to this group, send email to sqlal...@googlegroups.com >> <javascript:>. >> Visit this group at https://groups.google.com/group/sqlalchemy. >> For more options, visit 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. 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.
