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>: > > > 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. > -- 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.
