If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: [seeking advice] Handling large amounts of archived data

I have some 20 years of data coming down the pipeline, which I will need to store in an Oracle Database. This is expected to be huge (hundreds of gigabytes).

This database will house all the data, as well as new data.

The problem is reports. Half of the company needs reporting off of all the data, while the other half only needs current data (this has been defined as data from 2005 and up).

My original thought is to create 2 databases, one for archived, and one for current, then create some union-views for people to report off of. This way, half the company can run their full reports on the views, while the other half can run quicker reports on the current database only.

Store your data in partationed tables. This will allow sub-sets of the data to be queried by date. A quick and dirty would be to make a partation for each year. Only those partations that contain data for the year(s) you specify will be queried.

Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.

Store your data in partationed tables. This will allow sub-sets of the data to be queried by date. A quick and dirty would be to make a partation for each year. Only those partations that contain data for the year(s) you specify will be queried.

That would certainly solve my problem, since I won't have to create views joining 2 identical tables.

But what about overhead and performance? Will the users who need current data suffer?