Column-partitioned tables with a primary index provide these benefits:
- Single-AMP access
- More efficient aggregations than NoPI
- Direct merge hash (and rowkey) join to table when a join is on the PI columns
Customers may be interested in this indexing method if implementing a small number of partitions (for example, to vertically partition a PI table into frequently used columns and rarely used columns). Another use may be to partition rows frequently used in WHERE and SELECT clauses. This method provides the advantages of a PI table but reduces the amount of data read for most requests.
Considerations include the following:
- Load times may increase. INSERT-SELECT requires row redistribution and sort by combined partition/hash value of the source relation if the source relation does not have the same PI columns.
- Row header compression and autocompression may be less effective than using column-partitioned primary AMP index or column-partitioned NoPI. In most cases, the column-partitioned table with a PI is larger than the table without column partitioning.
- The number of table headers increases (compared to column-partitioned NoPI tables), but for wide rows and a large number of rows per value, the effect is negligible. The increase in row headers may become excessive as you add more column partitions, especially if the PI is unique or uncommon.
- Sliding window joins are not supported.
- Other limitations apply to all column-partitioned tables. Column-partitioning is not supported for set tables, queue tables, global temporary tables, volatile tables, derived tables, multitable or aggregate join indexes, compressed join indexes, or secondary indexes.