Results 1 to 9 of 9

Thread: mviews

  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: mviews

    I have a question on creating mviews.
    my code is:
    create materialized view mv_tab1
    build immediate
    refresh fast
    enable query rewrite
    as
    select
    DISTINCT a.UNIT
    ,a.CLASS
    ,a.TYPE
    ,a.DESCR
    ,a.LOCATION
    ,a.DESCR
    ,a.unit_DESCR
    from tab1 a
    I get this error:

    SQL> @c:\mv_sum;
    from tab1 a
    *
    ERROR at line 14:
    ORA-12015: cannot create a fast refresh materialized view from a complex query
    Am I going wrong somewhere??

    What should the query_rewrite_integrity be set to? I set it to 'stale_tolerated'
    Last edited by nandinir; 11-30-06 at 12:40.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    exactly what it said, when you use aggrates (distinct), the database can't automatically maintain the mview. You will have to perform a scheduled refresh.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    I dont want a fast refresh, so I gave refresh force.
    create materialized view mv_tab1
    build immediate
    refresh force
    enable query rewrite
    as
    select
    DISTINCT a.UNIT
    ,a.CLASS
    ,a.TYPE
    ,a.DESCR
    ,a.LOCATION
    ,a.DESCR
    ,a.unit_DESCR
    from tab1 a
    query_rewrite_enabled=true
    query_rewrite_integrity=enforced

    SQL> @c:\mv_tab1;

    Materialized view dropped.


    Materialized view created.

    SQL> analyze table tab1 compute statistics;

    Table analyzed.

    SQL> exec dbms_mview.refresh('mv_tab1');

    PL/SQL procedure successfully completed.

    I cant see the updated data in the mview. Can anyone tell me where exactly I'm going wrong, please.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What changes do you expect to the mview from running a table analysys?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    I went through some article in asktom.oracle and I followed the steps given by him, but that was an example for fast refresh and here I'm using refresh force.

    http://asktom.oracle.com/pls/ask/f?p...:4541191739042

    Whenever we rebuild index, we need to run analyze command for the statistics to accurately reflect the data in your database. I inferred from Tom's advise that perhaps we need to analyze when we create mviews too. I'm not sure though. Correct me if I am wrong.
    Last edited by nandinir; 12-01-06 at 09:36.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Gathering statistics have nothing to do with returning the correct data, only the efficiency of the database in finding that data and since an MVIEW is an actual table, then gathering statistics on it is a good idea to speed up the query.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jul 2005
    Posts
    276
    I analyzed the MVIEW but still I'm not able to update the data. And moreover, when I create the MVIEW from the start with the added row(which was not in the table initially), its not being loaded into the mview.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    A complex MVIEW is ONLY updated when you run the refresh. Please paste the MVIEW create script and the data that is not being included.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Jul 2005
    Posts
    276
    Here is the script:

    drop materialized view mv_dw_sum_assets
    /

    create materialized view mv_tab1
    build immediate
    refresh force
    enable query rewrite
    as
    select
    DISTINCT a.UNIT
    ,a.CLASS
    ,a.TYPE
    ,a.DESCR
    ,a.LOCATION
    ,a.DESCR
    ,a.UNIT_DESCR
    from tab1 a
    /
    query_rewrite_integrity=enforced

    And the row which is not being updated is test data with 999 in the first three columns and xxxx in the last 4 columns.

Posting Permissions

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