Sample Exam Questions

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

Sample Question


Select three potential benefits of partitioning a table.

  1. Improving access performance by reducing the disk capacity required by the table.

  2. Improving access performance by reducing the size of the index.

  3. Improving access performance by performing a full scan within a partition instead of an index scan.

  4. Reducing the processing time for operations such as bulk deletion of old data.

  5. Speeding up the process when an UPDATE causes data to move between partitions.

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

Answer and Explanation

Partitioning is a method of dividing a large table into smaller tables, or partitions. For instance, you might partition your sales data into monthly segments based on the date of sale. If partitioned monthly, three years of data would be stored across 36 partitions.

Performance improvements from partitioning occur when access is limited to a specific partition. If access is concentrated on a portion of a large table, and that portion fits within the memory of the database server, there could be a significant increase in performance.

Even though the data is divided into smaller tables, the total volume of data remains the same, so the disk space required by the table does not change.
Indexes are created for each partition, increasing the number of indexes but reducing the size of each individual index file. Depending on the query conditions, it may be sufficient to access only certain indexes. If these indexes fit within the memory, there is potential for a substantial performance improvement.

For instance, if you need to search and aggregate sales data from two months ago, without partitioning, you would use an index to search for data dispersed throughout the table. However, if the data is partitioned by month, you know that the relevant data is only in a specific partition. In such cases, especially when a large amount of data is being retrieved, it might be faster to perform a full scan of that partition without using an index.

If you need to delete all data from a particular month, this operation can be significantly sped up. Instead of deleting individual data points scattered across the table, you can simply DROP the entire partition.

If you update a key column used for partitioning (for example, the sales date) with an UPDATE, the data may move to another partition. Internally, this is just a DELETE operation on the original partition and an INSERT operation on the destination partition. Therefore, it won't be faster compared to a scenario where partitions are not used.

Therefore, the correct answers are B, C, and D.