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

Hmmmm Stored Procedure is certainly not out of the question. That
would be certainly be a plausible approach.

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

And herein lies the rub, At this point, there is almost NO data to speak
of. I am trying to prepare for when there will be. I could simply
eliminate the index on CreateDate... if indeed it slows the query. You
first point is well taken... there could be fewer rows per deal_id then
those created after certain time, although, I expect no considerably
fewer. Actually, the time frame should small in this query, perhaps no
more then a few days, and the number of rows created in that time frame
should be less then a few hundred.

Perhaps this will never become an issue, but the index on DEAL is the
most important. Therefore, the Store procedure approach is probably the
safer alternative.

I will explore that.

Thanks to all!
Mitch