Benefits and Considerations for Column-Partitioned Tables with a Primary AMP Index
Column-partitioned tables with a primary AMP index provide these benefits:
Single-AMP access
Local access when joining on the primary AMP index columns
Better performance for aggregations than column-partitioned NoPI tables when grouping
by the primary AMP index columns.
More efficient joins than column-partitioned NoPI tables:
Allows a local dynamic hash or product join when performing an equality join on primary
AMP index columns and the other relation has the same primary AMP index or PI columns.
Allows a dynamic hash or product join with redistribution instead of duplication of
the other relation when performing an equality join on primary AMP index columns and
the other relation does not have the same primary AMP index or PI columns.
Reduces memory usage, CPU, and I/O.
Allows for the other relation to be much larger.
Customers may be interested in this indexing method to replace already existing column-partitioned
NoPI tables or if they are interested in implementing column-partitioning for the
first time.
Considerations include the following:
Load times may increase. INSERT-SELECT requires row redistribution if the source does
not have the same primary AMP index or PI columns.
Row header compression and autocompression are similar to that on column-partitioned
NoPI tables.
A column-partitioned primary AMP index may not be defined as UNIQUE, but it can have
a USI or primary key on the same columns.
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, hash
indexes, or secondary indexes.