FB 1.5.3

in this query, I want all "deal"s and the last entry of "Discussion"
where the deal has been edited since 12/1


Select D.Somecolumn, C.SomeColumn
from Deal D
left join Discussion C on C.Deal_Id=D.Id and C.Id=(Select Max(c2.ID)
from discussion c2 where c2.deal_id=d.id)
Where D.editDate >= '12/1/2006'
Order by 2

gives a plan using the index on D.EditDate (IDX_DEAL_1)

PLAN (C2 ORDER IDX_DISCUSSION_1) PLAN SORT (JOIN(D INDEX (IDX_DEAL_1),C
INDEX(PK_DISCUSSION)))


Works like a charm.

Now, in this query, I want all "deal"s and the last entry of
"Discussion" where the deal has been edited since 12/1, or, a
"discussion" or "attachment" has been added since the same date.


Select D.Somecolumn, C.SomeColumn
from Deal D
left join Discussion C on C.Deal_Id=D.Id and C.Id=(Select Max(c2.ID)
from discussion c2 where c2.deal_id=d.id)
Where D.editDate >= '12/1/2006'
OR (Exists ( select 1 from discussion DS where DS.Deal_ID=D.ID AND
DS.CreateDate >= '12/1/2006' ))
or (Exists ( Select 1 from Attachment A where A.Deal_ID=D.ID AND
A.CreateDate >= '12/1/2006'))
Order by 2

however, this query does not use an index, but rather natural for D...

PLAN (C2 ORDER IDX_DISCUSSION_1) PLAN (DS INDEX
(FK_DISCUSSION_1,IDX_DISCUSSION_2)) PLAN (A INDEX
(FK_ATTACHMENT_2,IDX_ATTACHMENT_1)) PLAN SORT (JOIN (D NATURAL,C INDEX
(PK_DISCUSSION)))

Why? How could I possibly change this so that it uses the index on
d.editDate?

perhaps not so interestingly, I can change the portion "d.editdate=blah"
to any other indexed field, and I still get a natural order search on
D. but, removing the exists, and the correct index is used no matter
which indexed column I use in the where clause.

Thanks,
Mitch