SQL views and materialized views are powerful tools for simplifying complex queries and improving database performance. This post explains the difference between regular views (virtual tables) and materialized views (physically stored query results), how to create and refresh materialized views in Oracle SQL, and when to use each for query optimization.
View
- A view is a virtual table, not physically stored in the database
- Can consist of one or more base tables
- Contains rows and columns
- Can be queried like a regular table
Create View
create view v_emp as select * from emp where deptno = 10;
Replace View
create or replace view v_emp as select * from emp;
Drop View
drop view v_emp;
What are Materialized Views?
- Materialized Views (MV) are physically stored in the database
- MVs can provide performance and efficiency benefits
- When you query a MV you are accessing a physical table rather than the base tables used in the SQL query
- MVs do not contain live data but can be refreshed
- In SQL Server they are called Indexed Views
Create materialized view
create materialized view mv_emp_dept
as
select emp.*, dept.dname from emp join dept on emp.deptno = dept.deptno;
Materialized Views - Syntax
Simple vs Complex MVs
A Materialized View can be considered Complex when it has been constructed using:
- A CONNECT BY clause
- An INTERSECT, UNION ALL or MINUS Set Operation
- The DISTINCT or UNIQUE keyword
- Aggregation Functions and Group By Clauses
- Table Joins
Fast Refresh
- Fast refresh updates the Materialized View with only the changes made to the base table, rather than truncating and re-populating
- To do a fast refresh you need to create a Materialized View log which tracks changes made to the base tables (CREATE MATERIALIZED VIEW LOG ON talbe_name;)
- You must have CREATE TABLE privileges on the master table
- Fast refresh can only be performed on Simple MVs
COMPLETE AND FORCE REFRESH
- A COMPLETE REFRESH will clear the entire MV Table and populate it again
- A FORCE REFRESH will attempt to do a FAST REFRESH and revert to a COMPLETE refresh if this fails
Performing a Manual Refresh
- DBMS_MVIEWS.REFRESH(mv_name, refresh type);
- 'F'=FORCE, 'C'=COMPLETE
- DBMS_SNAPSHOT.REFRESH(mv_name, refresh type);
Useful Links And Resources
Official Oracle Documentation (Basic Materialized Views)
Official Oracle Documentation (Advanced Materialized Views)
Official Oracle Documentation (Query Rewrite)
FAQs from the Ask Tom Forum on Materialized Views
Frequently Asked Questions
What is a materialized view?
A materialized view is a database object that stores the result of a query physically on disk, unlike a regular view which is just a saved query executed each time — this allows materialized views to dramatically improve query performance for complex aggregations.
What is the difference between a view and a materialized view?
A regular view is a virtual table that re-executes its defining query every time it is accessed, while a materialized view stores the query results physically and must be refreshed periodically, offering much faster read performance at the cost of storage and refresh overhead.