>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 not "considerably"
fewer. Actually, the time frame should always be small in this query,
perhaps no more then a few days, and the number of discussion and
attachment 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 (I feel) as the number of rows here will eventually get
large. Therefore, the Stored procedure approach is probably the safer
alternative to allow me to split the query into mulitple queries and
make more effective use of the appropriate indexes.

I will explore that.

Thanks to all!
Mitch