To take optimal advantage of row-partitioning as used in Vantage for row-partitioned tables and join indexes, you need a thorough understanding of partitioning expressions, the general notion of partitioning, and the specific attributes that partitioning brings to a table.
The placement of data on the AMPs and the use of row partition elimination by the system can impact different queries differently. You must consider the impact of partitioning on data maintenance. Partitioning increases the size of each row header in a partitioned table or index by either 2 or 8 bytes (partitioned table rows are 4 bytes wider if multivalue compression is specified for the table) and that partitioning also increases the size of each secondary index row by 2 or 8 bytes for each referencing ROWID in the index.
Partitioning is a physical database design consideration, and is more likely to work well if you have done a good logical database design first.
- The partitioning expression
- Queries
This includes both those queries designed specifically to access the partitioned table and the general class of all queries that are likely to access it.
- Performance, access methods, join strategies, row partition elimination
- Ease of altering the partitioning expression
- Effects of the row partitioning on data maintenance for the table
- Backup and restore operations on the table
A successful partitioning expression is one that takes advantage of row partition elimination, supports ease of partition altering with ALTER TABLE, and has no significant negative impact on data maintenance.
Experiment with your intended uses of partitioned tables, considering and analyzing performance tradeoffs between using partitioning or not, partitioning strategies, and using partitioning with, or instead of, other indexing such as secondary and join indexes.
Analyze the maintenance process choices and their performance. Additional maintenance is required if you define a USI to enforce uniqueness on a column set of a partitioned table.
Make sure you are getting the results that you expect. Be sure to review EXPLAIN reports, looking for row partition elimination and rowkey-based joins. Defining a sophisticated partitioning expression is helpful only if the queries in your workloads are able to invoke row partition elimination. Be sure to measure performance for the query workload and for critical queries both before and after creating the partitioned table or join index. Verify that partitioning improves the performance of your maintenance workloads, and weigh the costs against the benefits.