Hi Adam,

Adam wrote:

>Hi Alexandre,
>
>Part of the problem of the trigger approach (apart from the
>constraint not being obvious) is that if I delete a record from
>tableA, then I would have a before delete trigger to remove matching
>records from tableB (to replicate cascade). However, due to MGA
>another transaction started before I commit could insert a record
>into tableB matching the record I have just deleted, and they will
>not get an exception.
>
>

Yes, I know it, but procedural referencial constraints should only be
used on "static" parent table.

>Even without the hint directive, can you think of any reason you
>would ever want to use a FK index with poor selectivity where a
>compound key starting with the FK field with perfect selectivity
>exists?
>
>

The first segment will have the same selectivity as the single segment
index. Don't know what the bennefit will get, probably none

>Perhaps it is as simple as an improvement to the optimiser to ignore
>any index with a selectivity worse than ____ [insert value]. Then
>again, perhaps this is easier said then done.
>
>
>

Can go so deep, but I think just if some histogram of distribution of
the values should help here.

>Yeah, it may have been caching that made the significant difference.
>The actual query used a where clause as well on that same field so I
>don't know if that makes a difference. It is a bit tricky because the
>query is generated on the fly based on the available data, but I have
>made a change to +0 to the foreign key field and this at least makes
>it acceptable.
>
>

Good...

>In any case, I have reduced the query time from about a minute to 10
>seconds, and there is another section of code where this information
>is parsed over which takes too long IMO, so I will focus on that
>first. (The first rule of optimisation, work on whatever will give
>you the best improvement).
>
>

For sure....

>Thanks for your help
>
>

I gave you no help at all. :-)

>Adam
>

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br