Re: Deferrable vs. Not-Deferrable Constraints

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Thu, 6 Feb 2003 07:14:23 +1100
Message-ID: <W7e0a.41550$jM5.104238@newsfeeds.bigpond.com>

"Jean Arband" <arband_jean_at_yahoo.com> wrote in message news:8931f2b4.0302050805.6b1fcb16_at_posting.google.com...
> >
> > 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.

It *is* the case, I'm afraid.

SQL> create table X as select * from dba_objects; Table created.

SQL> alter table X add (constraint X_PK primary key(object_id) deferrable initially immediate);
Table altered.

SQL> set autotrace on
SQL> analyze table X compute statistics; Table analyzed.

SQL> analyze index X_PK compute statistics; Index analyzed.

SQL> select * from X where object_id=3442;

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=85)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'X' (Cost=2 Card=1 Bytes=

          85)

   2    1     INDEX (RANGE SCAN) OF 'X_PK' (NON-UNIQUE) (Cost=1 Card=1
          )

And you might care to note that the Cost here is 2.

Now, trying it with a non-deferrable constraint:

SQL> create table Y as select * from dba_objects; Table created.

SQL> alter table Y add (constraint Y_PK primary key(object_id) not deferrable);
Table altered.

SQL> analyze table Y compute statistics; Table analyzed.

SQL> analyze index Y_PK compute statistics; Index analyzed.

SQL> select * from Y where object_id=3442;

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=85)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'Y' (Cost=2 Card=1 Bytes=

          85)

   2    1     INDEX (UNIQUE SCAN) OF 'Y_PK' (UNIQUE) (Cost=1 Card=2937
          7)

And you might note that the cost here, is, er 2.

Last time I checked, 2 equalled 2. Meaning that there is no difference between the costs of the two statements.

And in case there's any doubt:

SQL> select uniqueness from dba_indexes
  2 where table_name in ('X','Y');

UNIQUENES

---

NONUNIQUE
UNIQUE And in case anyone says the test isn't using enough rows:

SQL> select count(*) from X;
  COUNT(*)

---

     29374

SQL> select count(*) from Y;
  COUNT(*)

---

     29377

> If the constraint is deferrable, I can insert many duplicates key before
the commit.

But so what? When you commit, the violating records will be rolled back. The net result is that the *contents* of the table will be unique, deferrable or not. And the optimizer knows this.

> So the the query must use a range scan instead of a unique scan.

The numbers don't lie. Have a look at the actual costs, not try and work things out from theory.

Regards
HJR Received on Wed Feb 05 2003 - 14:14:23 CST