SQL Performance (MOSC)

MOSC Banner

create Primary constraint using existing unique index took 9 hours

edited Mar 9, 2015 10:23AM in SQL Performance (MOSC) 9 commentsAnswered

The table and indexes were just created using impdp, took 6 hours in heavily loaded system.

The unique index used by this constraint took about 20 minutes to create.

The problem is occurring with this one primary constraint.

Other primary constraints using existing unique index took only seconds, even on large tables and indexes.

No blocking lock were observed.

An earlier execution, running about stand alone, took 3.5 hours.

01:38:58 SQL>
01:38:58 SQL> ALTER TABLE "XXXX"."PSTNG_LN_CAT"
01:38:58   2  ADD CONSTRAINT "PK_PSTNGLNCAT_PSTNG_LN_CATDOC"
01:38:58   3  PRIMARY KEY ("DOC_CD", "DOC_DEPT_CD", "DOC_ID", "DOC_VERS_NO", "DOC_PSTNG_NO")
01:38:58   4  ENABLE;

Table altered.

Elapsed: 08:56:51.84

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center