> >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.
