Parallel PDDL when enable Constraint 并行 启用约束

Symptoms

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

You are modifying table with mutli billion rows.  Specific column was nullable and became not null

The time to change constraint takes  around 40 minutes.

Mainly due to execution

SELECT /* all_rows ordered dynamic_sampling(2) */ A.rowid,:1,:2,:3 from "<user_name>"."<table_name>" A WHERE ( "<COL1>" is NULL)

this query is running without parallelism

You specifically run
ALTER SESSION FORCE PARALLEL DML PARALLEL 8
/
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8
/
ALTER SESSION FORCE PARALLEL DDL PARALLEL 8
/

But it still does not use parallelism

Any option to force parallelism?
 

Changes

Cause

Incorrect syntax
 

Solution

Firstly change
ALTER TABLE PARALLEL 8

and then
ALTER TABLE MODIFY NOT NULL-----modify 这种命令才行，直接enable constraint不行，要先enable novalidate 再 modify!!!

then in this case it should run in parallel

ALTER SESSION ENABLE PARALLEL DDL;

ALTER TABLE XXXXX.CONS PARALLEL 8 ;

Alter table XXXXX.CONS disable constraint CONS_nn10;

Alter table XXXXX.CONS enable  noVALIDATE constraint CONS_nn10;--必须有

Alter table XXXXX.CONS enable VALIDATE constraint CONS_nn10;

--Alter table XXXXX.CONS modify   constraint CONS_nn10 VALIDATE;

ALTER TABLE XXXXX.CONS NOPARALLEL  ;

---test 1：

Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 
SQL> set timing on
SQL> ALTER SESSION ENABLE PARALLEL DDL;

SQL> ALTER TABLE XXXXX.CONS PARALLEL 8 ;

SQL> Alter table XXXXX.CONS disable constraint CONS_nn10;

SQL> --Alter table XXXXX.CONS enable  noVALIDATE constraint CONS_nn10;
SQL> Alter table XXXXX.CONS enable VALIDATE constraint CONS_nn10;

Executed in 633.273 seconds

SQL> ALTER TABLE XXXXX.CONS NOPARALLEL  ;

-----test 2：

SQL> ALTER SESSION ENABLE PARALLEL DDL;

SQL> ALTER TABLE XXXXX.CONS PARALLEL 8 ;

SQL> Alter table XXXXX.CONS disable constraint CONS_nn10;

SQL> Alter table XXXXX.CONS enable  noVALIDATE constraint CONS_nn10;

Table altered

Executed in 0.015 seconds

SQL> Alter table XXXXX.CONS enable VALIDATE constraint CONS_nn10;

Table altered

Executed in 16.47 seconds

SQL> ALTER TABLE XXXXX.CONS NOPARALLEL  ;

-----------------create index----------------------

----enable not null constraint 慢的话，可以考虑 paralledl 建个index 再enable 再drop index----

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.3 and later
Information in this document applies to any platform.

GOAL

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

You want to create the PRIMARY KEY constraint while specifying the name and storage clause for the underlying INDEX in PARALLEL. You used the following compound statement:

SQL> ALTER SESSION ENABLE PARALLEL DDL;
SQL> ALTER TABLE emp ADD CONSTRAINT P_EMP PRIMARY KEY (empno)
USING INDEX (CREATE UNIQUE INDEX I_EMP ON
emp (empno) PARALLEL) ;
SQL> ALTER SESSION DISABLE PARALLEL DDL;

and you find that the execution time takes long time, while creating the index in parallel alone and enabling the constraint only takes much less time.
SQL> ALTER SESSION ENABLE PARALLEL DDL;
SQL> CREATE UNIQUE INDEX I_EMP ON  emp(empno) PARALLEL;
SQL> ALTER TABLE emp ADD CONSTRAINT P_EMP PRIMARY KEY (empno) USING INDEX I_EMP;
SQL> ALTER SESSION DISABLE PARALLEL DDL;

You want to know why this takes place.

SOLUTION

The index generation in the compound statement is not executed in parallel. Only the second statement is
executed in parallel. The reason behind that is that for nonpartitioned tables and indexes, only the following types of DDL statements can be parallelized:

CREATE TABLE...AS SELECT
CREATE INDEX
ALTER INDEX...REBUILD

