RE: ** performance with delete

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sat, 14 Mar 2009 12:12:11 -0400
Message-ID: <32335937CE9E4A8AABDE980D22F16202_at_rsiz.com>

Partitioning and never deleting is a good solution to this problem. Whatever validates a row for delete is used to omit that row from copy-forward to a new partition (or a catch-all of all oldish rows which is itself periodically winnowed.) After a partition reaches (drops to) your chosen percentage of rows remaining, then the "not valid for delete" rows are copied forward and you exchange the partitions. True there is some administration not required of delete, but it is far cheaper. You might still want exp for archival reasons or the ability to load data for analysis somewhere else, but I'm not sure what the point of the imp would be. Certainly the aggregate of administrative issues and possible miniscule downtime with the partition swapping is far far less than an import. You can beat exp/imp with a decently constructed "poor man's" partitioning with multiple tables representing the partitions and union all with synonym rotation.  

Regards,  

mwf  

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of A Joshi
Sent: Saturday, March 14, 2009 11:40 AM
To: oracle-l_at_freelists.org
Subject: ** performance with delete  

Hi,

   I have a choice to do delete of rows for tables in OLTP db on 10g on sunos on ongoing basis or do it once in six months. The % of rows deleted for the tables varies and is upto 50% of rows for some tables. The tables do get new rows steadily either way. Either way I plan to do exp/imp of the table once in six months. After exp/imp I think it will be same either way and performance will be fine at that point. Question is how will the performance be prior to the exp/imp for the six months or so. Anyway to check or take action based on checks. Using method 1 : deletes on ongoing basis. Using method 2 : do the deletes in one shot at end of six months. Is one preferable over the other or is there criteria or 'depends'. The tables do have multiple indexes and used extensively. Clustering factor for the indexes is one criteria to be considered. I am thinking index access and index size would behave differently compared to table access. I am trying to figure out the table access being done by application/jobs. I can also use shrink command in between but do not know if it has bugs or any issue with it. To be safe exp/imp or move is planned. Thanks  

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

Received on Sat Mar 14 2009 - 11:12:11 CDT