Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/articles/oracle/)
-   -   Difference between Views and Materialized Views (http://www.go4expert.com/articles/difference-views-materialized-views-t29595/)

bashamsc 4Apr2013 18:12

Difference between Views and Materialized Views
 

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.


All times are GMT +5.5. The time now is 18:34.