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 if replacing already existing column-partitioned NoPI tables or if 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 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, or secondary indexes.