Oracle Database Discussions

Hi,


We are building an application where data in the current year will be stored in a database(current_database) and the data of the past years will be stored in another database (history_database);becuase we fear that the data in some of our tables may be more than 50 millions rows over a period.hence this approach of purging the history data into another database.


Our business is highly client oriented (client ID present in tables or is the
main given field for our table joins).
Regarding the estimated size of the db in 5 years, and the response
performances we need to ensure, we are looking to the possibilities to improve
the current data model.
Today we have one schema with none partitionned tables.

Then we are studying 3 possibilities:

- Separate our schema into multiple ones: one for current data & another for history data.
- Have separate database for current & history data.
- Or implement partitionong scheme.

Note that in some scenarios(not the majority but...), we may have to do
multi-client/all-clients queries, meaning if we choose multi-schema/table that
we need do an union of queries on several schemas/tables.

What are the PRO/CONS of each solution?
- concerning overall database disk space?
- query retrieval? knowing that we have many scenarios of table access by
indexes and few by table scan. Knowing as well that we could parallelize the
heavier queries.
- maintenance (remove data older than 5 years, managing disks etc...)
- Performace impact while reporting or data retrieval.
- maintainence (backup ,recovery,upgrades,patches).

Thanks for your support in advance!

Ram