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");


[Non-text portions of this message have been removed]