Re: Archiving data into another database

  • From: "Sandra Becker" <sbecker6925@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 31 Jan 2007 07:49:33 -0700

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

Other related posts: