You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
The current SQL statement for retrieving all foreign keys in Oracle, as provided in FOREIGN_KEYS.sql, may lead to performance issues and errors in certain scenarios. For instance, in our database, the current implementation resulted in an "ORA-01652: unable to extend temp segment" error after more than 40 minutes of execution time.
Proposed Solution
I propose optimizing the existing SQL statement using explicit JOIN statements. Below is a modified version of the SQL statement that significantly improves performance, reducing the execution time to 4 seconds in our testing environment:
SELECTNULLAS PKTABLE_CAT,
P.OWNERAS PKTABLE_SCHEM,
P.TABLE_NAMEAS PKTABLE_NAME,
PC.COLUMN_NAMEAS PKCOLUMN_NAME,
NULLAS FKTABLE_CAT,
F.OWNERAS FKTABLE_SCHEM,
F.TABLE_NAMEAS FKTABLE_NAME,
FC.COLUMN_NAMEAS FKCOLUMN_NAME,
FC.POSITIONAS KEY_SEQ,
NULLAS UPDATE_RULE,
DECODE(F.DELETE_RULE, 'CASCADE', 0, 'SET NULL', 2, 1) AS DELETE_RULE,
F.CONSTRAINT_NAMEAS FK_NAME,
P.CONSTRAINT_NAMEAS PK_NAME,
DECODE(F.DEFERRABLE, 'DEFERRABLE', 5, 'NOT DEFERRABLE', 7, 'DEFERRED', 6) AS DEFERRABILITY
FROM
ALL_CONSTRAINTS P
JOIN
ALL_USERS USERS ONP.OWNER=USERS.USERNAMEJOIN
ALL_CONSTRAINTS F ONP.OWNER=F.R_OWNERANDF.CONSTRAINT_TYPE='R'ANDP.CONSTRAINT_NAME=F.R_CONSTRAINT_NAMEJOIN
ALL_CONS_COLUMNS PC ONPC.OWNER=P.OWNERANDPC.CONSTRAINT_NAME=P.CONSTRAINT_NAMEANDPC.TABLE_NAME=P.TABLE_NAMEJOIN
ALL_CONS_COLUMNS FC ONFC.OWNER=F.OWNERANDFC.CONSTRAINT_NAME=F.CONSTRAINT_NAMEANDFC.TABLE_NAME=F.TABLE_NAMEANDFC.POSITION=PC.POSITIONWHERE1=1ANDUSERS.ORACLE_MAINTAINED='N'AND NOT REGEXP_LIKE(USERS.USERNAME, '^APEX_[0-9]{6}$')
AND NOT REGEXP_LIKE(USERS.USERNAME, '^FLOWS_[0-9]{5}$')
AND REGEXP_LIKE(P.OWNER, 'DM_CORE999')
ANDP.CONSTRAINT_TYPEIN ('P', 'U')
ORDER BY
PKTABLE_SCHEM,
PKTABLE_NAME,
KEY_SEQ;
Possible Alternatives
No response
Additional Context
No response
The text was updated successfully, but these errors were encountered:
@timo-brandes Thanks for reporting this, and testing the improved query. I have fixed the code in SchemaCrawler, and will release it with the next release.
Feature Request
Problem Description
The current SQL statement for retrieving all foreign keys in Oracle, as provided in FOREIGN_KEYS.sql, may lead to performance issues and errors in certain scenarios. For instance, in our database, the current implementation resulted in an "ORA-01652: unable to extend temp segment" error after more than 40 minutes of execution time.
Proposed Solution
I propose optimizing the existing SQL statement using explicit JOIN statements. Below is a modified version of the SQL statement that significantly improves performance, reducing the execution time to 4 seconds in our testing environment:
Possible Alternatives
No response
Additional Context
No response
The text was updated successfully, but these errors were encountered: