> >Personally, I have never wanted a DELETE or UPDATE with LIMIT. The one
> >time I did something similar in Oracle, I used partitions, and just dropped
> >or truncated the partition containing the old data.
> >
>
> Yeah, that’s the proper way to handle the issue assuming that sufficient forethought was put into the design of the
database. It becomes trivial to drop a partition part of your weekly/monthly maintenance.
Well, sometimes it's not that easy. How would you handle a batch
processing system which stores the incoming requests in a queue table in
the data base, and then periodically processes a batch of it, with the
additional constraint that it is allowed to process at most 1000 at a
time so it won't produce a too long running transaction ? Suppose the
processing is quite costly, and the queue can have bursts of incoming
requests which then have to be slowly processed... the requests are
coming from the web and must be processed asynchronously, the insert
into the data base must be very fast.
Partitioning would be able to solve this kind of problem I guess, if one
processing chunk is one partition, and it is dropped after processed,
but it needs a whole lot of setup and I'm not sure how well it would
work with largely variable bursts of data... the number of partitions
could grow indefinitely, and there would be a race condition when
there's low traffic and we need to process an incomplete chunk (after a
maximum sleep timeout for e.g.) while there are still some incoming
requests we don't want to loose.
So what I'm talking about is not maintenance, but on-line operation...
Cheers,
Csaba.