So Alter table is not supported but create index is. If you're working with partitioned tables and
indexes, the scope of Oracle's parallel DDL support broadens. The following statements can be
parallelized for partitioned tables and indexes:

CREATE TABLE...AS SELECT
ALTER TABLE...MOVE PARTITION
ALTER TABLE...SPLIT PARTITION
CREATE INDEX
ALTER INDEX...REBUILD PARTITION
ALTER INDEX...SPLIT PARTITION

Not all tables allow these operations to be executed in parallel. Tables with object columns or
LOB columns don't allow parallel DDL. Based on that you need to do it in two steps

------------------------------------------

SYMPTOMS

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

You are modifying table with mutli billion rows.  Specific column was nullable and became not null

The time to change constraint takes  around 40 minutes.

Mainly due to execution

SELECT /* all_rows ordered dynamic_sampling(2) */ A.rowid,:1,:2,:3 from "<user_name>"."<table_name>" A WHERE ( "<COL1>" is NULL)

this query is running without parallelism

You specifically run
ALTER SESSION FORCE PARALLEL DML PARALLEL 8
/
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8
/
ALTER SESSION FORCE PARALLEL DDL PARALLEL 8
/

But it still does not use parallelism

Any option to force parallelism?
 

CHANGES

CAUSE

Incorrect syntax
 

SOLUTION

Firstly change
ALTER TABLE PARALLEL 8

and then
ALTER TABLE MODIFY NOT NULL

then in this case it should run in parallel

------metalink上是错误的

 
alter table SCHED_STEP parallel (degree 8);

ALTER TABLE SCHED_STEP

 MODIFY (CREATION_DB_ID NUMBER NOT NULL),

     CTRL_DB_ID NUMBER NOT NULL);

this takes about 40 minutes but never goes parallel.

 
user13514341, it seemed to me that you should be able to modify a column in parallel. I have not checked the following but I did find another note that shows how.

Do Not Use Parallel Query In ALTER TABLE MODIFY (column Not Null) (Doc ID 2264469.1)

The note sets the DOP on the table then performs the ALTER MODIFY with parallel DDL set on in session. That seems worth a try.

Do Not Use Parallel Query In ALTER TABLE MODIFY (column Not Null) (Doc ID 2264469.1)

The note sets the DOP on the table then performs the ALTER MODIFY with parallel DDL set on in session. That seems worth a try.

I am on Oracle EE 19.11/AIX Power9, ran into same issue,

I do recall following method working prior , following does not work any more

set timing on
alter session force parallel ddl;
alter table <tb> parallel n;
alter table <tb> modify column c not null;
alter table <tb> noparallel;
The fastest was,

set timing on
alter table <tb> parallel n;
alter session force parallel ddl;
alter table <tb> modify column c (type) constraint <constraint name> not null enable novalidate;
-- Was fast as parallel query count(*)
alter table <tb> modify constraint <constraint name> validate;
alter table <tb> noparallel;

----必须先enable novalidate

