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)
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 Go to next message
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
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 Go to previous messageGo to next message
BlackSwan
Messages: 25048
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 Go to previous messageGo to next message
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

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 Go to previous messageGo to next message
BlackSwan
Messages: 25048
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.
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 Go to previous messageGo to next message
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
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 Go to previous message
BlackSwan
Messages: 25048
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.


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


Current Time: Fri Dec 09 09:47:49 CST 2016

Total time taken to generate the page: 0.12880 seconds