- The optimal application for column-partitioned tables is large fact tables or call detail tables that are accessed frequently by analytic workloads.
A join index may provide acceptable performance for requests that depend on primary index access, while column partitioning the base table can enable superior performance for other classes of workloads.
Use column partitioning for tables accessed by analytic queries where the tables are refreshed using bulk loading techniques periodically with possible interim modifications.
Do not use column-partitioned tables for highly volatile data, for cases in which a table is lightly populated with rows, or if the data is to be used in tactical query workloads.
- To facilitate query performance, make sure that you follow at least one, if not both, of the following guidelines.
- Make sure that the number of column partitions accessed by a request does not exceed the number of available column partition contexts.
- Write the request to be highly selective.
Ideally, make sure that both of these guidelines are followed.
Minimize requests in workloads that heavily access column-partitioned tables and do not conform to this recommendation, because their performance is likely to be degraded.
To support acceptable performance for queries that have the desired characteristics, use physical database design options like secondary and join indexes when possible. Understand that additional maintenance costs are incurred because Analytics Database must update index subtables any time their base table columns are updated.
- Measure the INSERT cost for tables that are candidates for column partitioning.
Do not use column partitioning when the increased cost of inserting data is not acceptable or is not offset by improved performance in the query workload.
- Because column-partitioned tables are not recommended for highly volatile data, apply UPDATE operations to column-partitioned tables sparingly.
- Perform DELETE operations on a column-partitioned table either for the entire table or for entire row partitions.
- The Optimizer uses PPICacheThrP to determine the number of available file contexts that can be used at one time to access a partitioned table.
- Periodically refresh or append new rows to a column-partitioned table, or to the row partitions of the column-partitioned table, using INSERT ... SELECT requests that move large quantities of data.
Date or timestamp partitioning may help to improve column-partitioned table maintenance.