Sample Exam Questions
From the objective of OSS-DB Exam Silver
S3.1 SQL commands (Materialized view)
Sample Question
3.07
You created a materialized view mview that combines tables table1 and table2. Choose three appropriate statements.
- The command used to create it is in the format of
CREATE MATERIALIZED VIEW mview AS SELECT ... FROM table1, table2... - Rather than executing the SELECT statement used in the AS clause during creation,
SELECT * FROM mview
is usually faster. - The rows included in the result of executing the SELECT statement used in the AS clause during creation and the result of executing
SELECT * FROM mview
are always the same.A FUNCTION is executed as SELECT func_name(arg1, arg2 ...), whereas a PROCEDURE is executed as CALL proc_name(arg1, arg2 ...). - You updated a row in table1 that is the source of mview, but the SELECT from mview showed the old data prior to the update.
- Although the size of the source table table2 is huge, only the definition statement of mview is saved in the database. Therefore, you do not have to worry about running out of disk space when creating a materialized view.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2024/05/01
Answer and Explanation
Materialized views are a type of view. However, while a normal view only stores the SELECT statement that defines it in the database and executes a SELECT from the original table each time a SELECT is performed from the view, materialized views store data on the disk like a table. When selecting from it, they directly select from the stored data.
Materialized views are created using the CREATE MATERIALIZED VIEW command. It is almost the same as the regular CREATE VIEW command, except for the inclusion of MATERIALIZED.
When selecting from mview, it reads the stored data directly without joining tables, therefore, it is usually faster than re-executing the original SELECT.
Even if the original table is updated, the data stored as a materialized view is not updated. Therefore, the result of executing the SELECT statement used to create it and the result of selecting from mview can be different. Even if the original data is updated, the old data before the update is displayed when selecting from mview. Materialized views are particularly effective in cases where the search results can be data from a certain point in the past, but the execution of the search requires speed.
To update the data in mview to reflect the latest updates, execute
REFRESH MATERIALIZED VIEW mview;
Unlike regular views, materialized views consume disk space. Therefore, especially when the source table is large, it is necessary to pay attention to the disk space.
Therefore, the correct answers are A, B, and D.
© EDUCO All Rights Reserved.