Difference Between Views and Materialized Views in PL/SQL

In PL/SQL views play an important role in data accessibility, data manipulation, and query optimization. Views help restrict the user's data, which is not supposed to be accessed by a particular user. Views are instrumental in securing data by restricting the visibility of specific fields or rows based on user permissions.

The two main types of views in Oracle PL/SQL are standard Views and Materialized Views. Each type has unique properties, functions, and use cases, making it essential to understand when to apply one over the other.

In this article, we'll examine the differences between Views and Materialized Views, including their properties, syntax, and practical implementations.

What Are Views and Materialized Views in PL/SQL?

Views and Materialized Views help to form a layer of abstraction over the database table which restricts user's accessibility and manipulation of data. The major difference between Views and Materialized Views is that Views are dynamic (show the latest data) and materialized views are static (show data from last refresh). Let's look into the concept of Views and Materialized views.

1. Views

Views are the virtual table of the dataset which are created by executing SELECT query in SQL. It acts as an actual relation. Views do not get stored in physical memory instead it returns original and updated data from the table every time it is accessed. The important thing to know about views is that if we make any changes in the original table, it will also get reflected in the views table. We can generate virtual tables or views as per our data requirements.

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2,...
FROM table_name
WHERE condition;

2. Materialized View

Materialized views also act as a virtual table but in this, the result of the query is stored in physical memory and the stored result of the query reduces the need for repeated computations and helps to enhance the query performance. They can also be considered as a physical copy of the original database which helps in the warehousing of data. Unlike Views, they are not updated every time they are accessed. If we want to update Materialized View then we need to do it manually with the help of some trigger.

Syntax:

CREATE MATERIALIZED VIEW materialized_view_name
AS
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY columns]
[ORDER BY columns]

Examples

Example 1: Creating a View

To create a view in SQL, first, let's take a database table named "employees". This table shows the employee data such as their ID, name, department, and salary. We want to create a view that displays only the employee ID, name, and department of the employees where the department is "IT".

Now, to create a view we will execute the below query:

CREATE VIEW employee_view AS
SELECT emp_id, emp_name, emp_salary
FROM employees
WHERE department = 'IT';

We have successfully created a view named employee_view that displays the employee ID, name, and department for all employees. To show the view in a database table we will use below mentioned query:

SELECT * FROM employee_view;

Output:

Example 2: Creating Materialized View

To create a Materialized View, first, we'll take a database table named "sales" which has sales data such as product ID and quantity sold. Then we will create a materialized view named sales_mv that contains the product ID and total sales for each product.

Now, to create a materialized view we will execute the below query:

CREATE MATERIALIZED VIEW sales_mv AS
SELECT product_id, SUM(quantity_sold) AS total_sales
FROM sales
GROUP BY product_id;

To show the result of querying materialized view:

SELECT * FROM sales_mv;

Output:

Key Differences between Views and Materialized Views

When to Use Views vs. Materialized Views

Choosing between Views and Materialized Views depends on your use case:

• Use Views when you need real-time access to updated data, such as in transactional systems or real-time dashboards.
• Use Materialized Views when you prioritize performance over freshness, especially in data warehousing scenarios or for complex aggregations.

Conclusion

Views and Materialized Views both are important tools in data abstraction and data manipulation. Views are dynamic which means they always return the updated and original data whereas Materialized Views are static which only provide the last refreshed data. Both are used for data abstraction as per the situation of the user due to their different functionalities. By understanding the difference between these two we can effectively use them for data abstraction in PL/SQL.