** 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

Other related posts: