Hi,

> Hello IB support,

> Please help me to compose a plan for this query :

> select sum(x.Price*x.Qty/d.Rate) from xfers x
> inner join docs d on x.docid = d.id and x.dstid = :id
> and d.customerid = x.dstid and d.DepID = :dep and d.DocDate <
> CURRENT_TIMESTAMP
> inner join optypes t on x.typeid = t.id and t.factor <> 0

> The default plan is:

> PLAN JOIN (
> D INDEX (IDX_DOCDATE,RDB$FOREIGN38,RDB$FOREIGN40),
> X INDEX (RDB$FOREIGN248,RDB$FOREIGN246),
> T INDEX (RDB$PRIMARY41)
> )

Try reorganizing your query.

Here's how I would write it:

select sum(x.Price*x.Qty/d.Rate)
from xfers x
inner join docs d on ((x.docid=d.id) and (d.customerid=x.dstid))
where (x.dstid=:id)
and (d.DepID=:dep)
and (d.DocDate<CURRENT_TIMESTAMP)
and exists (select *
from optypes t
where (t.id=x.typeid)
and (t.factor<>0))

I don't know if it would be quicker, but sometimes its just the way
the query is written that makes a difference. I usually don't force a
plan on a query, but rearrange the query or create additional index,
so the query optimizer creates a better plan.

Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.accramed.ca)