Sample Exam Questions

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

Sample Question


Choose 3 appropriate descriptions of declarative partitioning supported from version 10 of PostgreSQL and partitioning using inheritance supported from previous versions. Assume that the parent table is named parent and the partitions are named partition1, partition2, partition3 ....

  1. In either method, the parent table is created using the usual CREATE TABLE parent... and no special options are needed to use partitions.
  2. When creating a partition, the command is
    CREATE TABLE partition1 PARTITION OF parent...
    for declarative partitioning, and
    CREATE TABLE partition1 () INHERITS (parent)
    for inheritance, both using CREATE TABLE.
  3. In either method, partitions that will store the data are not automatically created when executing the INSERT statement, and partitions need to be created in advance.
  4. In either method, if the parent table and partitions are correctly created with the CREATE command,
    INSERT INTO parent...
    will automatically store the inserted data into the appropriate partition.
  5. In declarative partitioning, all partitions have the same columns and different columns cannot be added, but in partitioning using inheritance, different columns can be added for each partition.

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

Answer and Explanation

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
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
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.