At 23:46 26.06.2003 +1000, you wrote:

>If you have a foreign key on those two btable FK columns, then you couldn't
>have a row in btable with fk1 = something and fk2=0 unless there was a
>matching primary key in atable. IOW, your "OR" test is redundant if those
>two cols are defined as a foreign key and it would probably stuff up the
>optimizer as well.

What makes you think fk2 is a foreign key... I must admit I didn't pay
attention to this part of this example, it is not a foreign key at all.

>If they are not defined as a foreign key then the question of splitting the
>key index between the JOIN criteria and the search criteria doesn't
>arise. If you needed to make that join, it would be fairly
>absurd. Still...in that case you'd need to have separate indexes on them
>experiment with plans to see what worked better - each case on its merits
>and a to-do list item to fix up that relationship! :-))

Well, it is kind of on a to-do list and the vast majority of the records do
not contain 0. Basically, we have been registering all tumors in Norway for
the last fifty years. Then we introduced another table called reports and
linked it to the tumor table. Now, if one person just have one tumor of a
particular type, things are easy and there is no uncertainty, but for those
cases where a person have multiple tumors of the same site, we do not
necessarily know which tumor a particular report refer to and manual
resolution is required. Nowadays, this resolution is enforced, but we
cannot just delete old records because it wasn't enforced years ago.

As for trial and error, this was how I discovered the benefits of using OR
(2=0) to disable indexes. So, in general I do

select t1.pk1, t1.pk2, t1.col1, t1.col2, t2.cola, t2.colb
from atable t1
join btable t2
on t2.fk1 = t1.pk1
and (t2.fk2 = t1.pk2 or t2.fk2 = 0 or 2=0)

or

select t1.pk1, t1.pk2, t1.col1, t1.col2, t2.cola, t2.colb
from atable t1
join btable t2
on t2.fk1 = t1.pk1
where (t2.fk2 = t1.pk2 or t2.fk2 = 0 or 2=0)

(Note that fk2 is just laziness not to change the variable name, there are
no foreign keys defined on the tables as far as I know.)

Generally speaking, this is very efficient if we have a useful where clause
on some indexed field.

Set
-and a big hooray for Tim words of wisdom that just dropped into my
mailbox! No reason to put a question mark behind an answer.