As we have seen above, views are executed when we fire query on it. So, it does not speed up the execution of the query that is having views. But it simplifies the query. But when we write a query it is always expected it to run quickly. So what we can do is, run the frequently used query and store it under some name! This is similar to view but here we are executing the query and storing the results of query under name. This type of view is called materialized view. Using materialized view in complex query reduces the execution time for running repeated queries. It involves the time for running ‘SELECT * FROM …’. Unlike views, in this type of views, copies of table records are created and stored. Materialized views are not virtual tables. Hence there will be a storage space allocated to it.
CREATE MATERIALIZED VIEW mv_Design_Emp AS SELECT e.EMP_ID, e.EMP_FIRST_NAME, e.EMP_LAST_NAME, d.DEPT_ID, d.DEPT_NAME FROM EMPLOYEE e, DEPARTMENT d – two tables are used to create a view WHERE e.DEPT_ID = d.DEPT_ID -- Join condition AND d.DEPT_NAME = ‘DESIGN’; -- Filtering condition
Here unlike views, materialized views mv_Design_Emp and mv_Test_Emp are two separate copies of tables and stored in the different data block of memory.
We can have SELECT query on materialized views as we do with any other tables. Since this view is created and stored in advance, it might not have latest records from the table. That means if any records are inserted into the table after a materialized view is created and that inserted record is a valid candidate to be in materialized view, then it will not be shown in the materialized view. Materialized view needs to be refreshed to get the latest record.
Comparison between Views and Materialized View :
