Re: Quicker way to Delete
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "oratips@xxxxxxxxx" <oratips@xxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 18 Jul 2013 19:36:58 +0000
As with all things, it depends.
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@xxxxxxxxx> wrote:
>Software version : Oracle 11gR2 (11.2.0.3) RAC on Redhat 6
>
>On Thu, Jul 18, 2013 at 3:27 PM, Bala <oratips@xxxxxxxxx> 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
>
>
>--
>//www.freelists.org/webpage/oracle-l
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
