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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
