Re: 8 and 8i default constraint behavior

See, I knew you'd know!!

Thanks Jonathan! (Incidentally, what's the extra byte?)

Regards
HJR "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:989397497.12280.0.nnrp-10.9e984b29_at_news.demon.co.uk...
>
> 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).
> >
>
>
>
>
>
Received on Wed May 09 2001 - 07:24:53 CDT