2
\$\begingroup\$

Goal: I want to output all non-standard FK constraints from clients DB, versus our "standard" DB (which contains, by default, 2,631 FK).

Trial: I wrote this code:

SELECT owner, table_name, constraint_name, r_owner, r_constraint_name, status
FROM all_constraints
WHERE constraint_type = 'R'             -- "Referential integrity"
    AND r_constraint_name IN
    (
        SELECT constraint_name
        FROM all_constraints
        WHERE constraint_type IN ('P', 'U') -- "Primary key" or "Unique"
    )
    AND constraint_name <> 'AB_C_COST_GRP_ID'
    AND constraint_name <> 'AB_C_COST_GRP_ID'
    AND constraint_name <> 'AB_C_SRC_ID'
    -- ... 2,625 other such lines ...
    AND constraint_name <> 'ZN_BD_ID'
    AND constraint_name <> 'ZN_FR_ID'
    AND constraint_name <> 'ZN_SYST_ID'
ORDER BY table_name, constraint_name;

... with 2,631 constraints names (= the known FK in our standard DB) which I then exclude from the listing (via the WHERE clause).

NOTE -- I wrote it this way, because it's above the 1,000 items constraints of an IN clause… Anyway, the time was equivalent…

Results: It works well, but it takes 6 MIN to complete!

How to write this to get a normal performance (and eventually write the code more compactly)?

  • RDBMS is Oracle, indeed better to state it clearly.

  • Why not adding a table? Because I may not have sufficient privileges to do so; I'd like the test to be totally "transparent", not intrusive, so that I can run it on any client Oracle DB.

  • Same thing regarding indexes: I don't know if that column (of that system table or view) is indexed, but I can't do anything for it. I may just have SELECT rights granted on our clients' DB.

EXPLAIN PLAN OUTPUT:

