Performance Issues for Column‑Partitioned Tables
Column partitioning is a physical database design choice that can improve the performance of some classes of workloads. Columnar partitioning is expected to have the following performance impacts.
For example, if 20% of the data in rows is needed to satisfy a query, the I/O for a column‑partitioned table should be approximately 20% of the I/O for a table without column partitioning.
I/O might be further reduced depending on the effectiveness of autocompression. Additional I/O might be needed to reconstruct rows if many columns are projected or used in predicates.
The performance of CPU‑bound workloads might not improve with column partitioning.
There is a range of from 1 or more orders of magnitude of improved performance to 1 or more orders of magnitude of degraded performance for accessing and updating column‑partitioned tables.
The greatest improvement occurs when there is a highly selective predicate on a column in a single‑column partition of a table with hundreds or thousands of columns and only a few columns are projected.
The worst case is when a query is not very selective, most or all of the columns in the table are projected, there are thousands of column partitions, only 8 available column partition contexts, and the table is row‑partitioned in a way that there are very few rows in a populated partition.