Re: how to delete parts of data of a very large table quickly?

Umm. Rather than create a table, insert rows, truncate original table, put rows back and then get rid of temp table, you'd be much better off using CREATE TABLE AS SELECT (NO LOGGING / PARALLEL - but understand the implications of both) to build a new table containing just the rows you want. Drop the original table and rename the new one to the original name.

I'm pretty sure a non APPENDing insert will generate just as much rollback as a DELETE.
As I understand it, CREATE TABLE AS SELECT creates minimal if any rollabck.

Phillip wrote:
>
> the best way may be to create a temporary table and insert the rows you want
> saved from this table into the temporary table. Then truncate the original
> table, and reinsert the rows from the temporary table. The 'truncate' command
> will bypass any redo activity and will be much faster than a normal delete.
> Also keep in mind that there's no rollback from a truncate, so if you execute
> this command, make sure that this is what you want.
>
> zhangguoping_at_boco.com.cn wrote:
>
> > i have a very large table, i want to delete some data. The data to be
> > deleted is also very large. i am afraid rollback segment may suffer from it.
>
> --
> Phillip
Received on Thu Apr 24 2003 - 02:56:01 CDT