Optimize Oracle Foreign Key Retrieval SQL Statement

[tibramo]

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:

SELECT
    NULL AS PKTABLE_CAT,
    P.OWNER AS PKTABLE_SCHEM,
    P.TABLE_NAME AS PKTABLE_NAME,
    PC.COLUMN_NAME AS PKCOLUMN_NAME,
    NULL AS FKTABLE_CAT,
    F.OWNER AS FKTABLE_SCHEM,
    F.TABLE_NAME AS FKTABLE_NAME,
    FC.COLUMN_NAME AS FKCOLUMN_NAME,
    FC.POSITION AS KEY_SEQ,
    NULL AS UPDATE_RULE,
    DECODE(F.DELETE_RULE, 'CASCADE', 0, 'SET NULL', 2, 1) AS DELETE_RULE,
    F.CONSTRAINT_NAME AS FK_NAME,
    P.CONSTRAINT_NAME AS PK_NAME,
    DECODE(F.DEFERRABLE, 'DEFERRABLE', 5, 'NOT DEFERRABLE', 7, 'DEFERRED', 6) AS DEFERRABILITY
FROM
    ALL_CONSTRAINTS P
        JOIN
    ALL_USERS USERS ON P.OWNER = USERS.USERNAME
        JOIN
    ALL_CONSTRAINTS F ON P.OWNER = F.R_OWNER AND F.CONSTRAINT_TYPE = 'R' AND P.CONSTRAINT_NAME = F.R_CONSTRAINT_NAME
        JOIN
    ALL_CONS_COLUMNS PC ON PC.OWNER = P.OWNER AND PC.CONSTRAINT_NAME = P.CONSTRAINT_NAME AND PC.TABLE_NAME = P.TABLE_NAME
        JOIN
    ALL_CONS_COLUMNS FC ON FC.OWNER = F.OWNER AND FC.CONSTRAINT_NAME = F.CONSTRAINT_NAME AND FC.TABLE_NAME = F.TABLE_NAME AND FC.POSITION = PC.POSITION
WHERE
  1 = 1
  AND USERS.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')
  AND P.CONSTRAINT_TYPE IN ('P', 'U')
ORDER BY
    PKTABLE_SCHEM,
    PKTABLE_NAME,
    KEY_SEQ;

Possible Alternatives

No response

Additional Context

No response

[sualeh]

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

[tibramo]

@sualeh Thank you for updating the query and your hard work on this project.

[sualeh]

@timo-brandes Please use SchemaCrawler v16.20.6, and let me know if it helps.

[tibramo]

@sualeh Thanks for the release, it is working – and fast :)

[sualeh]

@timo-brandes Thanks for the confirmation, and thanks for your query!