Mitchell Peek wrote:

> 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)))

If this truly is the statement you want, then I'm sorry to say that
there is no way to avoid NATURAL on D (at least I do not know of any way
to do so without creating a stored procedure). However, if there are
considerably less records with correct Deal_ID in discussion or
attachment than there are records created after 1 December, then you may
observe a noticeable speed increase if you eliminate the index for
CreateDate, e.g. like this:

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+0 >= '12/1/2006' ))
or (Exists ( Select 1 from Attachment A where A.Deal_ID=D.ID AND
A.CreateDate+0 >= '12/1/2006'))
Order by 2

(In my daily work, one of the most common sources of frustration is when
I do an ad-hoc query, makes a minor modification and then runs it
without checking the plan carefully enough. Often, a query I expected to
take a couple of seconds all of a sudden takes minutes due to use of an
additional not-too-selective index.)

HTH,
Set