This discussion is archived
8 Replies Latest reply: Feb 25, 2013 2:40 AM by 905989 RSS

Materialized View consistency, replication

905989 Newbie
Currently Being Moderated
Hi,

Oracle 10.2.0.4 on Solaris



We added a new column to the source table that has around 100Million rows with 16 indexes. This is the transactional table.

We have a reporting database using the same schema as primary via Materialized View (MV) replication. The same table was altered on replicate as well.

The MV was dropped and recreated to include the new column. It was created as follows:
CREATE MATERIALIZED VIEW "SCHEMA"."TABLE"
ON PREBUILT TABLE WITH REDUCED PRECISION
USING INDEX
REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT sysdate + 30/(60*60*24)
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT "TABLE"."COL1" "COL1",
.......
               "TABLE"."NEWS_COLUMN" "NEW_COLUMN"
FROM "SCHEMA"."TABLE"@SOURCE.WORLD "TABLE"
The MV was refreshed using the following script:
exec DBMS_MVIEW.REFRESH('SCHEMA.TABLE','C',ATOMIC_REFRESH=>false);
Which meant that the MV did a complete refresh of the replicate table.

We specified complete refresh to ensure that replicate table data was consistent, obviously accepting that synching replicate table with 100 Million rows takes a good while.

At some point data refresh itsdelf was completed but indexes on replicate were being built. So hence my questions

1) If a new record is added to the source table after data refresh is completed with MV , then that will end up in MV log in the source database and will be synched after the next refresh run of MV. That should have no impact to the replicate table and indexes being built
2) How indexes via MV are built on the replicate table? Logically one would think that once the data in the replciate table in refreshed, indexes will be built based on the replicate table. In other words indexes are built (as usual) based on the data on the replicate table and are not refreshed from primary across. The complete MV refresh just refreshes the data and the replicate table indexes are then rebuilt. Is this correct
3) if we killed the MV refresh process, then the worst scenario would be to restart the complete refresh of the MV again?
4) In general whatever one does on the MV on the replicate site should have no impact on the source table.

