(Lack of) Optimization of Unique Constraint Creation

The Constraint Optimization series:

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
– 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,,]

> 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'
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

One Comment

  1. Foued

    Thanks for the share.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>