Sample Exam Questions

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

Sample Question


Choose one incorrect statement about views and materialized views.

  1. Materialized views can be indexed, but views cannot be indexed.

  2. Materialized views consume disk space depending on the amount of data, but views do not consume disk space even as data increases.

  3. Executing the same SELECT statement on a view and a materialized view with the same definition always returns the same result.

  4. When executing "SELECT * FROM view_name" on a view and a materialized view with the same definition, it is usually faster on the materialized view.

  5. Some views can be updated by INSERT/UPDATE/DELETE depending on their definition, but INSERT/UPDATE/DELETE operations cannot be performed on materialized views.

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

Answer and Explanation

The term "materialized" implies that something has been made tangible or concrete. A materialized view is essentially a view, but unlike a regular view that consists solely of a SELECT statement definition, a materialized view contains both the definition and the actual data, making it structurally identical to a table.

When executing a SELECT statement from a view, the system internally runs the SELECT statement that defines the view. This means that it searches for data while evaluating conditions such as table joins and WHERE clauses. However, when executing a SELECT statement from a materialized view, it simply retrieves the data that is physically stored in the view. As a result, a SELECT statement from a materialized view is typically faster. The trade-off is that materialized views consume disk space proportional to the amount of data they hold.

Additionally, you can create indexes to speed up data retrieval from materialized views. For a regular view, while you can create indexes on the original tables that constitute the view, you cannot directly create an index on the view itself.

Materialized views contain data when they are created, but they do not automatically update when the underlying table is modified. The data in a materialized view is only updated when you execute a REFRESH MATERIALIZED VIEW. Regular views always reference the data from the original table, so a SELECT statement from a view displays the most recent data. However, a SELECT statement from a materialized view may display outdated data, leading to potential discrepancies between the two. Due to this characteristic, materialized views are sometimes referred to as 'snapshots'. They are particularly useful in scenarios where the most recent data is not necessarily required, but there is a need to speed up processing. For instance, when you want to analyze data from various perspectives up until the end of the previous month.

A view is considered 'updatable' if it is simple enough based on a single table, without the use of aggregate functions or set operations. In this case, not only SELECT statements but also INSERT, UPDATE, and DELETE operations can be performed. On the contrary, even if the definition of a materialized view is simple, it does not support update operations.

This is a question where you need to select the incorrect option, so the correct answer is C.