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

  • How do you use "Materialized Views without applying deletes"? 0.5GB does not seem like a lot even if replicating between sites. Commented Oct 18, 2011 at 10:04
  • Our database team advise that they can configure the destination to not apply delete DML (the same team who don't like the 4700 tables rather than the amount of data) Commented Oct 18, 2011 at 10:47
  • 4700 is rather a lot of tables, I'd suggest testing with a dozen or so to get a feel for how long the whole batch. The MV 'not applying deletes' makes no sense to me unless they are rolling their own - I'd get back to them on that one. Commented Oct 18, 2011 at 11:59

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.