OraFAQ Forum: SQL & PL/SQL » 22 to 30% data need to be deleted from very large DWH tables (406,609,065 rows)

Home » SQL & PL/SQL » SQL & PL/SQL » 22 to 30% data need to be deleted from very large DWH tables (406,609,065 rows) (Oracle 10g R2 under UNIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

22 to 30% data need to be deleted from very large DWH tables (406,609,065 rows) [message #386339]

Fri, 13 February 2009 19:06

Kanagaraj Velusamy
Messages: 12
Registered: October 2005
Location: Bangalore

Junior Member

Hi all,
What could be the best solution for deleting 22 to 30% data from very large DWH tables (406,609,065 rows) in production.
Cheers
Raj

Report message to a moderator

Re: 22 to 30% data need to be deleted from very large DWH tables (406,609,065 rows) [message #386341 is a reply to message #386339]

Fri, 13 February 2009 19:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

>best solution
Best as measured by what metric; minimized elapsed time, minimized disk activity, minimized CPU cycles?

DELETE FROM ...... -- Is your only option

[Updated on: Fri, 13 February 2009 19:28]

Report message to a moderator

Re: 22 to 30% data need to be deleted from very large DWH tables (406,609,065 rows) [message #386343 is a reply to message #386341]

Fri, 13 February 2009 20:27

Kanagaraj Velusamy
Messages: 12
Registered: October 2005
Location: Bangalore

Junior Member

Hi Swan,

Thanks for your reply. Delete from table with where condition is
the only way. But main thing is performance issue.

we can think in this way also. Creating a new table with
valid records(those records not going to be deleted) and drop the orginal table then re name the new table to original table and creating all indexes and constaints. Like this what are the different ways we can use. I am thinking of good solution to avoid performance problem. I am expecting some possible solutions
from Oracle Experts and DBA from Orafaq.

Kind regards
Kanagaraj

Report message to a moderator

Re: 22 to 30% data need to be deleted from very large DWH tables (406,609,065 rows) [message #386344 is a reply to message #386339]

Fri, 13 February 2009 20:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

But main thing is performance issue.
Best as measured by what metric; minimized elapsed time, minimized disk activity, minimized CPU cycles?
PERFORMANCE is in and of itself 100% ambiguous!
If you won't answer my question why should I waste my time trying to answer yours?
Rank order the dimensions of "performance" in decreasing order of importance.

>Creating a new table with.......
Is data unavailability considered good or bad performance?

one possible "quick" method is partition exchange; if the data was partitioned.

Report message to a moderator

Re: 22 to 30% data need to be deleted from very large DWH tables (406,609,065 rows) [message #386409 is a reply to message #386344]

Sat, 14 February 2009 10:50

Kanagaraj Velusamy
Messages: 12
Registered: October 2005
Location: Bangalore

Junior Member

Hi Swan,

Thanks for reply.

I would consider all three items in order
minimized elapsed time,
minimized disk activity,
minimized CPU cycles.

Only for two hours 1 am to 2 am these table will not be accessed.
So with in two hours I will be able to delete all 20% of data. I did POC in testing environment. Delete from table where ROWID
in (select statement) takes long time.. It is not comming out for 7 hours then I killed the session. Please let me know what other information you want from me.

Kind regards
Kanagaraj

Report message to a moderator

Re: 22 to 30% data need to be deleted from very large DWH tables (406,609,065 rows) [message #386414 is a reply to message #386339]

Sat, 14 February 2009 11:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

All other things being equal, it should not take multiple hours to delete 50-125 million rows.

Something very unique to your environment exists which you are not providing us details; trigger, ON DELETE CASCADE, etc.

I refuse to continue with this guessing game.

enabling SQL_TRACE will reveal where actual time is being spent.

Report message to a moderator

Previous Topic:	How does oracle run sql
Next Topic:	Replacing a Null column value with the value of the above record

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Oct 07 12:52:28 CDT 2025