Sample Exam Questions
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2024/05/01
Partitioning is a technique to improve search efficiency and speed by dividing a large table into several smaller tables. If the data being searched is spread across the disk space occupied by the table, many pages will need to be loaded, but if the data is concentrated on a particular partition, performance can be improved, such as a significant reduction in the number of pages loaded.
Declarative partitioning, supported since version 10 of PostgreSQL, is a convenient way to store data in the appropriate partitions by simply making the appropriate declarations when creating tables and partitions. When creating a parent table, you would use the PARTITION BY clause to specify which column values are used as a base for partitioning, as in
CREATE TABLE parent(...) PARTITION BY ...
To create a partition, in addition to the name of the parent table, you would use the FOR VALUES clause to specify the values that are used as a base to determine the data to be included in that partition, as in
CREATE TABLE partition1 PARTITION OF parent FOR VALUES...
These values are used to automatically determine and sort the destination partition when executing an INSERT statement. Each partition has the same columns that you specified when creating the parent table, and you cannot add columns individually.
PostgreSQL has long supported table inheritance, and executing a SELECT statement on the parent table also selects data from the inherited tables partition1..., so this can be used to implement partitioning features. Unlike declarative partitioning, the parent table is created as a regular table without any special specifications. When executing INSERT INTO parent, the data is inserted directly into the parent table unless special mechanisms are put in place. If you want to automatically distribute the data to each partition, you need to set a trigger on the parent table and create a program that changes the INSERT to the appropriate partition by referencing the key column values. To make it function as effective partitioning, each partition may need to have constraints and indexes added, but since each partition is a regular table (except for the use of inheritance), it is possible to add columns individually.
Both in declarative partitioning and in partitioning using inheritance, partitions that will store the data are not automatically created, and the necessary partitions must be created in advance.
Therefore, the correct answers are B, C, and E.
© EDUCO All Rights Reserved.