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

    Unanswered: Mview on Summary table

    I'm writing a Mview for a summary table and I'm not sure if the options I chose is right. I created a log file on tab

    CREATE MATERIALIZED VIEW LOG ON tab;

    And then a mview on tab as below:

    create materialized view mv_sumtab
    enable query rewrite
    REFRESH fast
    START WITH SYSDATE NEXT trunc(SYSDATE) + 1
    as
    select DISTINCT a.UNIT
    ,a.CLASS
    ,a.TYPE
    ,a.DESCR
    ,a.LOC
    ,a.DESCR
    ,a.UNIT_DESCR
    from tab a
    I get this error:
    REFRESH fast
    *
    ERROR at line 3:
    ORA-00905: missing keyword
    I'm not clear about "enable query rewrite" too. I read some stuff online but unable to comprehend. Can someone please explain in brief about it?
    Thanks in advance.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    A brief explanation about how to Create a Materialized View can be found at the end of this link.

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    Thanks Littlefoot. I actually went through this doc before but I didnt understand this point and its a little unclear. Don't know if I'm thinking the right way or not.

    The QUERY REWRITE clause lets you specify whether the materialized view is eligible to be used for query rewrite.
    If we dont enable query rewrite, cant we modify the query later on. Is that what it means? Perhaps, I'm wrong.

Posting Permissions

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