Arno Brinkman wrote:

>>insert into TARGET (...)
>>select ...
>>from A
>>inner join F
>> on A.FK = F.PK
>>where F.Status in ('1', '2')
>> and A.Status in ('1', '2')
>
> Did you try to create a compound index on table F (FK, Status) and changed the
> query to :
>
> SELECT
> ...
> FROM
> A
> JOIN F ON ((A.FK = F.PK and F.Status = '1') or
> (A.FK = F.PK and F.Status = '2'))
> where
> A.Status IN ('1', '2')

OK, I tried it on a different but similar query. It seems to achieve
almost the same speed as dropping the F.Status condition altogether. But
I was a bit surprised by the plan.

This is the original plan for the query I just tested:
PLAN JOIN (A INDEX (A_Status,A_Status),
F INDEX (F_PK,F_Status,F_Status))

This is the plan with your suggested modification of the query:
PLAN JOIN (A INDEX (A_Status,A_Status),
F INDEX (F_PK,F_PK))

I would have expected something like this:
PLAN JOIN (A INDEX (A_Status,A_Status),
F INDEX (F_PK_STATUS,F_PK_STATUS))
where F_ID_STATUS is the index you said I should create. (Note that you
mixed up FK/PK on F in your reply.)

What's going on?

Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64