Sample Exam Questions
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2024/05/01
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.
© EDUCO All Rights Reserved.