Hi,

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

Because you ask for "(d.editDate >= '2006-01-12') OR exists(...) OR exists(...)" it isn't able to
use an index on table Deal for every part involved with the OR comparisions. Which meant the table
needs to be scanned completly and thus NATURAL. If a Deal (thus editDate) is always created before
Discussion and Attachment (which looks so) then you want to use this:

"d.editDate >= '2006-01-12' AND (EXISTS(...) OR_ EXISTS(...))"

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database development support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Firebird and Interbase users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info




--------------------------------------------------------------------------------
Mijn Postvak In wordt beschermd door SPAMfighter
1092 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!