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 a recent post I showed that Oracle does a nice optimization when we add a new (nullable with no default value) column with an inline (a.k.a. “column-level”) check constraint in a single ALTER TABLE statement. This optimization does not apply for out-of-line (“table-level”) check constraints.
So, what about foreign key constraints?
Clearly, when adding a new nullable with no default value column to a table which contains records, then, by definition, the new column has NULL in all the existing records. If in the same ALTER TABLE statement we also create a foreign key constraint for the new column, then, by definition, the constraint is valid for all the existing records, because NULLs always pass the foreign key constraint check. So in this case it is unnecessary to access each and every record of the table for the validation phase of the foreign key constraint creation, and indeed Oracle applies this optimization, in both inline and out-of-line foreign key constraints. Let’s see it.
[tested in 11.2.0.4, 12.1.0.2, 12.2.0.1]
Setup
SQL> create table p (id number primary key);
Table created.
SQL> create table c (data varchar2(1000));
Table created.
SQL> insert /*+ append */ into c
select lpad(rownum,1000,'x') from dual connect by level<=1e6;
1000000 rows created.
SQL> commit;
Commit complete.
Adding the Foreign Key to an Existing Column
First, let's see what happens when we add the column and the constraint in two separate statements.
SQL> col value new_value value_0 noprint
SQL> select b.value from v$statname a, v$mystat b
where a.statistic# = b.statistic# and a.name = 'session logical reads';
SQL> set timing on
SQL> alter table c add (
x number
);
Table altered.
Elapsed: 00:00:00.05
SQL> alter table c add (
constraint fk1 foreign key (x) references p(id)
);
Table altered.
Elapsed: 00:00:03.17
SQL> set timing off
SQL> select b.value-&VALUE_0 logical_reads from v$statname a, v$mystat b
where a.statistic# = b.statistic# and a.name = 'session logical reads';
LOGICAL_READS
-------------
143920
Note the elapsed time and the number of logical reads - most of them are due to accessing all the rows of C in order to validate the foreign key constraint. If we turn SQL trace on for this example, we can see the following query (or a similar one) that Oracle issues in order to perform this validation:
select A.rowid, :1, :2, :3 from "DEMO"."C" A , "DEMO"."P" B where( "A"."X" is not null) and( "B"."ID" (+)= "A"."X") and( "B"."ID" is null)
Now, let's see the optimization in action when adding the column and the corresponding foreign key constraint in the same ALTER TABLE statement. First using out-of-line constraint and then using inline constraint.
Adding the Column and the Foreign Key Together – Out-of-Line Constraint
SQL> col value new_value value_0 noprint
SQL> select b.value from v$statname a, v$mystat b
where a.statistic# = b.statistic# and a.name = 'session logical reads';
SQL> set timing on
SQL> alter table c add (
y number,
constraint fk2 foreign key (y) references p(id)
);
Table altered.
Elapsed: 00:00:00.04
SQL> set timing off
SQL> select b.value-&VALUE_0 logical_reads from v$statname a, v$mystat b
where a.statistic# = b.statistic# and a.name = 'session logical reads';
LOGICAL_READS
-------------
475
Adding the Column and the Foreign Key Together – Inline Constraint
SQL> col value new_value value_0 noprint
SQL> select b.value from v$statname a, v$mystat b
where a.statistic# = b.statistic# and a.name = 'session logical reads';
SQL> set timing on
SQL> alter table c add (
z number constraint fk3 references p(id)
);
Table altered.
Elapsed: 00:00:00.03
SQL> set timing off
SQL> select b.value-&VALUE_0 logical_reads from v$statname a, v$mystat b
where a.statistic# = b.statistic# and a.name = 'session logical reads';
LOGICAL_READS
-------------
376
Much faster, with significantly less logical reads, since accessing the actual records is omitted.
And, if we trace these operations, then the validation query that we've seen in the trace file in the first example does not appear in the trace files in the second and third examples.