SQL Performance (MOSC)

MOSC Banner

Create Constraint Using index VS novalidate option

Hi,

We have table XXX and we created UNIQUE index AAA on this table on columns COL1,COL2,COL3.

We are adding constraint YYY as a PK on the same column set using the below syntax ( ALTER TABLE ADD CONSTRAINT USING INDEX ).

ALTER TABLE XXX ADD CONSTRAINT YYY PRIMARY KEY (COL1,COL2,COL3)
USING INDEX PCTFREE 10 INITRANS 40 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ENABLE PARALLEL 30;

We have noticed that adding the constraint is taking much time so we decided to create the PK using novalidate option using the below syntax and it was created in no time ( we can guarantee the data uniqueness as we already have UNIQUE index YYY on the same columns).

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