Column-partitioned tables and join indexes can have one of these indexing methods:
- Primary AMP index (PA)
- Primary index (PI)
- No primary index (NoPI)
NoPI is covered in the following topic NoPI Tables, Column-Partitioned NoPI Tables, and Column-Partitioned NoPI Join Indexes.
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.
Benefits and Considerations for Column-Partitioned Tables with a Primary Index
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 they want to implement only a few 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 will be larger than the table without column partitioning.
- The number of table headers increases (compared with column-partitioned NoPI tables), but for wide rows and many 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 nearly unique.
- 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, hash indexes, or secondary indexes.