Thanks
  • 1. Re: Materialized View consistency, replication
    TSharma-Oracle Guru
    Currently Being Moderated
    1) Correct
    2)One Primary index will be built if you will create index WITH PRIMARY KEY Clause. You have to create all other indexes.
    3) In your case where Atomic_refresh=False, Table will be truncated and then inserted, So if you kill the refresh job after the truncation step, there will be nothing in the table and YES you have to do a complete refresh after that. But If you use Atomic_refresh=True, then when you restart your job, it will do only the FAst refresh.
    4)Correct.
  • 2. Re: Materialized View consistency, replication
    JohnWatson Guru
    Currently Being Moderated
    TSharma wrote:
    1) Correct
    2)One Primary index will be built if you will create index WITH PRIMARY KEY Clause. You have to create all other indexes.
    3) In your case where Atomic_refresh=False, Table will be truncated and then inserted, So if you kill the refresh job after the truncation step, there will be nothing in the table and YES you have to do a complete refresh after that. But If you use Atomic_refresh=True, then when you restart your job, it will do only the FAst refresh.
    4)Correct.
    Surely the (3) description is a mistake? There is no reason for the table to be truncated during a fast refresh. If the refresh fails, all that will happen is that any updates that did get applied will be rolled back so the MV remains as it was. Since the procedure call is refreshing only one materialized view, atomic_refresh true or false makes no difference.

    Apart from that, this
    The complete MV refresh just refreshes the data and the replicate table indexes are then rebuilt.
    is not quite correct: no indexes are rebuilt, they simply maintained during the refresh operation.
    --
    John Watson
    Oracle Certified Master DBA
    http://skillbuilders.com

    Edited by: JohnWatson on Feb 24, 2013 6:41 PM
    My apologies, I see that the procedure call specifies a 'C' complete refresh, it is the view definition that says 'fast'. But the use of atomic_refresh is not relevant.
  • 3. Re: Materialized View consistency, replication
    905989 Newbie
    Currently Being Moderated
    Hi John,

    Many thanks for the info. One point if I may:

    We used the following refresh on one MV only
    exec DBMS_MVIEW.REFRESH('SCHEMNA.TABLE','C',ATOMIC_REFRESH=>false);
    ‘C’ for complete refresh. In this case where Atomic_refresh=False, Oracle would do a TRUNCATE and INSERT /*+APPEND*/, which is more efficient (with no activity on the replicate) than delete and insert (fully logged, defailt 10g behaviour). DBA also reported that indexes were built one after another, like 10/18, 11/18 etc. Is this a valid statement?

    Thanks again
  • 4. Re: Materialized View consistency, replication
    JohnWatson Guru
    Currently Being Moderated
    902986 wrote:
    Hi John,

    Many thanks for the info. One point if I may:

    We used the following refresh on one MV only
    exec DBMS_MVIEW.REFRESH('SCHEMNA.TABLE','C',ATOMIC_REFRESH=>false);
    ‘C’ for complete refresh. In this case where Atomic_refresh=False, Oracle would do a TRUNCATE and INSERT /*+APPEND*/, which is more efficient (with no activity on the replicate) than delete and insert (fully logged, defailt 10g behaviour). DBA also reported that indexes were built one after another, like 10/18, 11/18 etc. Is this a valid statement?

    Thanks again
    I believe so. But the atomic_refresh is not relevant when refreshing only one view.

    Edited by: JohnWatson on Feb 25, 2013 8:27 AM
    Correction: atomic is not relevant for fast fresh of one view, full refresh is different.
  • 5. Re: Materialized View consistency, replication
    rp0428 Guru
    Currently Being Moderated
    >
    But the atomic_refresh is not relevant when refreshing only one view.
    >
    What is that statement is based on? You've said that twice now. Please post something that supports that statement.

    The atomic_refresh parameter is definitely relevant as far as I can tell.

    I can't find anything about the parameter behaving differently based on the number of views being refreshed.

    If set to TRUE (the default) Oracle will do a DELETE followed by an INSERT.

    If you set it to FALSE you will get a TRUNCATE followed by a direct-path INSERT (insert with an APPEND hint after the table is locked.

    I just tested this example from 5 years ago in AskTom and reproduced these results in vanilla versions of both 11.2.0.1.0 and 10.2.0.1.0
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:616795500346622064
  • 6. Re: Materialized View consistency, replication
    TSharma-Oracle Guru
    Currently Being Moderated
    Since the procedure call is refreshing only one materialized view, atomic_refresh true or false makes no difference.
    I totally disagree with you. When ATOMIC_REFRESH=False, your data will be truncated and Data will not be available for that period of time. When ATOMIC_REFRESH=True, Data will be deleted and then inserted so if anything happens then Oracle will rollback the data but you cannot roll back the truncate.
    no indexes are rebuilt, they simply maintained during the refresh operation.
    I agree with you in this case. I was thinking after 1st complete refresh.
  • 7. Re: Materialized View consistency, replication
    JohnWatson Guru
    Currently Being Moderated
    TSharma wrote:
    Since the procedure call is refreshing only one materialized view, atomic_refresh true or false makes no difference.
    I totally disagree with you. When ATOMIC_REFRESH=False, your data will be truncated and Data will not be available for that period of time. When ATOMIC_REFRESH=True, Data will be deleted and then inserted so if anything happens then Oracle will rollback the data but you cannot roll back the truncate.
    I was referring to fast refresh. The view is fast refreshable, and defined on a pre-built table, so it should never be full refreshed. Sorry about that.
  • 8. Re: Materialized View consistency, replication
    905989 Newbie
    Currently Being Moderated
    Thank you all

    Very helpful indeed

Legend

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