Re: ** Want a new DELETE command **

Steve Corbett <stevec_at_fcs.wa.gov.au> wrote:
>you can do this by building a pl/sql loop, eg:
>begin
>loop
> delete table_name
> where rownum < 1000;
> exit when sql%notfound;
> commit;
>end loop;
>end;

Yes, I should have said that I knew that PL/SQL was the only current answer. But wouldn't it be nice to have more help from SQL where large amounts of redo-logs are concerned?

My current problem is I'm trying to purge old information from a very large table with many indexes against it. To keep the redo down, I blow away the indexes after a temporary table has been built (with UNRECOVERABLE) to store the rowids of the rows in the original table that I want to delete.

Now the problem is removing say 100,000 rows based on the rowids that I've already collected, then perform a COMMIT. I need to do this over and over until all the specified rows are gone. Just specifying ROWNUM won't help. There are specific rowids that must be deleted.

ANYWAY, how difficult could it be to create a DELETE command in SQL to allow for a COMMIT to happen every so many rows? I know, it has to pass the stinkin' SQL committee in the sky.

• Doug Anderson Oracle Database Administrator DAtheDBA_at_mindspring.com