Skip to Main Content
  • Questions
  • altering table to enable foreign key constraint doesn't utilize parallel query when desired

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Jeff.

Asked: February 02, 2023 - 8:09 pm UTC

Last updated: February 08, 2023 - 2:41 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

If I trace an execution of this statement

alter table TAB1 enable foreign_key_cons_on_tab2


then a recursive query similar to the following will attempt to find rows in TAB1 with no matching row in TAB2. If it fetches zero rows, then the constraint is enabled.

select /*+ all_rows ordered dynamic_sampling(2) */
       A.rowid
 from TAB1 A , TAB2 B
where ( A.KEY1 is not null )
  and ( B.KEY1 (+) = A.KEY1 )
  and ( B.KEY1 is null)


When I look at the recursive statement's execution plan, I see that it is serial in nature. If I manipulate some parameters I can coerce a parallel execution plan.

But the execution of that plan remains serial. No parallel query slaves are enlisted and the query ends up taking longer to finish than the serial plan.

If I take that recursive query and execute it in SQL*Plus (at depth 0), then it executes in parallel and uses all the PQ slaves I expected.

Why? Since exclusive locks are taken, there should be no reason to preclude parallel execution.

I tried the following with no success

- alter session force parallel ddl
- alter session force parallel query
- creating a SQL profile to add a parallel
- increasing the table's degree of parallelism

UPDATE #1
Thanks to Tanel Poder's advice, I was able to make this work.

1. enable novalidate
2. enable validate

I would be lying if I said I wasn't disappointed in having to cache this kind of information.

and Connor said...

Hi Jeff - I think the docs have always stated this (Well, I looked back as far as 11.2)

"If you change the state of any single constraint from ENABLE NOVALIDATE to ENABLE VALIDATE,
then the operation can be performed in parallel, and does not block reads, writes, or other DDL operations."

but I agree that we should be a little clearer on the opposite, ie, when you *wont* get it.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Jeff Holt, February 03, 2023 - 4:38 pm UTC

Thanks, Connor.

Even though I was able to speed up the validation using Tanel's advice, the constraint ended up in a state that makes me very uncomfortable.

It used to be "NOT DEFERRABLE" and "IMMEDIATE". Now it is "DEFERRABLE" and "DEFERRED".

I then altered the constraint to make it "initially immediate" but I don't think that's good enough.

I read how I can execute an alter session command to make all constraints immediate but the docs say I can't put it in a trigger. I'm hoping the docs are wrong and should have said "put it in a DML trigger".

1. If an application executes a 100,000 row delete on a 5B row table whose fkey constraints are deferred until COMMIT, will the recursive work at COMMIT time be the same as it would if it were immediate?

2. Will a constraint whose state is DEFERRABLE and IMMEDIATE cause the same behavior as NOT DEFERRABLE and IMMEDIATE?

3. Will an after logon trigger, setting constraints to immediate, make all constraints be enforced during DML operations such that a DEFERRABLE and IMMEDIATE constraint acts as if it were NOT DEFERRABLE and IMMEDIATE?

4. The docs say that once a constraint reaches the DEFFERABLE state, it cannot be made NOT DEFERRABLE. If I can execute an alter session command to make all constraints NOT DEFERRABLE, then why can't I change one?
Connor McDonald
February 06, 2023 - 3:00 am UTC

OK - I'm intrigued.

Are you saying that a constraint you added that did NOT have DEFERRABLE in its initial definition is now saying its DEFERRABLE ?


SQL>
SQL> create table t1 as select distinct owner from dba_objects;

Table created.

SQL> alter table t1 add constraint t1pk primary key (owner);

Table altered.

SQL>
SQL> create table t2 as select * from dba_objects;

Table created.

SQL> alter table t2 add constraint t2fk foreign key ( owner)
  2  references t1 ( owner);

Table altered.

SQL>
SQL> select * from user_constraints
  2  where constraint_name in ('T1PK','T2FK')
  3  @pr
==============================
OWNER                         : MCDONAC
CONSTRAINT_NAME               : T1PK
CONSTRAINT_TYPE               : P
TABLE_NAME                    : T1
SEARCH_CONDITION              :
SEARCH_CONDITION_VC           :
R_OWNER                       :
R_CONSTRAINT_NAME             :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : USER NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 06-FEB-23
INDEX_OWNER                   : MCDONAC
INDEX_NAME                    : T1PK
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 3
==============================
OWNER                         : MCDONAC
CONSTRAINT_NAME               : T2FK
CONSTRAINT_TYPE               : R
TABLE_NAME                    : T2
SEARCH_CONDITION              :
SEARCH_CONDITION_VC           :
R_OWNER                       : MCDONAC
R_CONSTRAINT_NAME             : T1PK
DELETE_RULE                   : NO ACTION
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : USER NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 06-FEB-23
INDEX_OWNER                   :
INDEX_NAME                    :
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 3

PL/SQL procedure successfully completed.

SQL>
SQL> alter table t2 drop constraint t2fk;

Table altered.

SQL>
SQL> alter table t2 add constraint t2fk foreign key ( owner)
  2  references t1 ( owner) enable novalidate;

Table altered.

SQL>
SQL> alter session force parallel ddl;

Session altered.

SQL>
SQL> alter table t2 modify constraint t2fk enable validate;

Table altered.

SQL>
SQL> select * from user_constraints
  2  where constraint_name in ('T1PK','T2FK')
  3  @pr
==============================
OWNER                         : MCDONAC
CONSTRAINT_NAME               : T1PK
CONSTRAINT_TYPE               : P
TABLE_NAME                    : T1
SEARCH_CONDITION              :
SEARCH_CONDITION_VC           :
R_OWNER                       :
R_CONSTRAINT_NAME             :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : USER NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 06-FEB-23
INDEX_OWNER                   : MCDONAC
INDEX_NAME                    : T1PK
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 3
==============================
OWNER                         : MCDONAC
CONSTRAINT_NAME               : T2FK
CONSTRAINT_TYPE               : R
TABLE_NAME                    : T2
SEARCH_CONDITION              :
SEARCH_CONDITION_VC           :
R_OWNER                       : MCDONAC
R_CONSTRAINT_NAME             : T1PK
DELETE_RULE                   : NO ACTION
STATUS                        : ENABLED
DEFERRABLE                    : NOT DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : VALIDATED
GENERATED                     : USER NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 06-FEB-23
INDEX_OWNER                   :
INDEX_NAME                    :
INVALID                       :
VIEW_RELATED                  :
ORIGIN_CON_ID                 : 3

PL/SQL procedure successfully completed.

SQL>
SQL>


Jeff Holt, February 07, 2023 - 6:24 am UTC

Are you saying that a constraint you added that did NOT have DEFERRABLE in its initial definition is now saying its DEFERRABLE ?

Not quite. I executed your test on my instance and was able to reproduce your results.

My test was slightly different. And I'm embarrassed to say I didn't catch my user error soon enough.

I did not start my test by dropping the DEFERRABLE constraint and recreating it. I thought that disabling the constraint would change the state to NOT DEFERRABLE, even though I had read the implication that a DEFERRABLE constraint had to be dropped if I wanted it in a NOT DEFERRABLE state.

I was merely disabling the constraint and then enabling it with the two steps. So, it started out as DEFERRABLE and ended up, uh, as DEFERRABLE.

Sorry for the hassle.
Connor McDonald
February 08, 2023 - 2:41 am UTC

thanks for the update

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.