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
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 to the equation.
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.
Delete operations can be nearly instantaneous when the partitioning column set matches
the retention policy, there is no secondary index defined on the partitioning column
set, and the delete is the last statement in the transaction.
You can delete all of the rows in a partition if you want to do so. In this case,
there is no journaling of rows if no secondary index is defined on the partitioning
These properties might permit you to drop a secondary index or join index on the partitioning
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