Materialized view - General questions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Materialized view - General questions

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Guys,

    We are on Oracle 8173 on Windows.

    I am new to the materialized view (MV) feature of 8i.

    I want to create a MV with manual refresh, which I will refresh weekly.

    Could someone provide me the query?

    Also, then, How will I refresh the MV.

    Thanks in Adv.

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    To build the materialized view do something like:

    CREATE MATERIALIZED VIEW emp_mv
    BUILD IMMEDIATE
    REFRESH FORCE
    ON DEMAND
    AS
    SELECT * FROM emp;

    Refresh the materialized view using:

    SQL> EXEC DBMS_MVIEW.REFRESH('EMP_MV');

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Tim,

    Thanks a lot.

    It worked.

    What is the diff. between REFRESH COMPLETE and FORCE?

    When do we have to use QUERY REWRITE and UPDATAEBLE?



    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    COMPLETE - All rows deletes and replaced.
    FAST - Only changes in base table are transfered to the MVIEW, rather than replacing all the rows.
    FORCE - A FAST is performed, if this cannot be done a COMPLETE is done.

    You'll need to define material view logs to use a fast refresh and there are a number of restrictions.

    Updatable is used if you want the MVIEW to be updatable, and the changes to be refleacted in the base table.

    Query rewrite is necessary if you want Oracle to rewrite SQL statements to take advantage of available MViews if they would improve performance.

    See:

    http://otn.oracle.com/docs/products/...994/mv.htm#721

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  5. #5
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Tim,

    Great help!

    Thank you very much.

    Now, I think I will have to schedule the job to refresh this MV.

    Correct?

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  6. #6
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Tim,

    I see two instances for my materialized view in the DBA_OBJECTS.

    One with object_type as TABLE and another with 'UNDEFINED'.

    But, I can still I am able to refresh the MV.

    Questions:

    1. Why there are two instances for the MV with the same name?

    2. Why it shows 'UNDEFINED' in object_type?

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  7. #7
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    One of the objects is the materialized view definition itself. The other is the actual table that holds all the rows. I'm not sure why your version does not list this properly. I'm using 9.2 and I get:

    SQL> select object_name, object_type from user_objects;

    OBJECT_NAME OBJECT_TYPE
    ------------------ ------------------
    EMP_MV TABLE
    EMP_MV MATERIALIZED VIEW

    2 rows selected.

    SQL>

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  8. #8
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    quote
    -----------
    One of the objects is the materialized view definition itself. The other is the actual table that holds all the rows. I'm not sure why your version does not list this properly. I'm using 9.2 and I get:
    -------------

    there is lot of difference between 8i Materialized views and 9i materialised views

    some of the features which i noticed is
    8i does not support automatic refresh on its own . where as 9i does support . we need to do manual refresh in 8i .

    i think in 8i it is a concept of snapshot which is called materialized views in 9i . ofcourse even in snapshots it creates materialized views.
    siva prakash
    DBA

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