(Lack of) Optimization of Unique Constraint Creation

The Constraint Optimization series:

• Part 1: Optimization of Check Constraint Creation
• Part 2: Optimization that Violates Data Integrity
• Part 3: Optimization of Foreign Key Constraint Creation
• Part 4: (Lack of) Optimization of Unique Constraint Creation
• Part 5: Adding a Column with a Default Value and a Constraint
• Part 6: Fast but Offline, or Online but Slow?
• Part 7: Adding a Unique Constraint in an Online Way
• Part 8: Constraint Optimization Summary

In the previous parts of this series I showed that Oracle does a nice optimization – that may save plenty of time – when we add in a single ALTER TABLE statement a new (nullable with no default value) column and a corresponding:
– inline (a.k.a. “column-level”) check constraint
or
– foreign key constraint (either inline or out-of-line)

What about unique constraints?

When we create a unique constraint on some column, Oracle creates by default a corresponding unique B*Tree index on that column, in order to enforce the constraint.
When we add a new column with no default value to a non-empty table, and in the same ALTER TABLE statement we create a unique constraint on that column, then the corresponding index will be created, by definition, with no entries (as B*Tree indexes do not contain entirely NULL keys). It would be nice if Oracle would simply create an empty index in this case, without scanning all the table rows, but unfortunately this kind of optimization is not done.

I added this suggestion to the OTN Database Ideas section: http://community.oracle.com/ideas/17672. Please vote up if you think it’s a good idea.

[Tested in 11.2.0.4, 12.1.0.2, 12.2.0.1]

> create table t (x varchar2(1000));

Table created.

> insert /*+ append */ into t
> select lpad(rownum,1000,'x') from dual connect by level<=1e6;

1000000 rows created.

> commit;

Commit complete.

First, let's add the column and the unique constraint in two separate statements:

> alter table t add (y number);

Table altered.

Elapsed: 00:00:00.05
> alter table t add (constraint y_uk unique (y));

Table altered.

Elapsed: 00:00:03.41

We can see that adding the column is very fast, while adding the constraint takes a significant amount of time - the time that is required for creating the corresponding index. This is expected and makes sense.

Now let's add the column and the unique constraint in a single statement. First using an out-of-line constraint and then using an inline constraint.

> alter table t add (
>     z number,
>     constraint z_uk unique (z)
> );

Table altered.

Elapsed: 00:00:03.36

> alter table t add (
>     w number constraint w_uk unique
> );

Table altered.

Elapsed: 00:00:03.81

We can see that it takes about the same time to create the unique constraint while adding the new column as it takes in separate statements.

Even if we add the constraint as "ENABLE NOVALIDATE" - instructing Oracle to skip the phase of validating existing rows - we still get the same results:

> alter table t add (
>     v number constraint v_uk unique enable novalidate
> );

Table altered.

Elapsed: 00:00:03.39

Also, by turning SQL trace on, we see in the trace files that Oracle does a full table scan of T while creating the index, in all these cases. For example:

PARSING IN CURSOR #157833112 len=71 dep=1 uid=194 oct=9 lid=194 tim=408018953472 hv=1998200890 ad='7ffb35a35c48' sqlid='9jk2n2jvjn81u'
CREATE UNIQUE INDEX "DEMO"."W_UK" on "DEMO"."T"("W") NOPARALLEL
END OF STMT
PARSE #157833112:c=0,e=808,p=0,cr=3,cu=0,mis=1,r=0,dep=1,og=1,plh=1844495725,tim=408018953472
.
.
.
EXEC #157833112:c=1171875,e=3791294,p=142857,cr=142919,cu=61,mis=0,r=0,dep=1,og=1,plh=1844495725,tim=408022744803
STAT #157833112 id=1 cnt=1 pid=0 pos=1 obj=0 op='INDEX BUILD UNIQUE W_UK (cr=142882 pr=142856 pw=0 time=847768 us)'
STAT #157833112 id=2 cnt=1000000 pid=1 pos=1 obj=0 op='SORT CREATE INDEX (cr=142882 pr=142856 pw=0 time=865033 us)'
STAT #157833112 id=3 cnt=1000000 pid=2 pos=1 obj=98142 op='TABLE ACCESS FULL T (cr=142882 pr=142856 pw=0 time=624624 us cost=38895 size=13000000 card=1000000)'
CLOSE #157833112:c=0,e=1,dep=1,type=0,tim=408022744929