Archiving OLTP

[Leojames22]

Folks,

This is a DBA as well as developer question. So I would like to get the input from both of you.

I am adminstring a 18 Gig OLTP database which growing fastly as the days pass by. Hence we need to arrive a solution to archive the old data to another database. The issue here is the customer asked us to incorporate both the database into the existing application. I mean the front end (VB). Right now the application access the only one database for all its record searches.

After we split the old data, for example say two years old data and create a arch_database, then the application should be inteligent enougth to look for that record from that database also.

I did talk to the developer I am working with. We are using an ini file to connect to the database. Since I don't know much in this area, I suggested a database link between these two database to trace the records.

250 users using this application. The current database in dedicated log in mode. By making a single DB link will suffice to serve all the queries going to arch_database?

Or is there any other way I can tackle this problem ?

Could you please suggest any sites I can refer to make a decision? Any books I need to refer? Any valuable comments will be highly appreciated.

We are using ORACLE 8.1.5 on SUN 2.6 with VB 6.0 front end.

Kindly ask me if I could provide more information.

Thanks

Leo james

[tombest]

I would use Oracle Partitioning. 18 GB is "small" these days. Not sure how fast it is growing, but if the app needs to sometimes access the "archived" data, then I would keep it in the same DB. Odds are, there will be times when the current and archived data need to contribute to the same query results. Moving it to another DB is going to make that harder to do.

Partitioning will allow you to keep the data in the same DB but still let the app perform as though there were only OLTP rows present. When you partition based on date, for example, any query against the table that qualifies the date column will allow the optimizer to eliminate some partitions, even for scans.

If a LOT of the data gets so old that the OLTP app doesn't access it anymore, then think about a data warehouse.

That's my opinion, anyway.

[Leojames22]

Thanks Tom,

We decided to split the table within the same database since there are not much tables to archive. So the table xyz will be split as
xyz_arch and xyz. We are already working on changing the code accordingly.

Thanks for your valuble info.

I am already working on partitioning the xyz_arch table.

Leo

[TomazZ]

If you are going to partition xyz_arch table anyway, I really cannot understand the need of separating data in two tables.
Performance overhead:
There will be no performance gain. Quite opposite, if you will have queries that need to access both data you can have significant performance overhead uniting it in one resultset.
Development overhead:
A lot more complexity will be involved in application development to control which data you access. Also, to overcome forementioned perfomance problems with uniting the two data sources in one result data set, additional complexity will be introduced in development.

Why?
Am I missing something?