- Row partition elimination enables large performance gains to be realizable, and these are visible to end users.
For this particular optimization, more populated partitions are generally better than fewer populated partitions.
- Batch inserts and updates can run faster if the partitioning schema matches the data arrival pattern. This optimization is visible only to the DBA and operations staff.
- Finer granularity of partitions is generally better than a coarser granularity: daily is better than monthly.
- The largest performance improvements occur when there are no secondary indexes.
- Teradata Parallel Data Pump inserts and updates can benefit from more FSG cache hits because of the increased locality of reference when a target table is partitioned on transaction date.
In this case, a finer partition granularity is generally better than a coarser partition granularity.
- Inserts into empty partitions are not journaled.
This optimization is only invoked if the table has no referential integrity constraints.
- Partitioned table rows are each 2 or 8 bytes wider than the equivalent nonpartitioned table row. Partitioned table rows are 4 bytes wider if multivalue compression is specified for the table.
The extra 2 or 8 bytes are used to store the internal partition number for the row.
- You cannot define the primary index of a partitioned table to be unique unless the entire partitioning column set is part of the primary index definition.
You can define a USI on the primary index columns to enforce uniqueness if the complete partitioning column set is not a component of the primary index definition; however, that adds different performance issues.
- Primary index-based row access can be degraded if the partitioning column set is not a component of the primary index.
- If you can define a secondary index on the primary index column set, then performance is independent of the number of partitions.
- If you cannot, or have not, defined a secondary index on the primary index, then having fewer partitions is better than having more partitions, whether achieved by means of the table definition itself or by row partition elimination during query processing.
- Joins of partitioned tables to nonpartitioned tables with the same primary index can be degraded. To combat this, observe the following guidelines:
- Identically partition all tables to be joined with the same primary index when possible and then join them on the partitioning columns.
- Fewer partitions, whether achieved by means of the table definition itself or by row partition elimination, are often better than more partitions for the nonpartitioned-to-partitioned join scenario.