Re: Archiving data into another database

In our case, prior to my arrival at the company someone told management that archiving the historical data that is rarely referenced would help performance. They also said it had to go into another database. A recent survey of our support personnel and our customers indicates that data older than three years in our largest tables is rarely used, but it is used. I personally believe that archiving this data to an archive table in the same database would be sufficient for our needs. Disk is disk no matter what database it belongs to and if the data is rarely accessed, the load on the current database would be minimal. My lead developer tells me it would be much easier for him to retool the application for another table in the same database than a table in another database. Based on my observations, it also definitely would be easier for the ad hoc queries our support personnel run on occasion. We are looking at roughly 300G of data that would be considered for archiving based on managements' current, ill-formed plan.

I, on the other hand, believe that tuning our extremely poorly performing SQL and having more appropriate indexes will give us a better ROI of my time. Some training for developers and support personnel wouldn't be out of the question either. Once we've cleaned up our code and indexes, defining an archiving strategy would be next on my list. Correct me if I'm wrong here, but knowing what you want to archive and why would need to be ascertained before attempting to actually archive anything. I'm also a proponent of having fully tested procedures in place so we know with certainty that we are archiving the data before deleting it from the primary table and that the application has been changed to correctly access the data when needed. Based on some recent successes I've had in migrating the database to a new node and tuning some critical SQL, my credibility is extremely high right now. I just want to make sure I'm headed in the right direction, hence the request to see what others have done.

Sandy

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