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
Best Answer
This sounds like a job for Change Data Capture (CDC), which allows you to (among other possibilities) ship your archivelogs from the OLTP database to the reporting one, mine them for the changes, then query the changes out, ignoring any you don't want (e.g., changes of type 'D' for DELETE), and using whatever process you might devise apply those changes to your reporting tables.
I have no idea how well CDC would do with a ruleset encompassing 4700 source tables from another database. I've never used it for more than about 50 tables myself.
FYI, there are licensing-related limits on CDC. The full feature set is only available on Enterprise Edition.