RE: Quicker way to Delete

From: Powell, Mark <mark.powell2_at_hp.com>
Date: Fri, 19 Jul 2013 16:58:12 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD1186FC168_at_G6W2491.americas.hpqcorp.net>

Bala, this may be a dumb question but since no one should need to see these rows to be deleted, why the concern over how long the delete takes? Just write a little pl/sql to perform the delete with a commit every 10M of data size or so to limit the amount of undo related work other concurrent sessions will have to do and let the delete process run. I agree inserts and update tasks need to be fast, but deletes not so much in my opinion. Now I am not saying that inefficient code should be used or allowed to be used only that I do not see mass deletes as usually being a critical performance item. Rather I want a mass delete to be a low impact task.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Karth Panchan Sent: Thursday, July 18, 2013 4:03 PM
To: Mark.Bobak_at_proquest.com
Cc: oratips_at_gmail.com; oracle-l
Subject: Re: Quicker way to Delete

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


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

Received on Fri Jul 19 2013 - 18:58:12 CEST