- When you are doing an analysis of whether a table should be partitioned or not, always weigh the costs of a given strategy set against its benefits carefully.You should consider all of the following factors at minimum.
- The partitioning expression.Consider all of the following factors when making your analysis of the partitioning expression.
- Would the proposed workloads against the table be better supported by a partitioning expression based on CASE_N, RANGE_N, or some other expression?
- Should the partitioning expression specify a NO RANGE, NO RANGE OR UNKNOWN, or UNKNOWN option?
- Should the table be partitioned on only 1 level or on multiple levels?
- The query workloads that will be accessing the partitioned table.
This factor must be examined at both the specific, or particular, level and at the general, overall level.
Among the factors you should consider are the following.- Performance
- Does a nonpartitioned table perform better than a partitioned table for the given workload and for particularly critical queries?
- Is 1 partitioning strategy more high-performing than others?
- Do other indexes such as USIs, NUSIs, join indexes, or hash indexes improve query performance?
- Does a partitioning expression cause significant row or column partition elimination to occur or not?
- Access methods and predicate conditions
- Primary index access, secondary index access, or something else?
- Do typical queries specify an equality condition on primary index with the complete partitioning column set included?
- Do typical queries specify a non-equality condition on the primary index?
- Join strategies
- Do typical queries specify an equality condition on the primary index column set (and partitioning column set if they are not identical)?
- Do typical queries specify an equality condition on the primary index column set but not on the partitioning column set?
- Do typical query conditions support row partition elimination?
- Performance
- Data maintenance
- What are the relative effects of a partitioned table versus a nonpartitioned table with respect to the maintenance workload for the table?
- If you must define a USI on the primary index to make it unique, how much additional maintenance is required to update the USI subtable?
- Frequency and ease of altering partitions
- Is a process in place to ensure that ranges are added and dropped as necessary?
- Does the partitioning expression permit you to add and drop ranges?
- If the number of rows moved by dropping and adding ranges causes large numbers of rows to be moved, do you have a process in place to instead create a new table with the desired partitioning in place, then INSERT … SELECT or MERGE the source table rows into the newly created target table with error logging?
- If you want to delete rows when their partition is dropped, have you specified NO RANGE, NO RANGE OR UNKNOWN, or UNKNOWN partitions?
- Backup and restore strategy
- The partitioning expression.
- You can specify row partitioning for any of the following table types.
- SET or MULTISET table
- Base data table
- Global temporary table
- Volatile table
- Non-row-compressed join index
- You cannot specify row partitioning for any of the following table types.
- Queue table
- Derived table
- Row-compressed join index
- Hash index
- Secondary index
- You can specify column partitioning for any of the following table types.
- MULTISET table with no primary index
- Base data table with no primary index
- Non-row-compressed join index with no primary index
- You cannot specify column partitioning for any of the following table types.
- SET table
- Global temporary table
- Global temporary trace table
- Volatile table
- Queue table
- Derived table
- Non-row-compressed join index
- You cannot specify a partitioned primary index for any of the following table types.
- Nonpartitioned NoPI table
- Column-partitioned table
- Global temporary trace table
- Row-compressed join index
- Hash index
- Secondary index
- You cannot define a partitioning expression using an identity column as a partitioning column.
- You can only define a PPI as UNIQUE if all the columns referenced in the partitioning expression are also referenced in the column list that defines the primary index.
- You cannot specify LOB columns in a partitioning expression.
- You cannot specify either external or SQL UDFs in a partitioning expression.
- You cannot specify PERIOD columns directly in a partitioning expression, but you can specify the BEGIN and END bound functions on Period columns in a partitioning expression.
- You can only define a PRIMARY KEY or UNIQUE constraint on the same set of columns as the primary index column list if both of the following conditions are also true.
- Not all of the partitioning columns are included in the primary index column list.
- A USI is not explicitly defined on the same column set as the primary index. Any such PRIMARY KEY or UNIQUE constraint implicitly defines a USI on the same set of columns as those defining the primary index column list.
- You cannot define a USI on a table with a PPI if all the partitioning columns are also referenced in the primary index column list.
Instead, define the primary index as unique.
- A NUSI on a table with a PPI must be value-ordered if it is defined on the same set of columns as the primary index column list and all the partitioning columns are duplicated in the primary index column list.
Note that a multicolumn NUSI created with an ORDER BY clause counts as 2 consecutive indexes against the maximum of any mix of 32 secondary, hash, and join indexes that can be defined per table. This includes the system-defined secondary indexes used to implement PRIMARY KEY and UNIQUE constraints.
- Do not assume that NUSIs are needed for column-partitioned tables.
You should only add a NUSI to a column-partitioned table if it provides a benefit.
- Columns referenced in partitioning_expression must be drawn from the set of columns defined for the table on which the partitioning is defined for row-partitioned tables.
This is not true for column-partitioned tables, which do not have a primary index.
- If the data type for the result of partitioning_expression is not INTEGER, BIGINT, or CHARACTER, then the system implicitly casts the value to the INTEGER or BIGINT type, depending on its size.
- If you attempt to insert or update a row of a partitioned table, and the partitioning expression does not produce a result in the range 1 - 9,223,372,036,854,775,807 after casting to BIGINT if the result is not already typed as BIGINT, then the system returns an error for the insert or update operation.
If you want to develop a partitioning expression that permits all rows to be inserted or updated, you can use CASE expressions, options on the CASE_N and RANGE_N functions, and asterisks in the RANGE_N function to construct such an expression. See Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145 for more information about the use of CASE_N and RANGE_N functions.
- Because DATE values can be cast to INTEGER and BIGINT, they are valid values for partitioning expressions; however, only values between DATE ‘1900-01-01’ and DATE ‘1906-12-31’ have values between 1 and 9,223,372,036,854,775,807 when cast to BIGINT.
To compensate, you can adjust the partitioning expression similarly to the following example.
PARTITION BY d1 - 36890
where d1 can then have values between DATE ‘2001-01-01’ and DATE ‘2007-12-31’ when cast to INTEGER values.
An even better way to handle dates is to use only the RANGE_N function when the partitioning column is of DATE type, as shown in the following example.
PARTITION BY RANGE_N(d1 BETWEEN DATE '2001-01-01' AND DATE '2007-12-31') EACH INTERVAL '1' DAY)
- If partitioning_expression is only a CASE_N function, then the number of conditions defined must be less than or equal to 214,748,647 (see the description of CASE_N in
Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145) to allow for the NO CASE [OR UNKNOWN} and UNKNOWN options.
This restriction applies even if the partitioning expression uses the CASE_N function in a subexpression.
Furthermore, the system enforces this restriction even if you do not specify additional options.
- If partitioning_expression is only a RANGE_N function, then the number of combined ranges defined for the function must be less than or equal to 9,223,372,036,854,775,805 to allow for the NO RANGE [OR UNKNOWN] and UNKNOWN options. When ranges are defined explicitly (not using an EACH clause), then other size limits such as those for table headers, request text, implied constraint checks, or EVL code, are likely to be reached before you exceed the limit of 65,535 ranges.
This restriction is enforced even if those options are not specified. This makes it possible to alter the index definition later to include the NO RANGE [OR UNKNOWN] or UNKNOWN options.
This rule does not apply if the partitioning expression includes the RANGE_N function in a subexpression.
See the description of RANGE_N in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145) for additional information and Modifying Partitioning Using the ADD RANGE and DROP RANGE Options for the implication of the NO RANGE option for removing partition ranges from a table using the ALTER TABLE statement.
- You cannot collect statistics on the system-derived PARTITION column for any of the following table types.
- Join index (see CREATE JOIN INDEX)
- Global temporary table
- Volatile table
See Teradata Vantage™ - Database Design, B035-1094 and Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 for more information about partitioned primary indexes.
See Redefining the Primary, Primary AMP, or Partitioning for a Table for information about how to use the ALTER TABLE statement to modify the primary index definition for a table.