- 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 resolves a query.
- Autocompression
- Row header compression
- User-specified multivalue and algorithmic compression
Trading I/O for CPU may enhance the performance of workloads on an I/O-bound system.
- 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 Vantage can apply row header compression and autocompression to column-partitioned table and join index data.
- Column partitioning is designed to reduce the number of I/O operations required to process workloads, but 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 functions such as INSERT operations undertaken on a column-partitioned table.
For a CPU bound system, column partitioning may provide no benefit, and may 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 can be beneficial. Experiment with running your CPU-bound workloads against both nonpartitioned tables and column-partitioned tables to determine what the differences are.