From the objective of OSS-DB Exam Silver
- Operation and Management - Basic operation management task (VACUUM, purpose and usage of ANALYZE)
Sample Question
1.12
Choose two statements that accurately describe the ANALYZE function.
- Obtains statistical information such as the frequency with which the table is accessed and the frequency with which it is updated.
- ANALYZE of a huge table takes time because the whole table is analyzed by default.
- With run-time parameters you can control the target of ANALYZE, such as the entire database, only certain tables in the database, only certain columns of a particular table.
- Since the target value of statistical information acquisition can be changed by parameters at the time of execution, it is possible to control the accuracy of statistical information and the time needed for ANALYZE.
- It is executed automatically when automatic vacuum is executed.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2019/03/04
Answer and Explanation
ANALYZE is a command to obtain statistical information on the contents of tables in the database and the results are used by the planner to determine the best execution plan.
When simply saying ""statistical information"", there are two types of statistical information on such ""table contents"" and statistical information on ""database operating status"" such as table access and update number (option A). So you need to careful not to confuse.
ANALYZE acquires statistical information of all tables in the database unless you specifically specify parameters, but you can specify the name of the table to be ANALYZE by parameter or the column name of the table.
Even when the target table is huge, it can be executed it in a short time because we calculate statistical information by sampling some data randomly. In this case, since the statistical information obtained as a result becomes approximate value, in order to increase (or decrease) the accuracy, change the amount of statistical information to be obtained by the setting variable default_statistics_target, or ALTER TABLE ... ALTER COLUMN ... It can be set for each column by SET STATISTICS. However, it can not be changed as a parameter when executing the ANALYZE command.
ANALYZE is automatically executed when automatic vacuum is executed. Conversely, if you do not want automatic vacuuming, it is recommended to periodically run ANALYZE.
Therefore, the correct answers are C and E.