Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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>...Received on Wed Aug 15 2001 - 06:38:07 CDT
>
>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.
>