Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.1 SQL commands (Materialized view)

Sample Question


Choose the two correct descriptions for materialized views and regular views.

  1. Both are created with the CREATE VIEW command.
  2. If the view definitions are the same, searching from materialized views is generally faster.
  3. If the view definitions are the same, a SELECT from either will always return the same result.
  4. Materialized views require disk space according to the amount of data they contain, while regular views do not require disk space.
  5. To modify a query that defines a view, the CREATE OR REPLACE VIEW command is used for a regular view, whereas a special command called REFRESH MATERIALIZED VIEW is used for a materialized view. 

※This sample exam is different from those that appear in the actual OSS-DB Exam.

Answer and Explanation

Materialized views, a feature added in PostgreSQL 9.4, have the characteristic of functioning as both a view and a table.
Regular views are created with the CREATE VIEW command, while materialized views are created with the CREATE MATERIALIZED VIEW command. Both follow the format of 'view name AS query (SELECT statement)' after the command name. Regular views only record the SELECT statement of the query in the database, while materialized views store the result of the query in the database. In other words, the internal processing of CREATE MATERIALIZED VIEW is similar to CREATE TABLE table_name AS SELECT statement.

When searching from a regular view, you need to execute the SELECT statement of the view definition itself and then execute the search SQL. However, when searching from a materialized view, you execute the search directly from the data saved when CREATE MATERIALIZED VIEW is executed, which makes it faster. Especially when the view definition joins multiple tables, contains complex WHERE clauses, or includes grouping operations, searching from a materialized view is faster because it searches from data that has already undergone these processes.
While materialized views are fast, they are not always advantageous. Because the result of the SELECT statement of the view definition is stored in the database, it consumes disk space separately from the data in the original table. In the case of regular views, only the SELECT statement that defines the view is stored, so no additional disk space is needed.

Also, the data saved when creating a materialized view is not automatically updated even when the original table is updated. While the search result from a view changes according to the update of the original table, the search result from a materialized view does not change and returns the same result as before. To reflect the update of the original table in the materialized view, you use the REFRESH MATERIALIZED VIEW command. Right after CREATE or REFRESH, both views give the same result, but in general, the same result is not always returned from a materialized view and a regular view, so you should use a materialized view only when the result does not necessarily have to be based on the latest data.

To change the SELECT statement that defines a view, you can use CREATE OR REPLACE VIEW, but for materialized views (as of PostgreSQL 11), there is no equivalent command, and you have to execute DROP MATERIALIZED VIEW and CREATE MATERIALIZED VIEW in sequence.

Therefore, the correct answers are B and D.