Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    34

    Unanswered: materialized view diff

    Hi Friends,

    I have a little problem .
    In my schema,I have a materialized view to a remote table.
    How can I get only the "changed" rows when I "fast refresh" the view?

    thank yuou in advance friends (excuse my poor english)

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    There are a number of restrictions on the type of queries which can be used for a fast refresh materialized view (far too many to go into here).

    You also must have created a log on the source table(s) which record changes to the table since the last refresh of the mview.

    Consider using the REFRESH FAST or REFRESH FORCE in the creation of the mview.

    The following is directly from the Oracle docs and should point you in the right direction.

    Hth
    Bill

    STORE (store_key, store_name, store_city, store_state, store_country)
    PRODUCT (prod_key, prod_name, prod_brand)
    TIME (time_key, time_day, time_week, time_month)
    FACT (store_key, prod_key, time_key, dollar_sales)

    /* create the materialized view logs */
    CREATE MATERIALIZED VIEW LOG ON fact
    WITH ROWID;
    CREATE MATERIALIZED VIEW LOG ON store
    WITH ROWID;
    CREATE MATERIALIZED VIEW LOG ON time
    WITH ROWID;
    CREATE MATERIALIZED VIEW LOG ON product
    WITH ROWID;

    /*create materialized join view join_fact_store_time as incrementally
    refreshable at COMMIT time */
    CREATE MATERIALIZED VIEW join_fact_store_time
    REFRESH FAST ON COMMIT AS
    SELECT s.store_key, s.store_name, f.dollar_sales, t.time_key, t.time_day,
    f.prod_key, f.rowid frid, t.rowid trid, s.rowid srid
    FROM fact f, store s, time t
    WHERE f.time_key = t.time_key AND
    f.store_key = s.store_key;

  3. #3
    Join Date
    Jan 2003
    Posts
    34
    Hi BILLM,

    thank you for interest.

    My problem is this:
    If ,by example, yestrday I refreshed my view and
    now I refresh it ,How can I get only rows changed during
    1 day?
    I should like to get only new rows and changed rows,not equal rows.

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I don't know of any Oracle support for this, maybe it's possible to determine the contents of the materialized view log on the source table - possibly through the system views or something - of that I don't know.

    Secondary to that, you could disable automatic refresh of the mview. Then before refreshing your mview manually, you would do....

    1: create table mview_temp as (select * from mview)

    2: refresh your mview

    3: select * from mview
    minus
    select * from mview_temp

    That should show you the changed rows, you'll have to do something else for deleted rows. Of course if the mview contains an awfull lot of data it's going to be time consuming.

    Is it not possible to put an update date/time column on the source table, automatically refreshed with current sysdate for all updates/inserts?

    Hth
    Bill

Posting Permissions

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