Re: Quicker way to Delete

If you can tolerate downtime:

Create table temp no logging as select * from tab_w_220m_rows where <conditions specifying rows you want to keep>; Truncate table tab_w_220m_rows;
For each index on tab_w_220m_rows, do:
Alter index <index_name> unusable;

Alter table tab_w_220m_rows nologging;
Insert /+* append */ into tab_w_220m_rows select * from temp;

For each index on tab_w_220m_rows, do:
Alter index <index_name> rebuild nologging;

Hope that helps,

-Mark

On 7/18/13 3:28 PM, "Bala" <oratips_at_gmail.com> wrote:

>Software version : Oracle 11gR2 (11.2.0.3) RAC on Redhat 6
>
>On Thu, Jul 18, 2013 at 3:27 PM, Bala <oratips_at_gmail.com> wrote:
>
>> Gurus,
>>
>> Any quicker way to delete 30 million records (based on condition) from a
>> table with 220 million records ?
>>
>> Any pointers greatly appreciated.
>>
>> Thank you all for your time.
>>
>> --
>> Bala Rao
>>
>
>
>
>--
>Bala Rao
>
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l