Arno Brinkman wrote:

>>UPDATE ACTIONS U
>>SET U.SERVED = U.SERVED / 7
>>WHERE U.TICKET_REF IN
>>( SELECT A.TICKET_REF FROM ACTIONS A
>>GROUP BY A.TICKET_REF
>>HAVING COUNT(A.TICKET_REF) = 7 )
>>
>>Is simple enough, hopefully you can see where I am going ( I
>>want to run it for 6 down to 2 as well ;) ), and I'm not too
>>concerned about the time it takes - except after 3 hours it
>>had not finished!
>
> Could you show us your PLAN please.

PLAN (A ORDER ACTION_TIC)
PLAN (U NATURAL)

But now it is working correctly!
About 30 seconds for the '2' case.

All I did was wiped the table, reset the generator, reloaded
using my 'selection' script, and ran the update directly as
above. No different from what I did yesterday morning -
except that the index (ACTION_TIC on TICKET_REF ) was added
after populating the table yesterday, but was in place when
the latest extract was carried out.

> And in fact don't you want to do this then instead of running 6 queries :
>
> UPDATE
> ACTIONS U
> SET
> U.SERVED = U.SERVED /
> (SELECT Count(*) FROM ACTIONS A WHERE A.TICKET_REF = U.TICKET_REF)

Only reason I did not run that at first was that 80000 of
the 140000 records the count is 1, so I was just trying to
remove unnecessary updates!
I was simply surprised when my 'obvious' approach failed.

*****
Helen

> Errm, yes...actually reminds me of a puzzle hereabouts a couple of years
> ago. The problem is, your operation is inherently recursive because your
> metadata doesn't provide you with any way to know whether the value has
> already been processed. It just gets into a tighter and tighter spiral,
> heading towards infinity in ever-decreasing nibbles until it finally
> expires from hyperventilation.

I can't see how this could be recursive. All I am asking it
to do is update a list of records - and the fact that it is
now working would suggest that I was correct with the
original SQL.

Now how on earth do I find out why it was not working for
the last 24 hours :)

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services