-----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                      
| Id  | Operation                                      | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                      
-----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                      
|   0 | SELECT STATEMENT                               |                      |   182 | 91910 |  1978   (1)| 00:00:01 |                                                                                                                                                                                      
|   1 |  SORT ORDER BY                                 |                      |   182 | 91910 |  1978   (1)| 00:00:01 |                                                                                                                                                                                      
|*  2 |   FILTER                                       |                      |       |       |            |          |                                                                                                                                                                                      
|*  3 |    HASH JOIN RIGHT OUTER                       |                      |  1715 |   845K|  1977   (1)| 00:00:01 |                                                                                                                                                                                      
|   4 |     INDEX FULL SCAN                            | I_USER2              |   130 |   520 |     1   (0)| 00:00:01 |                                                                                                                                                                                      
|*  5 |     HASH JOIN                                  |                      |  1715 |   839K|  1976   (1)| 00:00:01 |                                                                                                                                                                                      
|   6 |      INDEX FULL SCAN                           | I_USER2              |   130 |  3120 |     1   (0)| 00:00:01 |                                                                                                                                                                                      
|*  7 |      HASH JOIN RIGHT OUTER                     |                      |  1715 |   798K|  1975   (1)| 00:00:01 |                                                                                                                                                                                      
|   8 |       TABLE ACCESS FULL                        | USER$                |   130 | 21710 |     4   (0)| 00:00:01 |                                                                                                                                                                                      
|*  9 |       HASH JOIN                                |                      |  1715 |   519K|  1971   (1)| 00:00:01 |                                                                                                                                                                                      
|  10 |        TABLE ACCESS FULL                       | USER$                |   130 | 21710 |     4   (0)| 00:00:01 |                                                                                                                                                                                      
|* 11 |        HASH JOIN OUTER                         |                      |  1715 |   239K|  1967   (1)| 00:00:01 |                                                                                                                                                                                      
|  12 |         NESTED LOOPS OUTER                     |                      |  1715 |   226K|  1886   (1)| 00:00:01 |                                                                                                                                                                                      
|  13 |          NESTED LOOPS                          |                      |    93 | 12369 |  1514   (1)| 00:00:01 |                                                                                                                                                                                      
|  14 |           NESTED LOOPS                         |                      |    93 | 10044 |  1421   (1)| 00:00:01 |                                                                                                                                                                                      
|* 15 |            HASH JOIN                           |                      |    93 |  5766 |  1235   (1)| 00:00:01 |                                                                                                                                                                                      
|* 16 |             HASH JOIN                          |                      |   435 | 17835 |  1125   (1)| 00:00:01 |                                                                                                                                                                                      
|  17 |              VIEW                              | VW_NSO_1             |   362 |  5792 |  1088   (1)| 00:00:01 |                                                                                                                                                                                      
|  18 |               HASH UNIQUE                      |                      |   362 |   110K|  1088   (1)| 00:00:01 |                                                                                                                                                                                      
|* 19 |                FILTER                          |                      |       |       |            |          |                                                                                                                                                                                      
|* 20 |                 HASH JOIN OUTER                |                      |  3410 |  1042K|  1087   (1)| 00:00:01 |                                                                                                                                                                                      
|  21 |                  JOIN FILTER CREATE            | :BF0000              |  3410 |  1025K|   678   (1)| 00:00:01 |                                                                                                                                                                                      
|* 22 |                   HASH JOIN                    |                      |  3410 |  1025K|   678   (1)| 00:00:01 |                                                                                                                                                                                      
|  23 |                    INDEX FULL SCAN             | I_USER2              |   130 |  3120 |     1   (0)| 00:00:01 |                                                                                                                                                                                      
|* 24 |                    HASH JOIN                   |                      |  3410 |   945K|   677   (1)| 00:00:01 |                                                                                                                                                                                      
|  25 |                     TABLE ACCESS FULL          | USER$                |   130 | 21710 |     4   (0)| 00:00:01 |                                                                                                                                                                                      
|* 26 |                     HASH JOIN                  |                      |  3410 |   389K|   673   (1)| 00:00:01 |                                                                                                                                                                                      
|* 27 |                      HASH JOIN RIGHT OUTER     |                      |  3410 |   236K|   266   (1)| 00:00:01 |                                                                                                                                                                                      
|  28 |                       INDEX FULL SCAN          | I_USER2              |   130 |   520 |     1   (0)| 00:00:01 |                                                                                                                                                                                      
|* 29 |                       HASH JOIN OUTER          |                      |  3410 |   223K|   265   (1)| 00:00:01 |                                                                                                                                                                                      
|* 30 |                        HASH JOIN RIGHT OUTER   |                      |  3410 |   196K|   184   (1)| 00:00:01 |                                                                                                                                                                                      
|  31 |                         INDEX FULL SCAN        | I_USER2              |   130 |   520 |     1   (0)| 00:00:01 |                                                                                                                                                                                      
|* 32 |                         HASH JOIN              |                      |  3410 |   183K|   183   (1)| 00:00:01 |                                                                                                                                                                                      
|* 33 |                          HASH JOIN OUTER       |                      |  3410 |    99K|   147   (1)| 00:00:01 |                                                                                                                                                                                      
|* 34 |                           TABLE ACCESS FULL    | CDEF$                |  3410 | 71610 |   111   (1)| 00:00:01 |                                                                                                                                                                                      
|  35 |                           TABLE ACCESS FULL    | CON$                 | 28840 |   253K|    36   (0)| 00:00:01 |                                                                                                                                                                                      
|  36 |                          TABLE ACCESS FULL     | CON$                 | 28840 |   704K|    36   (0)| 00:00:01 |                                                                                                                                                                                      
|  37 |                        INDEX FAST FULL SCAN    | I_OBJ1               | 96451 |   753K|    80   (0)| 00:00:01 |                                                                                                                                                                                      
|* 38 |                      TABLE ACCESS FULL         | OBJ$                 |  6797 |   305K|   408   (1)| 00:00:01 |                                                                                                                                                                                      
|  39 |                  VIEW                          | _CURRENT_EDITION_OBJ | 96115 |   469K|   409   (1)| 00:00:01 |                                                                                                                                                                                      
|* 40 |                   FILTER                       |                      |       |       |            |          |                                                                                                                                                                                      
|  41 |                    JOIN FILTER USE             | :BF0000              | 96451 |  4238K|   409   (1)| 00:00:01 |                                                                                                                                                                                      
|* 42 |                     HASH JOIN                  |                      | 96451 |  4238K|   409   (1)| 00:00:01 |                                                                                                                                                                                      
|  43 |                      INDEX FULL SCAN           | I_USER2              |   130 |  3120 |     1   (0)| 00:00:01 |                                                                                                                                                                                      
|  44 |                      TABLE ACCESS FULL         | OBJ$                 | 96451 |  1977K|   408   (1)| 00:00:01 |                                                                                                                                                                                      
|* 45 |                    TABLE ACCESS FULL           | USER_EDITIONING$     |     1 |     6 |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|  46 |                    NESTED LOOPS SEMI           |                      |     1 |    29 |     3   (0)| 00:00:01 |                                                                                                                                                                                      
|* 47 |                     INDEX SKIP SCAN            | I_USER2              |     1 |    20 |     1   (0)| 00:00:01 |                                                                                                                                                                                      
|* 48 |                     INDEX RANGE SCAN           | I_OBJ4               |     1 |     9 |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|* 49 |                    TABLE ACCESS FULL           | USER_EDITIONING$     |     1 |     6 |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|  50 |                 NESTED LOOPS SEMI              |                      |     1 |    12 |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|  51 |                  FIXED TABLE FULL              | X$KZSRO              |     2 |     6 |     0   (0)| 00:00:01 |                                                                                                                                                                                      
|* 52 |                  INDEX RANGE SCAN              | I_OBJAUTH2           |     1 |     9 |     1   (0)| 00:00:01 |                                                                                                                                                                                      
|* 53 |                 FIXED TABLE FULL               | X$KZSPR              |    23 |   161 |     0   (0)| 00:00:01 |                                                                                                                                                                                      
|* 54 |                 TABLE ACCESS FULL              | USER_EDITIONING$     |     1 |     6 |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|  55 |                 NESTED LOOPS SEMI              |                      |     1 |    29 |     3   (0)| 00:00:01 |                                                                                                                                                                                      
|* 56 |                  INDEX SKIP SCAN               | I_USER2              |     1 |    20 |     1   (0)| 00:00:01 |                                                                                                                                                                                      
|* 57 |                  INDEX RANGE SCAN              | I_OBJ4               |     1 |     9 |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|* 58 |                 TABLE ACCESS FULL              | USER_EDITIONING$     |     1 |     6 |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|  59 |              TABLE ACCESS FULL                 | CON$                 | 28840 |   704K|    36   (0)| 00:00:01 |                                                                                                                                                                                      
|* 60 |             TABLE ACCESS FULL                  | CDEF$                |  6191 |   126K|   110   (0)| 00:00:01 |                                                                                                                                                                                      
|* 61 |            TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$                 |     1 |    46 |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|* 62 |             INDEX RANGE SCAN                   | I_OBJ1               |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                      
|* 63 |           TABLE ACCESS BY INDEX ROWID          | CON$                 |     1 |    25 |     1   (0)| 00:00:01 |                                                                                                                                                                                      
|* 64 |            INDEX UNIQUE SCAN                   | I_CON2               |     1 |       |     0   (0)| 00:00:01 |                                                                                                                                                                                      
|  65 |          VIEW PUSHED PREDICATE                 | _CURRENT_EDITION_OBJ |    18 |    36 |     4   (0)| 00:00:01 |                                                                                                                                                                                      
|* 66 |           FILTER                               |                      |       |       |            |          |                                                                                                                                                                                      
|  67 |            NESTED LOOPS                        |                      | 96567 |  4243K|     4   (0)| 00:00:01 |                                                                                                                                                                                      
|  68 |             TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$                 |     1 |    21 |     3   (0)| 00:00:01 |                                                                                                                                                                                      
|* 69 |              INDEX RANGE SCAN                  | I_OBJ1               |     1 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|* 70 |             INDEX RANGE SCAN                   | I_USER2              | 96567 |  2263K|     1   (0)| 00:00:01 |                                                                                                                                                                                      
|* 71 |            TABLE ACCESS FULL                   | USER_EDITIONING$     |     1 |     6 |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|  72 |            NESTED LOOPS SEMI                   |                      |     1 |    29 |     3   (0)| 00:00:01 |                                                                                                                                                                                      
|* 73 |             INDEX SKIP SCAN                    | I_USER2              |     1 |    20 |     1   (0)| 00:00:01 |                                                                                                                                                                                      
|* 74 |             INDEX RANGE SCAN                   | I_OBJ4               |     1 |     9 |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|* 75 |            TABLE ACCESS FULL                   | USER_EDITIONING$     |     1 |     6 |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|  76 |         INDEX FAST FULL SCAN                   | I_OBJ1               | 96451 |   753K|    80   (0)| 00:00:01 |                                                                                                                                                                                      
|  77 |    NESTED LOOPS SEMI                           |                      |     1 |    12 |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|  78 |     FIXED TABLE FULL                           | X$KZSRO              |     2 |     6 |     0   (0)| 00:00:01 |                                                                                                                                                                                      
|* 79 |     INDEX RANGE SCAN                           | I_OBJAUTH2           |     1 |     9 |     1   (0)| 00:00:01 |                                                                                                                                                                                      
|* 80 |    FIXED TABLE FULL                            | X$KZSPR              |     2 |    14 |     0   (0)| 00:00:01 |                                                                                                                                                                                      
|* 81 |    TABLE ACCESS FULL                           | USER_EDITIONING$     |     1 |     6 |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|  82 |    NESTED LOOPS SEMI                           |                      |     1 |    29 |     3   (0)| 00:00:01 |                                                                                                                                                                                      
|* 83 |     INDEX SKIP SCAN                            | I_USER2              |     1 |    20 |     1   (0)| 00:00:01 |                                                                                                                                                                                      
|* 84 |     INDEX RANGE SCAN                           | I_OBJ4               |     1 |     9 |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|* 85 |    TABLE ACCESS FULL                           | USER_EDITIONING$     |     1 |     6 |     2   (0)| 00:00:01 |                                                                                                                                                                                      
-----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                      

