error while creating materialized view
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: error while creating materialized view

  1. #1
    Join Date
    May 2002
    Posts
    22
    We are trying to create a materialized view with the following statements:
    CREATE TABLE agg
    (FELD1 NUMBER, FELD2 NUMBER, FELD3 NUMBER, FELD4 NUMBER, d NUMBER);

    CREATE MATERIALIZED VIEW LOG ON TESTTABLE WITH ROWID(FELD1, FELD2, FELD3, FELD4) INCLUDING NEW VALUES;

    CREATE MATERIALIZED VIEW TESTMATVIEW
    REFRESH FAST ON COMMIT
    AS SELECT
    feld4,
    SUM(FELD2+FELD3),
    COUNT(*)
    FROM TESTTABLE
    GROUP BY FELD4;

    and get the error message: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

    Please help me to solve this error

    Thanks in advance
    Bala

  2. #2
    Join Date
    Sep 2001
    Location
    Australia
    Posts
    18
    Hi Bala,

    I don't think that the fast refresh option for materialised views is valid if the view uses analytic functions - such as SUM.

    You might have to use complete refresh.

  3. #3
    Join Date
    Sep 2001
    Location
    Australia
    Posts
    18
    Just ignore my last post Bala. I just noticed a materialised view example in the Oracle documentation that uses SUM with a FAST REFRESH.

    Sorry if I misled you.

Posting Permissions

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


Click Here to Expand Forum to Full Width