Re: Quicker way to Delete

From: Karth Panchan <keyantech_at_gmail.com>
Date: Thu, 18 Jul 2013 16:03:28 -0400
Message-Id: <473788A9-15A3-4483-85B9-064EA516D340_at_gmail.com>

Without downtime you can create table with (220-30) =210 million as Table_New from Table_Old

Build necessary indexes on Table_New

Drop Table_Old

Finally rename Table_New to Table_Old

If you can create Table_New with Single partition, you can do exchange partition from Table_New to Table_Old. This way you don't require rename table

HTH
Karth

On Jul 18, 2013, at 3:36 PM, "Bobak, Mark" <Mark.Bobak_at_proquest.com> wrote:

> 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_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
>
>

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

Received on Thu Jul 18 2013 - 22:03:28 CEST