Re: Deferrable vs. Not-Deferrable Constraints

>
> There are ZERO performance differences between a deferred and an immediate
> constraint. The optimizer is smart enough to realise that a non-unique index
> enforcing a unique/primary key constraint is just as unique as a unique
> index doing the same thing: ultimately, both are unique in their contents,
> if not in their definition.
>

It seems that it is not the case.
If the constraint is deferrable, I can insert many duplicates key before the commit. So the the query must use a range scan instead of a unique scan.

SQL> create table tt (c1 number);

Table created.

SQL> alter table tt add constraint pk_tt primary key (c1)   2 deferrable initially immediate;

Table altered.

SQL> set autotrace on;
SQL> select * from tt where c1=1;

no rows selected

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 INDEX (RANGE SCAN) OF 'PK_TT' (NON-UNIQUE) Received on Wed Feb 05 2003 - 10:05:58 CST