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) */
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
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.
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.