We are about to deploy a 3rd party product running on Oracle 11gR2. The product allows very generic text searches. We'd like to mitigate the performance impact of the searches.
Our current design is to create an OLTP database holding 32 days of data and a archive/reporting one holding up to 7 years. Then, we replicate data between them every 24 hours using Materialized Views without applying deletes. This isolates the impact of searches.
However, our database team has raised concerns that MV's are not the right solution as the 3rd party product creates 4700 tables and we'll be replicating approx. 0.5GB data per day.
Any view on the validity of the concerns?
(we are currently looking at alternatives - no archive, 7 years data in OLTP and partitioning to lessen the impact of search)
Thanks in Advance, Matt