Jean Arband wrote:
>
> >
> > 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)
True - but you may want to do some timing tests - I've seen negligible
differences between the two. Whilst a range scan is required, Oracle
would not have to scan all the keys, just the first one to determine if
a duplicate exists.
hth
connor
Received on Thu Feb 06 2003 - 08:03:21 CST
