Databases: Simple vs. Complex vs. Materialized Views

1. Overview

In SQL databases, we can use views to represent consolidated data perspectives from one or more tables. There are different types of views, primarily differing in their purpose: some act as a restriction and simplify a query, while others improve the access performance by caching data. Overall, views serve as a powerful tool in database management.

In this tutorial, we’ll talk about simple and complex SQL views and how they differ from materialized views with some practical examples using the University database.

2. Why Do We Need Views

Views, stored in the database with a name, act as a virtual table displaying a subset of data from one or more actual tables. For example, we can create a view to store the output of a query that we often reuse or expose to a Web application.

A view can make a statement more readable. For instance, if a query joins tables from one or more databases, views can properly display the results. With views, we can grant permissions to specific users and hide sensitive information, thereby improving security.

Before moving forward, let’s consider an often-used subquery syntax:

SELECT * FROM
(
  SELECT column1, column2 FROM some_table
) s;

Notably, we can call this subquery an inline view. All the views work with the same concepts but also use DDL commands to store the query as a reusable statement.

3. Simple and Complex Views

If a query isn’t resource-intensive and the result often changes, we can consider using simple or complex views.

3.1. Simple View

A simple view contains only one base table or fetches data from only one table.

Suppose we want to show program information through a Web application or to a specific database user. We can create a view that hides all the sensitive or non-essential information. For example, the view can include only the program ID, name, and type. This way, we can grant non-admin users access to this view and hide the Program table and its sensitive information.

Let’s create a simple view of the Program table for regular users:

CREATE VIEW program_details AS
SELECT id, name, type from Program;

Once the view is created, we can SELECT from it as if it’s a table:

SELECT id, name, type
FROM program_details;

We can see an output example of the view:

+-----+------------------------------+-------+
| id  | name                         | type  |
+-----+------------------------------+-------+
| 111 | Operating Systems            | Major |
| 112 | Operating Systems            | Minor |
| 121 | Databases                    | Major |
| 122 | Databases                    | Minor |
| 131 | Theoretical Computer Science | Major |
...
| 512 | Linear Algebra               | Minor |
| 521 | Discrete Mathematics         | Major |
| 522 | Discrete Mathematics         | Minor |
+-----+------------------------------+-------+
24 rows in set (0.00 sec)

This way, views enable the creation of virtual tables.

3.2. Complex View

A complex view contains more than one base table. Furthermore, it can have derived columns created using GROUP BY and DISTINCT clauses, and it can use aggregate functions like AVG and COUNT.

Let’s create a view by joining the Faculty table with the Department table:

CREATE VIEW faculty_department AS
SELECT DISTINCT
  f.name AS faculty_name,
  f.national_id,
  d.name as department_name
FROM Faculty f
INNER JOIN Department d ON f.department_id = d.id;

Likewise, we can read from it:

SELECT faculty_name, national_id, department_name
FROM faculty_department;

+-----------------+-------------+--------------------------------+
| faculty_name    | national_id | department_name                |
+-----------------+-------------+--------------------------------+
| AV Subramanium  |  1340902317 | Computer Science               |
| Risa Sodi       |  1409239017 | Computer Science               |
| Wlliam Liu      |   231456789 | Computer Science               |
| Cormen Qiu      |  1040902317 | Computer Science               |
| Ajit Singh      |  1302632117 | Computer Science               |
...
| Rosita Klaus    |  1234015024 | Mathematics                    |
| Ballu Singh     |  2134105024 | Mathematics                    |
| Rory Ross       |  4021351024 | Mathematics                    |
+-----------------+-------------+--------------------------------+
46 rows in set (0.00 sec)

Furthermore, we can create a view with more complex queries including aggregate functions like COUNT.

For instance, let’s create a view that shows the Faculty count per Department:

CREATE VIEW faculties_per_dept AS
SELECT
  d.id AS department_id, d.name AS department_name,
  COUNT(DISTINCT f.national_id) as faculty_count
  FROM Department d INNER JOIN Faculty f
ON d.id = f.department_id
GROUP BY d.id, d.name;

