A 7.3.4 question...

I want to "expire" some data after 90 days, but not delete too
much at once so as not to overwhelm a system with precariously
balanced disk I/O and on a table with millions of rows.  If I 
could say it the way I think for a simple example, it'd be
like this:

        delete from mytable
        where posteddatetime < now() - '90 days'
        limit 100;

Of course, that's not legal 7.3.4 syntax.  These are both too
slow due to sequential scan of table:

        delete from mytable where key in (
                select key
                from mytable
                where posteddatetime < now() - '90 days'
                limit 100);
or 
        delete from mytable where exists (
                select m.key
                from mytable m
                where m.key = mytable.key
                  and m.posteddatetime < now() - '90 days'
                limit 100);

Tried to use a cursor, but couldn't figure out the syntax
for select-for-delete yet, or find appropriate example on
google.  Any clues?

TIA.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to