Re: Chunk Delete

On Thu, Nov 15, 2007 at 02:46:09PM +0100, Alexander Staubo wrote:
> On 11/15/07, Abraham, Danny <danny_abraham(at)bmc(dot)com> wrote:
> > With Oracle we do it with: delete ,tname> where <cond> and rownum < Y;
>
> You could create a temporary sequence:
>
> create temporary sequence foo_seq;
> delete from foos where nextval('foo_seq') < 50000;
>
> I'm not sure how fast nextval() is, even on temporary sequences; but
> it should be reasonably fast.

That's not going to do anything very useful after VACUUM has been run
will it? VACUUM will leave lots of empty slots within a page, that
subsequent INSERTs will populate. I suppose that you could cluster the
table first on some data column, and then do your delete trick. But
If the OP really has got a billion rows they're not going to want to
cluster it very regularly.

This basically goes back to the fundamental issue that a relation has no
implicit order (it sounds as though Oracle had an implicit one, but that
assumption doesn't hold with PG or in general).

Maybe partitioning could help here.

Sam