Forgot your password?    
+ Reply to Thread
Results 1 to 1 of 1

Thread: Materialized Views,Complete and Fast Refresh in 11g Oracle Database

  1. #1
    Oracle Administrator
    Join Date
    Dec 2011
    Posts
    93

    Materialized Views,Complete and Fast Refresh in 11g Oracle Database

    The Following tutorial is prepared on Oracle Database 11gR2.

    Materialized views facilitate you to execute a SQl query and save its results either locally or in a remote database.

    After the Materialized view is initially populated it can be refreshed later on to store fresh results into the underlying table.

    Materialized Views are mainly used for two reasons:

    1) Replication of data to separate remote databases.

    2) For improving the performance of queries by computing and storing the results of complex aggregations of data.

    In situations where complex sql queries are performed mainly in reporting or datawarehouse environments
    Materialized Views are really helpful in improving performance.

    Whenever a SQL query is executed oracle database has to lot of work in order to retrieve the data.
    For example it may have to do sorting (Memory or Disk Based), it has to decide the execution plan for the sql statement
    (Do a full tables scan or a indexed based scan) and lots of other stuff before retrieving the requested data.

    These type of queries if performed repeatedly will affect the performance of the server in a negative way.

    But with Materialized Views the performance can be improved significantly,
    because when a materialized view is created it stores all the data along with the execution plans.
    So even if the query is executed repeatedly it will not eat up all the resources as it did earlier.


    The Materialized view can be created on the base of tables, views or other materialized views.

    When a Materialized View is created, oracle also create a table with the same name as that of the materialized view
    and also creates a materialized view object.

    For the sake of simplicity we will only cover two types of materialized views:

    1) Complete - Refreshable Materialzed Views
    2) Fast-refresh Materialized Views



    COMPLETE REFERSH MATERIALIZED VIEWS


    In this type of materialized view there is a complete refresh of data at periodic intervals.


    SQL> alter user scott identified by tiger account unlock;

    User altered.

    SQL> grant create materialized view to scott;

    Grant succeeded.

    SQL> conn scott;
    Enter password:
    Connected.


    SQL> create table sales(
    2 sales_id int,
    3 sales_amt int,
    4 region_id int,
    5 sales_dtt timestamp,
    6 constraint sales_pk primary key (sales_id));

    Table created.

    SQL> insert into sales values(1,101,100,sysdate-50);
    SQL> insert into sales values(2,511,200,sysdate-20)
    SQL> insert into sales values(3,11,100,sysdate)
    SQL> commit;

    Now lets create a materialized view.

    SQL> create materialized view sales_mv
    2 refresh
    3 complete
    4 next sysdate+1/1440
    5 as
    6 select sales_amt, sales_dtt from sales;

    Materialized view created.


    So above we have created a materialized view based on the sales table, which will completely refresh itself after every one minute.



    SQL> select mview_name, refresh_method, refresh_mode, build_mode, fast_refreshable from user_mviews where mview_name = 'SALES_MV';

    Code:
    MVIEW_NAME           REFRESH_ REFRESH_MODE BUILD_MOD FAST_REFRESHABLE
    -------------------- -------- ------------ --------- ----------------
    SALES_MV             COMPLETE DEMAND       IMMEDIATE NO
    Materialized views can also be refreshed by (ON DEMAND or ON COMMIT).
    Since i did not mention either of these clauses the default refresh is on demand as seen above in REFRESH_MODE column.

    If you query the user_objects you can see that several objects have been created.


    SQL> col object_name format a20
    SQL> select object_name, object_type from user_objects where object_name like 'SALES%' order by object_name;

    Code:
    OBJECT_NAME          OBJECT_TYPE
    -------------------- -------------------
    SALES                TABLE
    SALES_MV             MATERIALIZED VIEW
    SALES_MV             TABLE
    SALES_PK             INDEX


    The materialized view is basically a logical container that stores data in a regular table.

    If you query the USER_SEGMENTS view you will find the base table its primary-key and the table that stores the data returned by the Materialized View.


    SQL> select segment_name,segment_type from user_segments
    2 where segment_name like 'SALES%'
    3 order by segment_name;

    Code:
    SEGMENT_NAME    SEGMENT_TYPE
    ------------    ---------------
    SALES           TABLE
    SALES_MV        TABLE
    SALES_PK        INDEX
    Now lets check the already existing data and some more.

    SQL> select sales_amt, to_char(sales_dtt,'dd-mon-yyyy') from sales_mv;

    Code:
     SALES_AMT TO_CHAR(SAL
    ---------- -----------
           101 22-nov-2011
           511 22-dec-2011
            11 11-jan-2012
    SQL> insert into sales values(4,99,200,sysdate);

    1 row created.


    SQL>insert into sales values(5,127,300,sysdate);

    1 row created.

    SQL> commit;

    Commit complete.


    After one minute the materialized view will get updated.

    SQL> select sales_amt, to_char(sales_dtt,'dd-mon-yyyy') from sales_mv;

    Code:
       SALES_AMT  TO_CHAR(SAL
          ---------- -----------
                 101 22-nov-2011
                 511 22-dec-2011
                  11 11-jan-2012
                  99 11-jan-2012
                 127 11-jan-2012

    if you have set a longer refresh interval and you do not want to wait that long you can order the refresh manually through the following command.


    SQL> exec dbms_mview.refresh('SALES_MV','C');

    PL/SQL procedure successfully completed.

    # Where "C" stands for complete refresh.


    So the whole process can ve summarized as following.

    1) User or application creates transactions.
    2) Base table is updated because of the transactions.
    3) A complete refresh occurs or is done manually and
    the data in the materialized view is deleted and completely
    refreshed with the contents of the master table(SALES).
    4) The User or application can query the materialized view
    which contains a point in time snapshot of the base table's data.



    FAST REFRESH MATERIALIZED VIEW

    Fast refreshable materialized views work a little bit differently. When a fast refresh materialized view is created it initially
    populates the materialized view table with data from the base or master table.

    After the initial data is populated only modified data is applied to the materialized view table after each refresh, Instead of a
    complete refresh like that in Complete refresh materialized views.

    Three basic steps are required to create a fast refresh materialized view.

    1) Create a base or master table if it does not exist.
    2) Create a Materialized view log on the base table.
    3) Create a fast refresh materialized view.

    Since i have already created a materialized view and base table i am going to drop them and make a fresh start.

    SQL> drop materialized view sales_mv;

    Materialized view dropped.

    SQL> drop table sales purge;

    Table dropped.


    SQL> create table sales(
    2 sales_id int,
    3 sales_amt int,
    4 region_id int,
    5 sales_dtt timestamp
    6 );


    SQL> alter table sales add constraint sales_pk primary key(sales_id);

    Table altered.


    SQL> desc sales;

    Code:
     Name                 Null?    Type
     ------------------ -------- ---------------------
     SALES_ID           NOT NULL NUMBER(38)
     SALES_AMT                   NUMBER(38)
     REGION_ID                   NUMBER(38)
     SALES_DTT                   TIMESTAMP(6)

    SQL> insert into sales values(1,101,100,sysdate-50);

    1 row created.

    SQL> insert into sales values(2,511,200,sysdate-20);

    1 row created.

    SQL> insert into sales values(3,11,100,sysdate);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL>

    When creating a fast refreshable materialized view a materialized view log is required.

    The reason behind this is that the log keeps track of all the changes made to the master table, so when a materialized view
    is refreshed only updated data is applied.

    It is something similar to the "block change tracking file" feature in RMAN.

    Further a materialized view can be created on the basis of primary key or by ROWID.

    If the master table has a primary key then primary key clause can be used otherwise use ROWID.

    Now lets create a materialized view log on the master table.

    SQL> create materialized view log on sales with primary key;

    Materialized view log created.

    If your base table does not have a primary key then a following error will occur.

    ORA-12014: table does not contain primary key constraint

    In that case make materialized view log based on ROWID.

    SQL> create materialized view log sales with rowid;


    Also, when creating a materialized view you have to mention whether the data is refreshed via PRIMARY KEY or ROWID.

    We are creating a materialized view based on primary key refresh.

    NOTE: The primary key columns must be part of the MV select query from
    the base table.


    SQL> create materialized view sales_mv
    refresh
    with primary key
    fast
    next sysdate+3/1440
    as
    select sales_id, sales_amt, sales_dtt from sales


    Materialized view created.

    Now lets query the USER_OBJECTS view.

    SQL> select object_name, object_type from user_objects
    2 where object_name like '%SALES%'
    3 order by object_name;

    Code:
    OBJECT_NAME          OBJECT_TYPE
    -------------------- -------------------
    MLOG$_SALES          TABLE
    RUPD$_SALES          TABLE
    SALES                TABLE
    SALES_MV             MATERIALIZED VIEW
    SALES_MV             TABLE
    SALES_PK             INDEX
    SALES_PK1            INDEX
    7 rows selected.


    Explanation.

    MLOG$_SALES = This is a table created along with the materialized view.
    It contains data that has changed in the base table.

    RUPD$_SALES = This table is created when a materialized view
    uses primary key for fast refresh. This is used
    to support updatable materialized views. But right
    now we are creating Read only MVs so ignore this table.

    SALES_PK1 = This index is automatically created and is based on the
    primary key columns of the base table.



    SQL> select sales_amt, to_char(sales_dtt,'dd-mon-yyyy') from sales_mv;

    Code:
     SALES_AMT TO_CHAR(SAL
    ---------- -----------
           101 23-nov-2011
           511 23-dec-2011
            11 12-jan-2012
    Lets add some records.

    SQL> insert into sales values(4,99,200,sysdate);

    1 row created.

    SQL> insert into sales values(5,127,300,sysdate);

    1 row created.

    SQL> commit;

    Commit complete.


    Now before refresh the mlog$_sales table will contain information about the two changes that
    have been made to the base table.


    SQL> select count(*) from mlog$_sales;

    COUNT(*)
    ----------
    2

    Wait for three minutes or refresh the view manually.

    SQL> exec dbms_mview.refresh('SALES_MV','F');

    Lets check the records.

    SQL> select sales_amt, to_char(sales_dtt,'dd-mon-yyyy') from sales_mv;
    Code:
     SALES_AMT TO_CHAR(SAL
    ---------- -----------
           101 23-nov-2011
           511 23-dec-2011
            11 12-jan-2012
            99 12-jan-2012
           127 12-jan-2012

    After the refresh is complete and the data is refreshed
    the MLOG$_SALES table will contain no records.

    SQL> select count(*) from mlog$_sales;

    COUNT(*)
    ----------
    0

    You can also check time of the last refresh when it happened.

    SQL> select mview_name, last_refresh_type, last_refresh_date
    from user_mviews;
    Code:
    MVIEW_NAME                     LAST_REF LAST_REFR
    ------------------------------ -------- ---------
    SALES_MV                       FAST     12-JAN-12
    The whole process above is summarized as following:

    1) User or application creates transactions.
    2) Data is commited in the base table.
    3) Then the MVlog table is populated with the changes.
    4) A fast refresh occurs automatically or manually.
    5) All the changes that have been made since last refresh
    are applied to the materialized view and rows that are no
    longer required are deleted from MVlog table.
    6) The users can query the materialized view which contains
    point in time snapshot of master tables data.
    Last edited by Hemant; 01-13-2012 at 09:01 PM.

+ Reply to Thread

Similar Threads

  1. RECOVERY SCENARIO: Complete loss of all database files including SPFILE using RMAN
    By devesh_ocp in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 04-25-2012, 10:26 AM
  2. Test/Development Database Refresh From Production Procedure
    By devesh_ocp in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 04-25-2012, 10:23 AM
  3. Oracle Database V$ Views
    By Pradeep in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 12-14-2011, 05:04 AM
  4. Oracle Database V$ Views
    By Pradeep in forum Oracle Database Administration 10g and 11g
    Replies: 0
    Last Post: 12-14-2011, 05:04 AM
  5. Oracle ASM Views and Usage - Quick Reference/Understanding
    By Hemant in forum RAC Installation, ASM Install , ASM Administration
    Replies: 0
    Last Post: 11-21-2011, 04:17 PM

Tags for this Thread

Posting Permissions

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

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics