At 01:46 PM 2/07/2003 +0200, you wrote:

>You're forgiven everything in advance ... :-)
>
>All tables have a primary key.

yes...and? you should have one index on your log table that corresponding
exactly to the PK of each table that you join to. So, in this query
(putting aside the FOR for now, as I have some thoughts about it):

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 <<<==== Index L.acc_num
JOIN clnt_sub S
ON S.code = L.acc_num <<<=====another index needed
AND S.sub_code = L.sub_code over these two columns
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

Index all the group and ordering columns EXCEPT those that have low
selectivity.

>The result set is used for printing reports for the end users.

OK, so the FOR indicates that you are doing this in a SP. Is outputting
rows for the report the only task happening in the SP?
I still can't see why you have all this grouping. The only aggregates you
will get here will be when the same client returns multiple times in the
same shift, right?

>There can be up to 3 or 4 POS_IDs and 13 different TRAN_TYPES

Then get rid of both of these because their selectivity is too low for
indexing.
CREATE INDEX "IDX_POSID" ON "LOG"("POS_ID");
CREATE INDEX "IDX_TRANTYPE" ON "LOG"("TRAN_TYPE");
CREATE INDEX "IDX_POSTYPE" ON "LOG"("POS_ID", "TRAN_TYPE");
plus all over those other indices where you will have large numbers of
duplicates.

(13 would be arguable if all 13 possible values were evenly distributed
through the table but, because of other discussions you and I have had
about this app, I suspect that most transactions will be of one or two
types...)

>The indices are an attempt to speed up other queries and SPs

All of the indices you defined here will make queries slower, not faster.

>There are no other indices on the LOG table.

Drop all of the indices and then go back and make new ones which actually
have uses. You need to have indices on both sides of joins if they are of
reasonably high selectivity. So, for example, a unique client code on the
left side (index it if it is not the PK) and the acc_num it is joining to.

Then, each of your order by columns should be indexed.

And, because you are obtaining this set via a SP anyway, I'd want to
experiment with nested FOR loops, cuz I don't think GROUP BY is what you
want here.

heLen