Sample Exam Questions

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

Sample Question

3.17

Choose the correct two steps for creating a partitioned table (declarative partitioning introduced in PostgreSQL version 10) and its partitions.

  1. Create a partitioned table using the CREATE PARTITIONED TABLE command.
  2. Create a partitioned table by specifying the PARTITIONED BY option in the CREATE TABLE command.
  3. Create partitions with the CREATE PARTITION command.
  4. Create a partition with the CREATE TABLE PARTITION command.
  5. Create a partition by specifying the PARTITION OF option in the CREATE TABLE command.

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

Answer and Explanation

Partitioning is a technique that divides a large table into many smaller parts, which can significantly improve the performance of searches and updates under certain conditions. The divided large table is called a partitioned table, and the individual sections corresponding to the divided parts are called partitions. Generally, each partition has the same structure (column definition) as the parent partitioned table, and the value of a particular column (partition key) determines which partition each row enters.
The creation of a partitioned table is done with the same CREATE TABLE command as for a regular table, but at that time, the PARTITIONED BY option specifies which column is the partition key and that the table is a partitioned table.
Even if you execute an INSERT on a partitioned table, the rows are not stored in the table itself, but are distributed to each partition for INSERT according to the value of the partition key. However, each partition is not automatically created, but needs to be created in advance.
The creation of partitions is also done with the CREATE TABLE command, but at that time, the PARTITION OF option specifies the parent partitioned table, and the FOR VALUES clause specifies the value of the partition key corresponding to that partition. Since the table structure itself is the same as the partitioned table, the clause specifying the column name and data type following the table name in CREATE TABLE is not necessary.

Therefore, the correct answers are B and E.

For detailed procedures on partitioning, please refer to the following pages in the manual:
https://www.postgresql.org/docs/14/ddl-partitioning.html
https://www.postgresql.org/docs/14/sql-createtable.html