There's a known behavior in oracle that some indexes fail to function against Unicode strings, the solution may involve special casts or some such but we would need to research this.
Please post an issue with as much as you can learn about this issue (including how to workaround) at https://bitbucket.org/zzzeek/sqlalchemy/issues/, thanks ! On Thu, Jun 21, 2018, 10:15 AM Vinit Shah <elysiums.sh...@gmail.com> wrote: > I've posted this on stackoverflow > <https://stackoverflow.com/questions/50927998/sqlalchemy-oracle-get-constraint-data-method-hanging-with-unicode-table-names>, > but I may have more luck here. > > I'm using SqlAlchemy's autoload feature to load up table data. Recently, > after an upgrade to Oracle 12c, I found that the table loading would not > complete for 30+ minutes (normally it's a few seconds). > > The underlying issue appears to have something to do with using a unicode > string as the schema name as opposed to a standard ascii string. > The method call that has the slowness is the '_get_constraint_data' call > (on the fetchall() line). I've placed the query it's using below. Passing > in a unicode string for the 'owner' parameter will stall the query while a > regular string will not. It looks like the table that doesn't like the > unicode string is the 'all_cons_columns' table, removing it speeds up the > execution. > > > SELECT > ac.constraint_name, > ac.constraint_type, > loc.column_name AS local_column, > rem.table_name AS remote_table, > rem.column_name AS remote_column, > rem.owner AS remote_owner, > loc.position as loc_pos, > rem.position as rem_pos, > ac.search_condition, > ac.delete_rule > FROM all_constraints ac, > all_cons_columns loc, > all_cons_columns rem > WHERE ac.table_name = :table_name > AND ac.constraint_type IN ('R','P', 'U', 'C') > AND ac.owner = :owner > AND ac.owner = loc.owner > AND ac.constraint_name = loc.constraint_name > AND ac.r_owner = rem.owner(+) > AND ac.r_constraint_name = rem.constraint_name(+) > AND (rem.position IS NULL or loc.position=rem.position) > ORDER BY ac.constraint_name, loc.position > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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.
