Evaluating the Relative Merits of Partitioning and Not Partitioning - Teradata VantageCloud Lake

For example, a query that examines two months of sales data from a table that maintains two years of sales history can read about 1/12 of the table instead of having to scan the whole table.

The advantages of row partition elimination can be even greater for multilevel partitioned tables (see the examples in Static Row Partition Elimination for some remarkable scan optimizations).

This provides the opportunity for a large performance boost to a wide range of queries. Importantly, the individuals who code those queries do not have to know the partitioning structure of the table and, therefore, there is no need to recode existing SQL applications.

For example, if a table is partitioned by transaction date, the loading of transactions for the current day can be dramatically enhanced, as can the deletion of rows from the table that are no longer necessary.

This disadvantage can be offset by using one of the following strategies:

• Choose a partitioning column that is a member of the primary index column set.
• Define a unique secondary index that can be used to make single row accesses to the table.
• Constrain the values of the partitioning column set to enable the Optimizer to eliminate row partitions when the query search conditions permit.

This disadvantage can be offset when query search conditions allow row partitions to be excluded from the join operation.

The Teradata query optimizer has several special product join and merge join methods for joining row-partitioned tables. See Product Join and Merge Join for descriptions and examples of these join methods.