Re: Unique contraints vs. Unique indexes

Certainly seems to be significant.

If you create two identical tables, with non-unique indexes on a non-null 'id' column, but then declare a primary key constraint on that column for one of the tables, then the treatment of the two tables is not identical at parse time:

e.g.
select

        t1.padding,
        t2.padding
from
        t1,
        t2
where
        t1.id + 1 = t2.id + 1
and     t1.id = 2500
and     t2.id = 2500

id is non-null, with a non-unique index in both cases. T1 additional has a PK constraint on id.

The optimiser consider two possible paths for T2   Access path: tsc Resc: 12 Resp: 12   Access path: index (equal)

and three possible paths for T1
  TABLE: T1 ORIG CDN: 5000 CMPTD CDN: 1

  Access path: tsc  Resc:  12  Resp:  12
  Access path: index (unique)
  Access path: index (eq-unique)

Note - this persists irrespective of order of tables, order of predicates, order of table creation etc. (As one would expect, since Oracle is viewing only the 'single table access paths' at this point of the extracted dump).

--
Jonathan Lewis

Seminars on getting the best out of Oracle
Last few places available for Sept 10th/11th
See http://www.jlcomp.demon.co.uk/seminar.html




Howard J. Rogers wrote in message <3b7a5366_at_usenet.per.paradox.net.au>...


>

>I believe Jonathan Lewis posted the other day that the optimiser is canny

>enough to work out that even though it appears as a non-unique index, it

>knows it is being used to supposrt a unique or primary key constraint, and

>can thus be treated as a unique.

>