Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
How do you use "Materialized Views without applying deletes"? 0.5GB does not seem like a lot even if replicating between sites. – Jack Douglas♦ Oct 18 '11 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) – Matt Sinfield Oct 18 '11 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. – Jack Douglas♦ Oct 18 '11 at 11:59

1 Answer

up vote 2 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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