My Oracle Support Banner

Create Primary Key Cannot Be Executed In Parallel When Using With Index (Doc ID 473656.1)

Last updated on AUGUST 27, 2024

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.