Re: SQL Without transaction context

Filip

There are two ways to remove data from a table. Using the delete command generates undo, as you've found. Using the truncate command does not, but it has two drawbacks:

1. It deletes all the rows in the table. If you want to keep a small number of rows (with small being defined in the context of the overall number of rows in the table), you can create table as select the rows you want, then truncate the original table, then insert into the old table as select the copied rows from the new table. Both the CTAS and the insert can be performed in parallel to improve performance.
2. Truncate is a DDL command. It can't be rolled back, so if you decide you didn't want to do that you will need to recover the database.

HTH. Pete

Filip Hanik wrote:

> Let's say I want to delete a couple of million rows in a database.
> I don't want my rollback segments to grow and I don't care for a rollback no
> matter what happens.
>
> Is there a syntax in PL/SQL to accomplish this?
>
> thanks
> Filip
>
> --
> Filip Hanik
> System Architect
> Digital Workforce
> fhanik_at_digitalworkforce.net

--
Regards

Pete

Received on Tue Jun 29 1999 - 15:57:46 CDT