Re: Confusing blocking issue - More Details

This looks like an extension to the old
choice of index thing:

    If you can use two indexes, which do you use ?     The one that was created last (in v8 and below).

I can't confirm the algorithm that Oracle uses to decide which index it's going to use to block on, but in a couple of tests where I built your sample with two indexes (one of which is redundant, you realise) then on my system I saw consistent behaviour:

    Oracle 8.1.7.4 - blocked if the two-column index     was built second: which also covers the case     where it was built first, then dropped and rebuilt.

    Oracle 9.2.0.2 - blocked if the two-column index     was built first.

Note - alter index rebuild did not change the behaviour on either system.

Note also that the constraint definition (cdef$) reported the 'proper' PK index as supporting the constraint, even when the FK check used the wrong index.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


Tim Callaghan wrote in message
<3lqf3v0a9kcgoatkde64anmq8afi83ci4d_at_4ax.com>...


>John,

>

>In my scenario I've actually got 2 indexes:

>

>First is my PK index (location).

>Second is my Unique index (location_id + last_touch_date).

>

>-Tim

>

>On Wed, 29 Jan 2003 14:48:49 -0000, "Jonathan Lewis"

><jonathan_at_jlcomp.demon.co.uk> wrote:

>

>>

>>I forgot to mention - the trigger has

>>nothing to do with it.  It's all down

>>to the index (id, timed) supporting

>>the primary key (id).

>