Re: Delete without rollback

In article <01bd8354$aff06220$a12c6394_at_J00679271.ddc.eds.com>, "Mark Powell" wrote:

What if the poster of the question does not want to remove all of the rows in the table?

If, you have 110,000 rows and wish to delete 100,000 of them, I would try creating a temporary table for the 10,000 rows, truncating your table and re-inserting the rows. Check the timings.

e.g.

create table temp_table as select * from your_table where ...; truncate your_table;
insert into your_table select * from temp_table;

Oh, and you either need to be the table owner, or have the DROP ANY TABLE (after 7.2) to allow a TRUNCATE

Finally, if you have 1,000,000 rows and wish to delete 100,000, make sure you have a large rollback segment which preferably will not dynamically extend beyond 20 extents and have large redo logs and redo_log_buffers.

regs

Neil Chandler

>The command you want is called 'truncate' You must be the owner or have
>'delete any table' privilege.
>
>x <x_at_x.com> wrote in article <355E1903.28A0_at_worldnet.att.com>...
>> Could some on please help me with.
>>
>> I need to delete 100000 rows from a very large
>> table in a SQL*Plus session.
>>
>> Is there a way to disable the rollback feature so
>> that the delete will be faster.
>>
>> I am looking for something similar to UNRECOVERABLE in
>>
>> "create table test1 UNRECOVERABLE as
>> select * from test"
>>
>> Thanks
>>
Received on Wed May 20 1998 - 05:04:15 CDT