Sample Exam Questions

From the objective of OSS-DB Exam Silver
- Operation and Management - Operation management work (purpose and usage of VACUUM, ANALYZE)

Sample Question


Choose two answers that best describe ANALYZE.

  1. Because all data in a table is analyzed, it takes time for large tables.
  2. Since an exclusive lock on the table is required, it is not possible to access the data during execution of vacuum.
  3. It can be executed by either the ANALYZE command or the VACUUM command.
  4. Generally, it is sufficient to execute only once for each table.
  5. By optional specification, it can be applied to either  all tables in the database, only specific tables in the database, or only certain columns of a specific table. 

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

Answer and Explanation

ANALYZE aggregates statistical information about the contents of a table. The result is used by the query planner to create an optimal execution plan.

Since ANALYZE requires only table read locks, it can be executed in parallel with other tasks on the table.

In a huge table, random sampling is done rather than analyzing all of the data. Therefore, ANALYZE can be done in a short time even on very large tables.

ANALYZE can be executed by executing the ANALYZE command or by specifying the ANALYZE option in the VACUUM command. All tables in the database are targeted unless you add  options. You can also optionally  specify a specific table or a specific column of a table by specifying the table name, or the table name and column name.

Since the result of ANALYZE has a big influence on the performance of a query, when there is a big change in the contents of the table, for example, when loading or updating a large amount of data, it is desirable to execute ANALYZE each time.

In addition, it is common to perform regularly at times when databases are not used much, such as nightly once a day. Of course, on a table with no updates, if you only run ANALYZE once, you do not need to do it after that.

Therefore, the correct answers are C and E