Helen,
as usual most of your advice is brilliant, but when it comes to the indexes
you want him to create, I suggest it is better to create them on the other
tables (as I already suggested in my first mail, though I errouneously
forgot to include sub_code in one of them). The reason is that he already
has a where clause covering the log table and that what we want is for him
to use one index for his where clause and then one index on each table to
link to.

Set

At 23:03 02.07.2003 +1000, you wrote:

>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 (SET: Indexing C.code would
>be better)
>JOIN clnt_sub S
>ON S.code = L.acc_num <<<=====another index needed
>AND S.sub_code = L.sub_code over these two columns (SET:
>preferrably s.code, s.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