Guidelines on Optimizing I/O Operations, CPU Usage, and Disk Space Usage for Column-Partitioned Tables and Join Indexes - Teradata Database

Guidelines on Optimizing I/O Operations, CPU Usage, and Disk Space Usage for Column-Partitioned Tables and Join Indexes

The primary intent of column partitioning is to reduce the number of I/O operations undertaken by a query workload. The following factors can all contribute to reducing the I/O required by query workloads on column‑partitioned tables.

Column partition elimination

Row partition elimination for multilevel partitioned tables and join indexes

Highly selective query predicates

Other factors such as those from the following list can also play a role in reducing the number of I/O operations required to resolve a query.

Autocompression

Row header compression

User‑specified multivalue and algorithmic compression

Trading I/O for CPU might enhance the performance of many workloads on an
I/O‑bound system.

See “Comparing the Number of I/O Operations Required to Answer the Same SELECT Request” on page 263 for more information about I/O operations and column‑partitioned tables.

A secondary intent of column partitioning is to reduce the amount of disk space consumed by table and join index storage. This is particularly effective when Teradata Database can apply row header compression and autocompression to column‑partitioned table and join index data.

Although column partitioning is designed to reduce the number of I/O operations required to process workloads, it is not intended to reduce the CPU usage of queries on column‑partitioned tables.

While there are cases where CPU usage decreases for queries made on a column‑partitioned table, CPU usage can also increase for some functions such as INSERT operations undertaken on a column‑partitioned table.

For a CPU bound system, column partitioning might not provide any benefit, and might even degrade performance. An exception is the case where a subset of the workload that is I/O bound, even if overall the system is CPU bound, in which case column partitioning could be beneficial. Experiment with running your CPU‑bound workloads against both nonpartitioned tables and column‑partitioned tables to determine what the differences are.