Re: 8 and 8i default constraint behavior

I haven't considered this in detail - but a primary key constraint has to be checked on insert, and the check surely has to be the internal equivalent of:

    select * from tableT
    where pk_col_set = {incoming column set}     and rownum = 1
    ;

It doesn't really matter whether the index is unique or non-unique if a row exists. Bear in mind also that although the index is
declared as non-unique, whilst the constraint is enabled each entry occurs at most once.

For any single example of the test, the performance of a first_row search is unlikely to be affected by whether the index is unique or not - there is a slightly re-arrangement of the row structure but in realistic terms that is unlikely to make much difference on the equality test.

On a large-scale, an entry in a non-unique index is one byte larger than the same entry in the corresponding unique index, so there is some scope for a consequential I/O impact in changing the indexes to non-unique.

It is also worth noting that a PK of (a,b) can be enforced by any index that starts with the two columns in any order - so a nominally non-unique index on (b,a,c) would be adequate for deferrable enforcement. (Tested on 8.1.7.0)

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html




>

>I've never quite worked out how uniqueness is enforced without a unique

>index, and maybe Jonathan can explain for us the precise internals.  I just

>know it happens (and allegedly, according to Steve Adams, there is no

>noticeable performance hit in doing so -otherwise I wouldn't recommend it).

>