Difference between Views and Materialized Views

Discussion in 'Oracle' started by bashamsc, Apr 4, 2013.

  1. bashamsc

    bashamsc New Member

    Joined:
    May 22, 2007
    Messages:
    51
    Likes Received:
    7
    Trophy Points:
    0
    Location:
    chennai

    Materialized Views


    • Stores results not queries
    • Requires Physical memory
    • No auto updates
    • Execution time is less

    Views


    • Stores queries not data
    • No physical memory required
    • Auto Updates
    • Execution time is more
    Now we will try to learn the difference by looking into below the examples.

    1. Stores queries not data vs Stores results not queries

    Let us suppose the view v_emplyee and it stores query as shown below

    Code:
    select * from t_employee
    When we perform
    Code:
    select * from v_employee
    it will execute the query
    Code:
    select * from t_employee
    But MV stores the result set of
    Code:
    select * from t_employee
    .
    When we select the data from MV it will not fetch data from the table t_employee.

    2. No physical memory required vs Requires Physical memory

    View uses only virtual memory because it will not store any data. But whereas MV it needs to store the data it requires physical memory.

    3. Auto Updates vs No auto updates

    Let us suppose t_employee consits below data

    Code:
    EMP_ID   EMP_NAME  EMP_SALARY
    
    1000	 XYZ	   2000
    
    2000	 PQR	   3000
    
    3000	 ABC       5000
    Now if we select data from view will data show as below

    Code:
    EMP_ID   EMP_NAME  EMP_SALARY
    
    1000	 XYZ	   2000
    
    2000	 PQR	   3000
    
    3000	 ABC       5000
    Now if we select data from MV will show data as below

    Code:
    EMP_ID   EMP_NAME  EMP_SALARY
    
    1000	 XYZ	   2000
    
    2000	 PQR	   3000
    
    3000	 ABC       5000
    Now if we will update table and will see what data we are going to see in view and MV.

    Code:
    Update t_employee set EMP_SALARY=4000 where EMP_ID=1000
    Code:
    select * from t_employee
    Code:
    EMP_ID   EMP_NAME  EMP_SALARY
    
    1000	 XYZ	   4000
    
    2000	 PQR	   3000
    
    3000	 ABC       5000
    Now if we select data from view will data show as below
    Code:
    EMP_ID   EMP_NAME  EMP_SALARY
    
    1000	 XYZ	   4000
    
    2000	 PQR	   3000
    
    3000	 ABC       5000
    Now if we select data from MV will show data as below
    Code:
    EMP_ID   EMP_NAME  EMP_SALARY
    
    1000	 XYZ	   2000
    
    2000	 PQR	   3000
    
    3000	 ABC       5000
    Now we can see clearly that data has been updated automatically in view but not in MV.

    For updating the data in MV we have two methods

    Fast Refresh :- Whatever changes (Insert/Delete/Update) happened to the table fast refresh will apply those changes to the MV.

    Below is the script to perform fast refresh

    Code:
    exec dbms_mview.refresh( 'T_EMPLOYEE_MV', 'F' );
    Fast refresh will use materialized logs to implement the changes to MV.

    Complete refresh :- Complete refresh will truncate the data in MV and re-load the data from table.

    Below is the script to perform fast refresh

    Code:
    exec dbms_mview.refresh( 'T_EMPLOYEE_MV', 'C' );
    4. Execution time is more vs Execution time is less

    Let us suppose view is created as below

    Code:
    create view v_employee as select * from t_employee where emp_salary=(select max(emp_salary) from t_employee);
    and MV is created as below

    Code:
    create materialized view t_employee_mv as select * from t_employee where emp_salary=(select max(emp_salary) from t_employee);
    Now if we do
    Code:
    select * from v_employee
    then it will take more time to get max emp_salary and then filter it out whereas MV already stored the result set it will directly give the result to us.
     
    askr likes this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice