Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: partial Mview refresh

    I have an mview which will be refreshed daily. But for the first time its going to be a complete refresh and the second time onwards it will be a specific refresh(say for 2 months) leaving the data older than 2months without refreshing.

    How can we refresh only specific data?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You'll need a FAST REFRESH materialized view, which will refresh only data which have changed since the last refresh (as opposed to the complete and force refresh). Read more about refresh types here.

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    What about an Mview with 90mil records- of which only a mil records might have changed. Wont it take a lot of time comparing it with all the data that has changed and then update it?

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It doesn't compare, you make a MVIEW LOG on the machine that is hosting the original table and it will automatically keep track of everything that changed. When you do the fast refresh, it reads the MVIEW LOG on the other system and pulls over only the rows that changed. If you do the refresh every day (or every 15 minutes for example), it will move over only the few records that it has to.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    Thanks very much for the info.

    I have a question on Indexes on Mviews. Usually we drop the indexes on tables before running an ETL on it and that way, it loads quickly. Is it the same with Mviews too? Do i have to drop the indexes before I refresh the mview? Does the indexes created by Oracle automatically help in any way in performance?
    Last edited by nandinir; 05-25-07 at 12:47.

  6. #6
    Join Date
    Jul 2005
    Posts
    276

    Post

    I looked in the manual and it said to create a log on the tables in the Mview if I'm using a fast refresh in the Mviews. I cant create a PK as the table contains NULL values. I created the Mview log using ROWID as follows.

    create materialized view log on asset with rowid;
    create materialized view log on book with rowid;
    create materialized view log on cost with rowid;

    create materialized view tab_mv
    refresh fast with rowid
    as
    select col1, col2, sum(col3)
    from asset,
    book,
    cost
    where ....
    group by .....
    I came across this article which says I cant refresh fast an Mview with rowid when I have aggregates and group by. Any suggestions.

    http://www.databasejournal.com/featu...0893_2192071_2

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You are correct. When you do NOT have a primary key (always unique and not null), then you have to use a rowid mview log. If you do not have a primary key, how do you select a specific table?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jul 2005
    Posts
    276
    Using the rowid. I can use either PK or ROWID, right?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •