2 Replies Latest reply: Feb 4, 2014 8:18 PM by user591655 RSS

Materialized view fast refresh or complete refresh?

d549d669-f28b-4e9d-a599-f2f013d34056 Newbie
Currently Being Moderated

Hi Guys,

 

Need help here.

We were ask to provide recommendation regarding the replication of our Production schema to Reporting Schema.

Here are the conditions:

1. Production schema and Reporting schema resides in one database server, only in different instance.

2. Tables to replicate is more or less 300 tables.

3. They only want to replicate changes that happen on the table on the said day.(insert,update)

4. Replication will only happen on the end of business hours(end of day).

 

What we have in mind is Materialized view.

What would be best to use, fast refresh or complete refresh?

What will be the effect on performance?Resources?

Or will there be any other method?

Appreciate your reply on this.

 

Thanks,

Nina

  • 1. Re: Materialized view fast refresh or complete refresh?
    spajdy Pro
    Currently Being Moderated

    Using MV (Materialized views) is onle of the possible way how to do what you are asked for.

    Fast refresh of MV is faster than complete refresh because it's based on "deltas" from last refresh. But it require MV log on table in primary DB. This MVLog consume same space in tablespace. And maintainig this MVlog during DML operation on table add some overhead to DML operation -> it's take a little bit longer time to complete.

    How much space is consumend by MV log depends on many factors:

    1/ number of DML changes on table

    2/ number of consumers (there can be more than on MV based on same table using same MV log)

    3/ period of MV refresh

     

    Plus of using MV is that it is very simple and you don't need no special license to use them.

    Other possible option is Golden gate (Streams previously)

    Next option is procedural replication - you have to write your own replication mechanism.

  • 2. Re: Materialized view fast refresh or complete refresh?
    user591655 Pro
    Currently Being Moderated

    Hi,

     

    Also you can refer below article which may help you.

     

    MATERIALIZED VIEW REFRESH: Locking, Performance, Monitoring (Doc ID 258252.1)


    Thanks,


Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points