1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Materialized view

Discussion in 'Server Administration and Options' started by dimpleboy, Jun 13, 2011.

  1. dimpleboy

    dimpleboy Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Makati, Philippines
    Hi I am new here, I am trying to simulate materialized view running on Oracle 11gR1 on top of windows OS. I want to include sysdate in the query of my materialized view on a fast refresh but i kept on having the error saying that oracle is seeing that may query is a complex query and not supported by the fast refresh of materialized view. Is there any work around that i can do? I need to simply select * from the master table then include sysdate column in it.

    Thank you!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're trying to build a materialized view with 'select *'? You will have problems with that strategy as DDL to the master table will invalidate the materialized view. You have read the documentation on materialized views found here:

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/basicmv.htm#i1006803

    notably the general restrictions for fast refresh (quoted here):

    "
    General Restrictions on Fast Refresh

    The defining query of the materialized view is restricted as follows:
    • The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.
    • The materialized view must not contain references to RAW or LONG RAW data types.
    • It cannot contain a SELECT list subquery.
    • It cannot contain analytical functions (for example, RANK) in the SELECT clause.
    • It cannot contain a MODEL clause.
    • It cannot contain a HAVING clause with a subquery.
    • It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.
    • It cannot contain a [START WITH …] CONNECT BY clause.
    • It cannot contain multiple detail tables at different sites.
    • ON COMMIT materialized views cannot have remote detail tables.
    • Nested materialized views must have a join or aggregate.
    "
    ? SYSDATE cannot be included for fast refresh and there is no work-around unless you consider modifying your base table definition to include a SYSDATE populated column. But you want the date of the refresh stored in the materialized view, I expect, which is not possible for a fast refresh MV:

    Code (SQL):
    SQL> CREATE TABLE yarpnop(
      2          sming NUMBER,
      3          erq     varchar2(25),
      4          krupt  DATE,
      5          CONSTRAINT yarpnop_pk
      6          PRIMARY KEY(sming)
      7  );
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..10000 loop
      3                  INSERT INTO yarpnop
      4                  VALUES (i, 'String '||i, sysdate+MOD(i,7));
      5          END loop;
      6
      7          commit;
      8
      9  END;
     10  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> CREATE materialized VIEW log ON yarpnop;
     
    Materialized VIEW log created.
     
    SQL>
    SQL> CREATE materialized VIEW yarpnop_mv
      2  refresh fast
      3  AS
      4  SELECT sysdate, yarpnop.*
      5  FROM yarpnop;
    FROM yarpnop
         *
    ERROR at line 5:
    ORA-12015: cannot CREATE a fast refresh materialized VIEW FROM a complex query

    SQL>
    SQL> CREATE VIEW yarpnop_vw
      2  AS
      3  SELECT sysdate sysdt, yarpnop.*
      4  FROM yarpnop;

    VIEW created.
     
    SQL>
    SQL> CREATE materialized VIEW yarpnop_mv
      2  refresh fast
      3  AS
      4  SELECT *
      5  FROM yarpnop_vw;
    FROM yarpnop_vw
         *
    ERROR at line 5:
    ORA-12015: cannot CREATE a fast refresh materialized VIEW FROM a complex query

    SQL>
     
  3. dimpleboy

    dimpleboy Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Makati, Philippines
    thanks for the reply zargon i made some things clearer for me now. I already done before the example you post and it really works. The problem is the original table where im goin to implement this doesnt have date filed like on the example. Anyway thank you so much for the respond. Right now I will try to simulate, making temporary table getting the data from the materialized view log, then there i will add date field. I hope this will work. Thank you!