Skip to Main Content
  • Questions

  • Advanced Replication using Materialized Views

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, David.

Asked: February 15, 2013 - 7:44 am UTC

Last updated: February 15, 2013 - 10:05 am UTC

Version: 11.2.2

Viewed 1000+ times

You Asked

I need to replicate a subset of table data from ebusiness suite to another database that is used for specific reporting purposes. Currently, the only requirement is to replicate a subset of data from 6 EBS tables, average peak of 1000 transaction per day. The type of relication I choose will set the basis for future replication (not exceeding 100k transaction per day). Business requirements will drive how often the data is replicated throughout the day. My questions are below:

1: Will advanced replication with materialized views meet both my current and future requirements? Materialized views will be READ-ONLY with FAST REFRESH.

2: At what point will advanced replication using materialized views start to degrade or have a negative impact on performance of the Master site.

3: At what point (transactionally) should I consider other forms of replication (e.g. streams)?

4. Are there any case studies that I can refrenece to support my decision?

Thanks in Advance

and Tom said...

1) you will have to ask if the creation of materialized view logs on the EBS tables is supported (I don't do apps - I don't know offhand if that is permitted).

If it is not, a fast refresh materialized view will not be possible.

If it is, then you just need basic replication, not advanced - and it would work.

2) the materialized view log will impact the insert/update/delete processing against the tables they are part of. Those actions will require more work - in order to maintain the materialized view logs. Typically, this is very light - especially since you do a max of 1,000 transactions per day - very light load.

Then there is the materialized view refresh, the application of the materialized view logs to the remote databases. This processing will depend on the complexity of your materialized views and is something you would have to measure (but again, you have such a lightly used system, it probably won't be very large)

3) that would be a personal decision based on benchmarks in your environment with your workload. If you had the need to be zero impact on or as near zero as possible - streams with downstream processing would move virtually all of the work off of the production machine.

Also, it would not require the creation of materialized view logs, so depending on what you hear from support in #1 above - it may be the way to go.

4) it really depends on your workload, your transactions. Architecturally - streams and materialized views are very different (understand their architectures, how they are processed). Materialized views would have to be supported by EBS, would impose an overhead for every transaction processed, and would impact the production server additionally during the refresh times. Streams - will not (does not have to with downstream processing). However, streams will be more complex to setup and administer over time (materialized views are pretty simple)


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

MATERIALIZED VIEW LOG in other schema

Vikas Sharma, January 05, 2014 - 3:20 pm UTC

Hi Tom,

I have 2 schemas. schema1 and schema2. have a table myorders in schema1. ie. schema1.myorders.

I would like to create a MATERIALIZED VIEW LOG on schema1.myorders in schema2. is it possible?

I checked oracle documentation but could not find any answer.

Regards,

Vikas Sharma
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.