What a dreadful plan, Tim!

Assuming CODE is fairly selective, the following two indexes should help you:

CREATE INDEX IDX_CLNT_SUB_CODE ON CLNT_SUB(CODE);
CREATE INDEX IDX_CLIENT ON CLIENT(CODE);

If not selective, add the PK to the end of the index.

HTH,
Set

At 13:04 02.07.2003 +0200, you wrote:

>Anyone got any ideas on how to optimize this query? It runs for three or
>four minutes, and there are only a few thousand records :
>
> FOR SELECT
> l.Receipt, l.Receipt2, l.Acc_Num, l.Sub_Code,
> SUM(l.MQuantity) AS MQuantity, L.INV_TYPE, L.INV_PREFIX,
> c.SurName, s.Sub_Name, s.Card_Code, s.RegNum
> FROM
> log L JOIN client C
> ON
> C.code = L.acc_Num
> JOIN clnt_sub S
> ON
> S.code = L.acc_num
> AND S.sub_code = L.sub_code
> WHERE
> L.POS_ID = :POSID
> AND L.SHIFTNUM = :SHIFTNUM
> GROUP BY l.Receipt, l.Receipt2, l.Acc_Num, l.Sub_Code,
> l.inv_type, l.inv_prefix, c.SurName, s.Sub_Name, s.Card_Code,
> s.RegNum
> ORDER BY c.Surname, l.Acc_Num, l.Sub_Code
>
>
>The query plan :
>
>PLAN SORT (SORT (MERGE (SORT (S NATURAL),SORT (JOIN (C NATURAL,L INDEX
>(IDX_POSTYPE))))))
>
>And the indices : (No indices on subsidiary tables)
>
>CREATE INDEX "IDX_DRYSTOCKSALES" ON "LOG"("TRAN_TYPE", "MISC_TRANS",
>"STOK_CODE", "SAL_REF", "POS_ID");
>CREATE INDEX "IDX_LOGSTOCK" ON "LOG"("STOK_CODE");
>CREATE INDEX "IDX_OPT" ON "LOG"("PAY_CODE", "PUMP");
>CREATE INDEX "IDX_POSID" ON "LOG"("POS_ID");
>CREATE INDEX "IDX_POSTYPE" ON "LOG"("POS_ID", "TRAN_TYPE");
>CREATE INDEX "IDX_TAX" ON "LOG"("POS_ID", "TAX_INDEX");
>CREATE INDEX "IDX_TRANDATA" ON "LOG"("TRAN_TYPE", "INV_TYPE", "POS_ID");
>CREATE INDEX "IDX_TRANTYPE" ON "LOG"("TRAN_TYPE");
>CREATE INDEX "IDX_UPDATELOG" ON "LOG"("RECEIPT", "RECEIPT2", "DESCRIP",
>"MQUANTITY", "POS_ID");