At 03/04/2002 05:39 PM, you wrote:

>Good Morning!
>
> I have a query that looks like this:
>
>SELECT SO.SERVICEORDERID, SO.STATUS,SO.CLIENTBILLTOID,
> BT.ATTN,BT.ADDRESS1, BT.ADDRESS2, BT.ADDRESS3,
>BT.CITY, BT.ST, BT.ZIP,
> CL.NAME,
>CL.ACCOUNTNUMBER,SO.VERBALPO,SO.BLANKETPOID,
> SUM((PD.QTY*PD.SALEPRICE)+PD.SALESTAX) AS
>PARTTOTAL
>FROM SERVICEORDER SO
>JOIN CLIENTBILLTO BT ON SO.CLIENTBILLTOID =
>BT.CLIENTBILLTOID
>JOIN CLIENT CL ON BT.CLIENTID = CL.CLIENTID
>LEFT OUTER JOIN PARTDETAIL PD ON SO.SERVICEORDERID =
>PD.SERVICEORDERID
>WHERE SO.STATUS = 10
> AND SO.DATECOMPLETED < '04/03/2002'
> AND SO.INVOICENUMBER IS NULL
>GROUP BY SO.SERVICEORDERID,
>SO.STATUS,SO.CLIENTBILLTOID,
> BT.ATTN,BT.ADDRESS1, BT.ADDRESS2, BT.ADDRESS3,
>BT.CITY, BT.ST, BT.ZIP,
> CL.NAME,
>CL.ACCOUNTNUMBER,SO.VERBALPO,SO.BLANKETPOID
>
>
> This is the plan that IB comes up with:
>
>PLAN
> SORT
> (JOIN
> (JOIN
> (BT Natral,
>
> CL INDEX (RDB$PRIMARY13),
> SO INDEX (SERVICEORDER_COMPLETEDATE,
> SERVICEORDER_CLIENTBILLTOID,
> SERVICEORDER_STATUS
> )
> ),
> PD INDEX (PARTDETAIL_SO)
> )
> )
>
> This makes for a rather slow result set.
> If I try to use **BT INDEX (RDB$PRIMARY14)**, IB
>complains that that index cannot be used. That is the
>primary key for that table.
>
> Any ideas why it cannot be used?

Do you have an index on CLIENTBILLTO.CLIENTBILLTOID?


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