These guidelines are a starting point, but may not be suitable for all workloads. As you gain experience using column partitioning, you may find situations where alternate choices are more appropriate.
The following are general points about optimizing the performance of column-partitioned tables and join indexes:
- Column partitioning is not optimal for all query types and workloads.
- Test and prove any physical database design, preferably first on a test system with a valid sample of the data and then on the production system with the full data before releasing the design into the production environment.This includes testing all of the following items before putting a column-partitioned table or join index into production use.
- Queries
- Workloads
- Bulk data loads
- Maintenance
- Archive, restore, and copy performance
Also be sure to check the space usage of the tables.
The performance guidelines for column-partitioned tables and join indexes are divided into the following subgroups.
- General performance guidelines for column partitioning
- Guidelines for queries, contexts, and table maintenance
- Guidelines for partitioning column-partitioned tables and join indexes
- Guidelines for specifying table and column attributes for column-partitioned tables
- Guidelines for specifying compression for column-partitioned tables and join indexes
- Guidelines on I/O operations, CPU usage, and disk space usage for column-partitioned tables and join indexes
- Guidelines for collecting statistics on column-partitioned tables and join indexes