SOLUTION

CREATE GLOBAL TEMPORARY TABLE std_temp_table
(
    constraint_name varchar2(45)
)
ON COMMIT PRESERVE ROWS;

-- Insert.
INSERT INTO std_temp_table (constraint_name)
    WITH names
    AS
    (
        SELECT 'AB_C_COST_GRP_ID' FROM dual UNION ALL
        ...
        SELECT 'ZN_FR_ID' FROM dual UNION ALL
        SELECT 'ZN_SYST_ID' FROM dual
    )
    SELECT *
    FROM names;

SELECT owner, table_name, constraint_name, r_owner, r_constraint_name, status
FROM all_constraints
WHERE constraint_type = 'R'             -- "Referential integrity"
    AND r_constraint_name IN
    (
        SELECT constraint_name
        FROM all_constraints
        WHERE constraint_type IN ('P', 'U') -- "Primary key" or "Unique"
    )
    AND owner NOT IN ('CTXSYS', 'MDSYS', 'SYS', 'SYSTEM', 'XDB')
    AND constraint_name NOT IN (SELECT constraint_name FROM std_temp_table);

takes around 6.5 seconds!!

The other variant, with a JOIN, takes around 7 seconds:

SELECT owner, table_name, ac.constraint_name, std_temp_table.constraint_name, r_owner, r_constraint_name, status
FROM all_constraints ac
FULL OUTER JOIN std_temp_table
    ON ac.constraint_name = std_temp_table.constraint_name
