Re: Delete without Rollback ?

In article <35C85724.CCE4EA38_at_a.com>,
  John Finn <a_at_a.com> wrote:

> Does anyone know how to Delete without Rollback transaction overhead?
> "DELETE FROM TABLE1 WHERE ..."
> and no transaction overhead, rollback, commit ...

If you want to get rid of all the data, use "TRUNCATE". If (as I suspect) you want to use a "WHERE" clause on a very large table, it may be worth doing the following...

SQL> CREATE TABLE TEMP_COPY UNRECOVERABLE AS SELECT * FROM TABLE1 WHERE...
SQL> DROP TABLE TEMP_COPY;
SQL> RENAME TEMP_COPY TO TABLE1;

This is a bit drastic, but is probably the fastest way for a very large table. Note that because you have dropped the table, you will have to recreate any indexes and re-grant any privs. on the table.

Make sure that you understand the implications of using UNRECOVERABLE, make sure you have a backup & be very, very careful when dropping any important tables!

An alternative to using "DROP... RENAME" would be to "TRUNCATE... INSERT FROM TEMP_COPY...", (as somebody else has suggested) but the insert would have redo & log overhead, so may not be acceptable to you.

Regards
Yuri McPhedran

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Aug 06 1998 - 08:36:50 CDT