-----文档不准-----------What DDL Commands are supported for Parallelism? Can We Enable or Modify Constraints Using Parallelism? (Doc ID 2561778.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.

GOAL

 Can We Enable or Modify Constraints Using Parallelism?

SOLUTION

Enabling or disabling or modifying the constraints cannot be run in parallelism.
As per Oracle Documentation, only the following DDL operations are supported in Parallelism.

The parallel DDL statements for non-partitioned tables and indexes are:
 

CREATE INDEX
CREATE TABLE AS SELECT
ALTER TABLE MOVE
ALTER INDEX REBUILD

The parallel DDL statements for partitioned tables and indexes are:
 

CREATE INDEX
CREATE TABLE AS SELECT

ALTER TABLE {MOVE|SPLIT|COALESCE} PARTITION

ALTER INDEX {REBUILD|SPLIT} PARTITION

-----------------实际方案

I know the following is DDL as opposed to DML, but I would appreciate any help.

ALTER TABLE VH_TEST
ADD CONSTRAINT "CK_VHTEST01" CHECK (deletedFlag=(1) OR deletedFlag=(0))
ADD CONSTRAINT "PK_VHTEST" PRIMARY KEY ("ID")
ADD CONSTRAINT "FK_VHTEST41" FOREIGN KEY ("BILLINGCODEID") REFERENCES "ENCOUNTERBILLINGCODE" ("ID")
ADD CONSTRAINT "FK_VHTEST42" FOREIGN KEY ("CONFIDENTIALITYCODEID") REFERENCES "LK_CONFIDENTIALITY" ("ID")
...

There are a total of 34 foreign key constraints in the entire statement in addition to the check and primary key. The statement takes 20 hours 18 minutes to execute.

I found an article where I can create the check and foreign key constraints with the ENABLE NOVALIDATE clause. Then, use the ALTER TABLE MODIFY CONSTRAINT VALIDATE statement 
after changing the degree of parallelism on the table. This is supposed to allow the checking of the existing data using parallel processes.

ALTER TABLE VH_TEST

ADD CONSTRAINT "CK_VHTEST01" CHECK (deletedFlag=(1) OR deletedFlag=(0)) ENABLE NOVALIDATE

 ADD CONSTRAINT "PK_VHTEST" PRIMARY KEY ("ID")

 ADD CONSTRAINT "FK_VHTEST41" FOREIGN KEY ("BILLINGCODEID") REFERENCES "ENCOUNTERBILLINGCODE" ("ID") ENABLE NOVALIDATE

 ADD CONSTRAINT "FK_VHTEST42" FOREIGN KEY ("CONFIDENTIALITYCODEID") REFERENCES "LK_CONFIDENTIALITY" ("ID") ENABLE NOVALIDATE

...

ALTER SESSION ENABLE PARALLEL DDL;

ALTER TABLE VH_TEST PARALLEL (DEGREE 2);

ALTER TABLE VH_TEST MODIFY CONSTRAINT "CK_VHPTENC01" VALIDATE;

ALTER TABLE VH_TEST MODIFY CONSTRAINT "FK_VHPTENC41" VALIDATE;

ALTER TABLE VH_TEST MODIFY CONSTRAINT "FK_VHPTENC42" VALIDATE;

...

What I would like to know is if there are any other ways besides this to reduce the amount of time that it takes to add the constraints.

Thanks.

Flag
Quote
Off Topic
Like
Comments
Mark D Powell
Mark D Powell DBA Posts: 17,366 Rubellite
May 25, 2022 9:11PM
User_4BW7J, if on the Enterprise Edition using parallel DDL is probably you best option.

HTH -- Mark D Powell --

Flag
Quote
Off Topic
Like
User_4BW7J
User_4BW7J Posts: 3 Green Ribbon
May 25, 2022 10:35PM
Thanks for the comment. Unfortunately, it does not look like the ALTER TABLE ADD CONSTRAINT is included.

The parallel DDL statements for nonpartitioned tables and indexes are:

CREATE INDEX
CREATE TABLE AS SELECT
ALTER TABLE MOVE
ALTER INDEX REBUILD
The parallel DDL statements for partitioned tables and indexes are:

CREATE INDEX
CREATE TABLE AS SELECT
ALTER TABLE {MOVE|SPLIT|COALESCE} PARTITION
ALTER INDEX {REBUILD|SPLIT} PARTITION
This statement can be executed in parallel only if the (global) index partition being split is usable.

Flag
Quote
Off Topic
Like
SureshMuddaveerappa
SureshMuddaveerappa Sr Data Warehouse Architect Posts: 16,771 Tanzanite
May 26, 2022 5:20AM
Hello User_4BW7J,

The other approaches to explore would also be dependent on how large the table is, availability of downtime and free space. One option to evaluate is to go through CTAS and have the table pre-built with the required constraints. Here you can control the degree of parallelism for this table build. Later switch this newly built table with the current existing one.

Cheers -- Suresh

Flag
Quote
Off Topic
Like
user8601485
user8601485 Posts: 1 Blue Ribbon
May 30, 2022 9:16AM
Try something like the commands below. All except the validate should execute very quickly. The validate takes much longer but should execute in parallel.

ALTER TABLE table_owner.table_name ADD CONSTRAINT table_name_fk001 FOREIGN KEY ("THE_KEY")

     REFERENCES table_owner.table2 (THE_KEY) DISABLE;

alter table table_owner.table_name modify constraint table_name_fk001 enable novalidate;

alter session set parallel_degree_limit=8;

alter session enable parallel ddl;

alter table table_owner.table_name parallel (degree 8);

alter table table_owner.table_name modify constraint table_name_fk001 validate;