Again, let’s SELECT all the rows from this view to get the result set:

+---------------+--------------------------------+---------------+
| department_id | department_name                | faculty_count |
+---------------+--------------------------------+---------------+
|             1 | Computer Science               |            14 |
|             2 | Electronics and Communications |             8 |
|             3 | Mechanical Engineering         |             8 |
|             4 | Civil Engineering              |             8 |
|             5 | Mathematics                    |             8 |
+---------------+--------------------------------+---------------+
5 rows in set (0.00 sec)

We can also apply a WHERE condition to filter the rows in the view based on specific criteria:

SELECT * FROM faculties_per_dept
WHERE faculty_count > 10;

This is yet another way that views and tables are alike.

3.3. DML on Views

Although not recommended, we can perform DML operations such as INSERT, DELETE, or UPDATE through a simple view. DML statements are possible on a simple view that contains a single table.

For example, we can update the name of a Program from the simple view program_details:

UPDATE program_details
SET name = 'Database Minor'
WHERE id = 122;

This command updates the program_details view and the table from which the view retrieves its data, in our case, the Program table.

4. Materialized View

A materialized view stores the results of the query. For instance, we might use it for expensive statements involving subqueries, joins, and aggregations. Saving the result set can drastically improve performance. Therefore, we often use a materialized view for large data sets we need to access in constant time.

4.1. Support

Unfortunately, not all SQL database management systems have the same usage for a materialized view. For example, MySQL doesn’t have a built-in system, so, we might need to use a plug-in such as LeapDB.

With PostgreSQL, we have a DDL command for a materialized view. However, a manual refresh is required to obtain the latest data.

The Oracle database, being more oriented to data warehousing, has an advanced DDL definition to create a materialized view with features such as refresh on schedule or commit. This is done using materialized view logs that record all changes to the data and can be refreshed incrementally.

4.2. Example

Let’s create a materialized view in PostgreSQL with the same query we saw earlier:

CREATE MATERIALIZED CREATE MATERIALIZED VIEW departmental_faculties AS
SELECT d.id AS department_id, d.name AS department_name,
COUNT(DISTINCT f.national_id) as faculty_count
FROM Department d INNER JOIN Faculty f ON d.id = f.department_id
GROUP BY d.id, d.name;

We can query the materialized view using a WHERE condition:

SELECT * 
FROM departmental_faculties
WHERE department_id = 5;

+---------------+-----------------+---------------+
| department_id | department_name | faculty_count |
+---------------+-----------------+---------------+
|             5 | Mathematics     |             8 |
+---------------+-----------------+---------------+
(1 row)

Finally, since a view resembles a database table, we can also apply indexes or partitions to a materialized view to improve the query performance:

CREATE UNIQUE INDEX ON departmental_faculties (
   department_id, department_name, faculty_count
);

Notably, we can’t create indexes like this on simple and complex views.

5. How Simple and Complex Views Differ From Materialized Views

With a regular view (simple or complex), we can simplify a query statement or restrict access to database data by granting its usage to some specific users or tenants of the database. However, each time we access a regular view, it runs the relative query in the background.

On the other hand, a materialized view stores data persistently on the main storage. It serves as a snapshot view of the data. Although it’s static data, we can refresh it if required. Materialized views can improve query performance resulting in faster access. We can choose a materialized view when rapid data retrieval and performance are crucial. However, it’s important to note that the accessed data may not always be the most up-to-date version.

Although still in use, views are getting deprecated due to the rise of NoSQL databases such as key-value storage which offers very low latency to data access.

Nonetheless, materialized views are still prevalent, particularly for offline data access or within SQL analytics databases.

6. Conclusion

In this article, we explored the distinction between regular views (simple or complex) and materialized views.

Simple or complex views execute the query each time we access them. In contrast, materialized views store data on the disk. The data can be periodically updated based on the view definition. Additionally, we can apply indexes or partitions to a materialized view. Simple or complex views are mainly used for security. Materialized views are still commonly adopted in data warehousing and databases for analytics.