Validating Constraints – An alternative approach to the alternative approach!

Recently I posted a video about some best practices for adding/enabling database constraints to minimize disruption to other users on the system. Check it out before proceeding with the rest of this post.

One of the examples in the video described how you could reduce the time to validate constraint by doing so in parallel. Here is a quick recap of that example.


SQL> create table par as select * from dba_objects
  2  where object_id is not null;

Table created.

SQL> alter table par add primary key (object_id);

Table altered.

SQL> create table chd as select d.* from par d,
  2   ( select 1 from dual connect by level <= 1000 );

Table created.

SQL>
SQL> alter table chd add constraint chd_fk foreign key ( object_id) references par (object_id)
  2  enable novalidate;

Table altered.

The above steps get no benefit from parallelism, but when the time comes to validate, we can do the following change to the tables first:


SQL> alter table par parallel;

Table altered.

SQL> alter table chd parallel;

Table altered.

Then when we run the VALIDATE component, you can see by looking at V$PX_PROCESS that we are cranking up server parallel processes to get the job done faster.


SQL> alter table chd modify constraint chd_fk enable validate;

Table altered.

SQL> select * from V$PX_PROCESS;   -- whilst the above was running

SERV STATUS           PID SPID                            SID    SERIAL#
---- --------- ---------- ------------------------ ---------- ----------
P000 IN USE             9 6432_8812                       486      14190
P001 IN USE            47 6432_8800                      1010      46438
P002 IN USE            48 6432_8780                      1063      62621
P003 IN USE            49 6432_8876                      1116      39577
P004 IN USE            50 6432_8872                      1174      45550
P005 IN USE            51 6432_8868                      1229      39891
P006 IN USE            52 6432_8832                      1276      61152
P007 IN USE            53 6432_8900                      1328      22022
P008 IN USE            54 6432_8896                      1383      38097
P009 IN USE            55 6432_8892                      1440      27581
P00A IN USE            56 6432_8828                         3      15290
P00B IN USE            57 6432_8856                        59      58574

Good friend Daniel reached out and made a valid point – that if you are doing this with other standard activity occurring on the database, having the tables set to PARALLEL by default might introduce some unexpected consequences on other queries against these tables. Thus the challenge then becomes: “Can we validate in parallel without setting the tables to parallel?”

I set the tables back to not allowing parallelism, and then tried a few syntactical variants to see if the ALTER TABLE … VALIDATE command could be tweaked to do the job in parallel. Unfortunately, this is not allowed.


SQL> alter table par noparallel;

Table altered.

SQL> alter table chd noparallel;

Table altered.


SQL> alter table chd modify constraint chd_fk enable validate parallel;
alter table chd modify constraint chd_fk enable validate parallel
                                                         *
ERROR at line 1:
ORA-03049: SQL keyword 'PARALLEL' is not syntactically valid following '...chd_fk enable validate '


SQL> alter table chd modify constraint chd_fk enable parallel validate;
alter table chd modify constraint chd_fk enable parallel validate
                                                *
ERROR at line 1:
ORA-03049: SQL keyword 'PARALLEL' is not syntactically valid following '...constraint chd_fk enable '

However, if you perform a SQL trace on the database session that is validating a constraint, you will in the trace file that the database is doing the same style of SQL operation that we would do manually if were were trying to validate the data. We simply join the parent and child tables with an outer join, and look for any rows that did not found an appropriate match.


select /*+ all_rows ordered dynamic_sampling(2) */ A.rowid, :1, :2, :3 
from CHD A , PAR B 
where( A.OBJECT_ID is not null) 
and( B.OBJECT_ID (+)= A.OBJECT_ID) 
and( B.OBJECT_ID is null)

Since this is just a standard database query, perhaps there is indeed a way to get the validating to be done in parallel. I can demand that the session I am in run its queries in parallel


SQL> alter session force parallel query;

Session altered.

Now when I run the ALTER TABLE … VALIDATE, we can see from V$PX_PROCESS that our validation query is being run in parallel even though the tables are not set to parallel.


SQL> select * from V$PX_PROCESS;

SERV STATUS           PID SPID                            SID    SERIAL#
---- --------- ---------- ------------------------ ---------- ----------
P000 IN USE             9 6432_8812                       486      51867
P001 IN USE            47 6432_8800                      1010      47241
P002 IN USE            48 6432_8780                      1063      56285
P003 IN USE            49 6432_8876                      1116       3667
P004 IN USE            50 6432_8872                      1174       1594
P005 IN USE            51 6432_8868                      1229      58303
P006 IN USE            52 6432_8832                      1276      12231
P007 IN USE            53 6432_8900                      1328       9120
P008 IN USE            54 6432_8896                      1383       2549
P009 IN USE            55 6432_8892                      1440      54871
P00A IN USE            56 6432_8828                         3       6595
P00B IN USE            57 6432_8856                        59      10881

Somewhat counter-intuitively, if you did not set queries to be performed in parallel, but instead forced DDL to be run in parallel (after all, it is an ALTER TABLE command that we are running), then you’ll discover that the validation is not run in parallel anymore.


SQL> alter session force parallel ddl;

Session altered.

SQL> select * from V$PX_PROCESS;  -- none set to "IN USE"

SERV STATUS           PID SPID                            SID    SERIAL#
---- --------- ---------- ------------------------ ---------- ----------
P000 AVAILABLE          9 6432_8812
P001 AVAILABLE         47 6432_8800
P002 AVAILABLE         48 6432_8780
P003 AVAILABLE         49 6432_8876
P004 AVAILABLE         50 6432_8872
P005 AVAILABLE         51 6432_8868
P006 AVAILABLE         52 6432_8832
P007 AVAILABLE         53 6432_8900
P008 AVAILABLE         54 6432_8896
P009 AVAILABLE         55 6432_8892
P00A AVAILABLE         56 6432_8828
...

So this is perhaps a safer option. Simply force the session to run queries in parallel, and you can leave the tables alone. This also solves the problem of DBAs forgetting to set tables back to NOPARALLEL when the operation is complete.

3 responses to “Validating Constraints – An alternative approach to the alternative approach!”

Alex

March 7, 2025 at 6:00 am

After the import with transform clause, can I query all the constraints which are in state enabled and novalidate (select table_name, constraint_name from dba_constraints where state =’ENABLED’ and validated=’NOT VALIDATED’) to generate a script for alter table … modify constraint … enable validate;? Should I try to validate every single constraint which shows ‘ENABLED’&’NOT VALIDATED’, or, would there be exeptions in database constraints which should remain in that state?

1. Connor McDonald
  
  March 7, 2025 at 6:43 am
  
  That’s ultimately a business decision – maybe there is old historical data in there in that isnt valid but must be retained, maybe there isn’t etc.
  
  Validated constraints help the optimizer, so generally I’d try validate them and then decide on a case by case basis on those that did not
  
  1. Alex
    
    March 7, 2025 at 10:46 pm
    
    Thank you. That’s fine. I was worried more about SYS tables (mainly Oracle schemas) than our bussines schema(s).

Learning is not a spectator sport