** performance with delete

From: A Joshi <ajoshi977_at_yahoo.com>
Date: Sat, 14 Mar 2009 08:39:35 -0700 (PDT)
Message-ID: <793198.87435.qm_at_web57514.mail.re1.yahoo.com>


   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

Received on Sat Mar 14 2009 - 10:39:35 CDT

Original text of this message