Re: Quicker way to Delete

From: Tim Gorman <tim_at_evdbt.com>
Date: Thu, 18 Jul 2013 14:16:47 -0600
Message-ID: <51E84D2F.7020907_at_evdbt.com>

If you've got 40 mins to spare, then there is "http://www.youtube.com/watch?v=pvbTAgq_BBY" on this topic from last year's Oak Table World, held next-door to Oracle Open World.

If you don't have 40 mins, then I'm glad to send you slides off-line, if you like?

On 7/18/2013 1:36 PM, Bobak, Mark 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

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

Received on Thu Jul 18 2013 - 22:16:47 CEST