RE: Is this Needed?

Donald,  

    Is this a needed activity, probably not as a whole, though I will agree that it's easier to export/import the entire schema. You may have one or more tables that have been heavy insert and delete that are not chained per say, but have a lot of wasted space therein. Now the maintenance action that's being suggested will clean that up, to what other purpose than reclaiming disk space I don't know. We have a very large table in PeopleSoft that gets archived periodically. A typical archive run will eliminate 40% to 50% of the rows in the table & we do export & reimport that table every time they archive just for the purpose of reclaiming space in the tablespace. They use the utilization rate of the tablespace as an indicator of having to run the archive process. I don't agree with the methods, but it does work.  

    Now, just like our little monster you've probably got people complaining about performance, and someone has made a decision based on that complaint and what they believe is the root cause. Shame, good thought, bad decision. We also had those problems, but after reviewing/capturing sql statement that ran against that table we found the true problem which was a misused index. BTW: since I didn't already mention it this application runs in RULE mode. So, what good is an index on a table that is going to return 80% of the data rows, none that's what. We had a serious discussion with the application developers & they did some work. End result no one complains about this table, until the tablespace utilization in up in the 90% range at which time they believe it's time to archive again.  

    And for those who don't think that old thinking is still not around, here's something from a vendor that I got recently:  

    "Anything over 100 for an index is a concern "  

Old beliefs die hard.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Freeman, Donald Sent: Wednesday, October 26, 2005 11:19 AM To: Christian Antognini; Freeman, Donald Cc: oracle-l_at_freelists.org
Subject: RE: Is this Needed?

I just checked and code tables are set to 10% while key application tables are set to 30%. I'm pretty sure these aren't going to be changed. I don't think there is any row chaining there. What are you getting at?  

Don Freeman
Database Administrator 1
Bureau of Information Technology
Pennsylvania Department of Health
717-783-8095 Ext 337

	-----Original Message-----
	From: Christian Antognini [mailto:Christian.Antognini_at_trivadis.com]
	Sent: Wednesday, October 26, 2005 11:05 AM
	To: dofreeman_at_state.pa.us
	Cc: oracle-l_at_freelists.org
	Subject: RE: Is this Needed?
	
	
	Don



	> Is this a waste of time?

	 
	As usually... it depends. IMHO there are three cases:
	- you waste time
	- you improve performance
	- you decrease performance
	 
	Of course the third one is the real problem! And about it... a simple question: how you will set PCTFREE before inporting the data?
	 
	 
	Regards,
	Chris
	 
	New Features Oracle Database 10g Release 2 seminars @ www.trivadis.com <http://www.trivadis.com> 
	Italiano: Lugano (24-Nov)
	Français: Genève (17-Nov)
	Deutsch: Düsseldorf (23-Nov), Zürich (29-Nov), Stuttgart (13-Dec)

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