WHERE constraint_type = 'R'             -- "Referential integrity"
    AND r_constraint_name IN
    (
        SELECT constraint_name
        FROM all_constraints
        WHERE constraint_type IN ('P', 'U') -- "Primary key" or "Unique"
    )
    AND owner NOT IN ('CTXSYS', 'MDSYS', 'SYS', 'SYSTEM', 'XDB')
    AND std_temp_table.constraint_name IS NULL;

PS- Both must be equivalent, simply not run enough times. So the choice then comes down to a question of readability...

\$\endgroup\$
  • 2
    \$\begingroup\$ Can you please edit your question to give some more context (e.g. what is the goal of doing this), provide schemas of your table, tell us what RDBMS you're using, and give us the output of EXPLAIN SQL, or query plan, or equivalent? \$\endgroup\$ – Dannnno Sep 26 '19 at 17:08
  • \$\begingroup\$ Why not have a table with those forbidden constraint_names (with index). Also there must be an index on constraint_type. \$\endgroup\$ – Joop Eggen Sep 26 '19 at 17:16
  • \$\begingroup\$ I think that the exclusion table (like Joop mentioned), with an index on the constraint name column, is probably going to be your best bet, given what you have to work with here. Listing each exclusion constraint_name in the WHERE clause is, as you've seen, going to be slow and not reasonably maintainable. \$\endgroup\$ – DBro Sep 26 '19 at 18:46
  • 2
    \$\begingroup\$ Update: I read your reason for not wanting to use a table. In SQL Server world we would be able to create a variable of "table" datatype right in the script, add index(es) to it, fill it with data, and then utilize that elsewhere in the script just as you would any physical table. Is something similar available in Oracle? This option would allow you to take advantage of the performance improvements without needing special schema permissions. Worth giving that a shot. \$\endgroup\$ – DBro Sep 26 '19 at 22:59
2
\$\begingroup\$

The query plan seems to be too complicated because all_constraint is a view and not table. So it's difficult to derive what it's doing.

But from common sense it seems the optimal execution plan would be this:

  1. It first selects all ('P', 'U') records in the inner query
  2. For each of them finds correspondent 'R' records (with loop join)
  3. Filters and sorts what it gets after the loop

Maybe it could be achieved by adding a /*+ MATERIALIZE */ hint in the subquery, maybe additionally use qb_name+leading hints as describe here.

But a better solution would be to rewrite the query to use explicit JOIN instead of IN (see this for example. Note that even if it tells Oracle query optimizer is capable of parsing IN as JOIN, it's not the case with your query because it self-joins view and not table).

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.