Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    42

    Unanswered: Materialized View Problem

    HI,
    I am facing a problem with Materialized View. Lets say I issued the following statements.

    CREATE TABLE TEST(n NUMBER(2) PRIMARY KEY,m NUMBER(2))

    CREATE MATERIALIZED VIEW LOG ON TEST LOGGING WITH PRIMARY KEY EXCLUDING NEW VALUES

    INSERT INTO TEST VALUES(12,13);

    UPDATE TEST SET m=14 WHERE n=12

    COMMIT;

    After this MLOG$_TEST CONTAINs 2 ROWS with DMLTYPE$$ as I for Inserted row and U for
    Updated row to refresh the Materialized View. The record structure is as follows

    N SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
    ------------------------------------------------------------------------
    12 1/1/4000 I N FE
    12 1/1/4000 U U 04

    Is it possible that we can keep only one record in MLOG$_TEST for both the DML operations??

    Can anybody help me out??

    Thanks in advance.

    Regards
    Rasmi

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    This is a duplicate of a previous posting.

    see http://www.dbforums.com/t1120112.html
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    No. That's the way mat views work. The insert will contain all the data for the row, but the update will only contain the changed column (8.1.7 and above). The mat logs get purged with the purge job (as long as the refresh
    has occurred )

    HTH
    Gregg

Posting Permissions

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