Multilevel Partitioning | Database Design | Teradata Vantage - Multilevel Partitioning - Advanced SQL Engine - Teradata Database

Multilevel partitioning allows each partition at a given level to be further partitioned into subpartitions. Each partition for a level is subpartitioned the same per a partitioning expression or by column partitioning defined for the next lower level. Rows are grouped by the combined partition number, and within a group are ordered first by hash value and then, if for a partitioned primary index, by uniqueness value. The combined partition numbers are mapped 1-to-1 with internal partition numbers on which the rows on an AMP are ordered. The file system orders rows by the internal partition number, rowhash value, and then uniqueness value.

For multilevel partitioning with a primary index, Vantage hash orders the rows within the lowest partition level. Multilevel partitioning undertakes efficient searches by using row partition elimination at the various levels or combinations of levels. See Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 for a description of row partition elimination and its various forms.

Partitioning CHECK Constraint for Multilevel Partitioning

A multilevel partitioned table has the following partitioning CHECK constraint, which the system stores in DBC.TableConstraints. Call this partitioning constraint form 3.

CHECK (/* nn */ constraint [...])

constraint

partitioning_expression IS NOT NULL

nn

Number of levels, or number of partitioning expressions, in the multilevel partitioning. nn can range between 02 and 62, inclusive.

partitioning_expression

Multilevel partitioning level.

Single-level partitioned tables have a different implied table-level partitioning CHECK constraint (see Single-Level Partitioning).

You can use the following query to retrieve a list of tables and join indexes that have PPIs and their partitioning constraint text.

SELECT DatabaseName, TableName (TITLE 'Table/Join Index Name'),             ConstraintText
FROM DBC.IndexConstraintsV
WHERE ConstraintType = 'Q'
ORDER BY DatabaseName, TableName;

You can use a query like the following to retrieve partitioning constraint information for each of the multilevel partitioned objects.

SELECT *
FROM DBC.TableConstraints
WHERE ConstraintType = 'Q'
AND   SUBSTRING(TableCheck FROM 1 FOR 13) >= 'CHECK (/*02*/'
AND   SUBSTRING(TableCheck FROM 1 FOR 13) <= 'CHECK (/*15*/';

The TableCheck column of DBC.TableConstraints contains the unresolved condition text for a table-level constraint check or implicit table-level constraint such as a partitioning constraint. The ConstraintType code for such an implicit table-level constraint is Q for an object with a partitioned primary index, where in the case of a multilevel partitioning, each of the partitioning levels for the index appears once in the order defined for the table in the text contained in TableCheck. For more information, see Teradata Vantage™ - Data Dictionary, B035-1092.

Rows that violate this implied index CHECK constraint, including those whose partitioning expression evaluates to null, are not allowed in the table.

Assume you create the following table:

CREATE TABLE orders (
  o_orderkey      INTEGER NOT NULL,
  o_custkey       INTEGER,
  o_orderstatus   CHARACTER(1) CASESPECIFIC,
  o_totalprice    DECIMAL(13,2) NOT NULL,
  o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
  o_orderpriority CHARACTER(21),
  o_clerk         CHARACTER(16),
  o_shippriority  INTEGER,
  o_comment       VARCHAR(79))
PRIMARY INDEX (o_orderkey)
PARTITION BY (RANGE_N(o_custkey   BETWEEN 0
                                  AND 49999
                                  EACH  100),
              RANGE_N(o_orderdate BETWEEN DATE '2000-01-01'
                                  AND     DATE '2006-12-31'
                                  EACH INTERVAL '1' MONTH))
UNIQUE INDEX (o_orderkey);

The partitioning CHECK constraint SQL text that would be stored in DBC.TableConstraints for this multilevel partitioned primary index is as follows.

CHECK (/*02*/ RANGE_N(o_custkey   BETWEEN 0
                                  AND 49999
                                  EACH  100)
              IS NOT NULL
          AND RANGE_N(o_orderdate BETWEEN DATE '2000-01-01'
                                  AND     DATE '2006-12-31'
                                  EACH INTERVAL '1' MONTH)
              IS NOT NULL )

The maximum size of this partitioning CHECK constraint is 16,000 characters.

Partitioning CHECK Constraints

A partitioned table or join index has the following partitioning CHECK constraint.

CHECK (/*nn bb cc*/ partitioning_constraint_1
       [ AND partitioning_constraint_n ] ... )

nn

The number of partitioning levels.

• For 2-byte partitioning, nn ranges between 01 and 15, inclusive.
• For 8-byte partitioning, nn ranges between 01 and 62, inclusive.

bb

The type of partitioning.

• For 2-byte partitioning, bb is 02.
• For 8-byte partitioning, bb is 08.

cc

The column partitioning level.

• For no column partitioning, cc is 00.
• Otherwise, cc ranges between 01 and nn, inclusive.

partitioning_constraint_i

Each of the partitioning constraints corresponds to a level of partitioning in the order defined for the table or join index.

The constraint specified by partitioning_constraint_i can be the partitioning constraint for any level and can represent either a row partitioning expression or a column partitioning expression.

When partitioning_expression_i is the row partitioning expression at level i, and partitioning constraint is the following.

partitioning_expression_i /*i d+a*/ IS NOT NULL

When partitioning_expression_i is the column partitioning expression at level i is the following.

PARTITION#Li /*i d+a*/ =1

a is the number of additional partitions that could be added.Leading zeros are not specified.

Assume you create the following table:

CREATE TABLE orders (
  o_orderkey      INTEGER NOT NULL,
  o_custkey       INTEGER,
  o_orderstatus   CHARACTER(1) CASESPECIFIC,
  o_totalprice    DECIMAL(13,2) NOT NULL,
  o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
  o_comment       VARCHAR(79))
NO PRIMARY INDEX
PARTITION BY (RANGE_N(o_custkey BETWEEN  0
                                AND 100000
                                EACH     1),
              COLUMN)
UNIQUE INDEX (o_orderkey);

The table-level partitioning CHECK constraint SQL text for this 8-byte partitioning is as follows:

CHECK (/*02 08 02*/ RANGE_N(o_custkey BETWEEN  0
                                      AND 100000
                                      EACH     1
       /*1 100001+485440633518572409*/
       IS NOT NULL AND PARTITION#L2 /*2 9+10*/ =1)

The maximum size of this table-level CHECK constraint is 16,000 characters.

You can use the following request to retrieve the level for the column partitioning for each of the objects that have column partitioning in the system.

SELECT DBaseId, TVMId, ColumnPartitioningLevel
       (TITLE ‘Column Partitioning Level’)
FROM DBC.TableConstraints
WHERE ConstraintType = 'Q'
AND   ColumnPartitioningLevel >= 1
ORDER BY DBaseId, TVMId;

See Single-Level Partitioningand Partitioning CHECK Constraint for Multilevel Partitioning for information about the table-level CHECK constraints that Vantage creates for single-level and multilevel partitioned primary index tables and join indexes.

Row Partition Elimination With Multilevel Partitioning

The following examples taken from various industries present examples of how multilevel partitioned primary indexes and row partition elimination can greatly enhance the performance of a query workload.

Example From the Insurance Industry

There are many cases for which row partition elimination using multiple expressions for WHERE clause predicate filtering can enhance query performance (see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 for details about the various forms of row partition elimination). For example, consider an insurance company that frequently performs an analysis for a specific state and within a date range that constitutes a relatively small percentage of the many years of claims history in its data warehouse.

If an analysis is being performed only for claims filed in Connecticut, only for claims filed in all states in June 2005, or only for Connecticut claims filed during the month of June 2005, a partitioning of the data that allows elimination of all but the desired claims should deliver a dramatic performance advantage.

The following example shows how a claims table could be partitioned by a range of claim dates and subpartitioned by a range of state identifiers using multilevel partitioning.

Consider the following multilevel PPI table definition:

CREATE TABLE claims (
  claim_id    INTEGER NOT NULL,
  claim_date  DATE NOT NULL,
  state_id    BYTEINT NOT NULL,
  claim_info  VARCHAR(20000) NOT NULL)
PRIMARY INDEX (claim_id)
PARTITION BY (RANGE_N(claim_date BETWEEN DATE '1999-01-01'
                                 AND     DATE '2005-12-31'
                                 EACH INTERVAL '1' MONTH),
              RANGE_N(state_id   BETWEEN 1
                                 AND    75
                                 EACH 1))
UNIQUE INDEX (claim_id);

Eliminating all but one month out of their many years of claims history would facilitate scanning of less than 5% of the claims history (because of business growth, there are many more recent than past claims) for satisfying the following query:

SELECT *
FROM claims
WHERE claim_date BETWEEN DATE '2005-06-01' AND DATE '2005-06-30';

Similarly, eliminating all but the Connecticut claims from the many states in which this insurance company does business would make it possible to scan less than 5% of the claims history to satisfy the following query:

SELECT *
FROM claims, states
WHERE claims.state_id = states.state_id
AND   states.state = 'Connecticut';

State selectivity varies by the density of their business book. The company has more business in Connecticut than, for example Oregon and, therefore, a correspondingly larger number of claims for Connecticut. Note that the partitioning in the example specifies up to 75 state_id values to allow for any of the 50 US states plus US territories and protectorates such as Puerto Rico, Guam, American Samoa, the Marshall Islands, Federated States of Micronesia, Northern Mariana Islands, Palau, and the American Virgin Islands in the future.

Combining both of these predicates for row partition elimination makes it possible to scan less than 0.5% of the claims history to satisfy the following query.

SELECT *
FROM claims, states
WHERE claims.state_id = states.state_id
AND   states.state = 'Connecticut'
AND   claim_date BETWEEN DATE '2005-06-01' AND DATE '2005-06-30';

For evenly distributed data, you would expect scanning of less than 0.25% of the data; however, the data for this company is not evenly distributed among states and dates, leading to a higher percentage of data to scan for the query.

Clearly, combining both predicates for row partition elimination has a significant performance advantage. Row partition elimination by both of these columns, as described, provides higher performance, more space efficiency, and more maintenance efficiency than a composite NUSI or join index for most of the queries run at this insurance company.

In fact, the performance advantage described previously could theoretically be achieved with the current single-level partitioning by using a single partitioning expression that combines both state and monthly date ranges. There are issues with using such a complex, single partitioning expression in this scenario but it is possible to do so.

The bigger problem is that a significant portion of the workload at this insurance company does not specify equality conditions on both partitioning columns. Though theoretically possible, Vantage is currently unable to evaluate more complex conditions with such a partitioning expression. With single-level partitioning, you would have to choose between partitioning by state or partitioning by date ranges.

If you chose partitioning by state, and a user submits a query that specifies a narrow date range without a state filter in the WHERE clause, the system does not perform row partition elimination. At this insurance company, both state-level (and, in some cases, also for a specific date range) analysis and enterprise-level (all states, but for a specific date range) analyses are common.

The users performing state-level analysis do not want to be penalized by having their data combined with all other states, but the users performing enterprise-level analyses also want their queries to be reasonably high-performing, at least by getting date range elimination, and be easy to construct.

Example from the Retail Industry

An example for a large retailer is similar to the insurance example, substituting division for state. In this case, the differences in size of partitions are even more exaggerated. For this company, division 1 is all of the United States, and represents greater than 85% of the entire business. But there are a number of divisions in other countries. The retailer currently replicates the data model for each country in order to provide reasonably efficient access to the data for an individual country. However, this is difficult to maintain, and limits the ability to do analyses across the entire company.

With multilevel partitioning, all the data can be stored in the same table, and analyses can be performed efficiently across either all the data, or with subsets of the data. This example is also applicable to manufacturers with multiple divisions and date associated data.

Importance of Partition Order for Specifying Partitioning Expressions

The specification order of partitioning expressions can be important for multilevel partitioning. The system maps multilevel partitioning expressions into a single-level combined partitioning expression. It then maps the resulting combined partition number 1-to-1 to an internal partition number. Rows are in logical RowID order, where a RowID consists of an internal partition number, a row hash value, and a row uniqueness value (RowIDs are identical for single-level and multilevel partitioning).

This implies a partial physical ordering based on how the file system manages the data blocks and cylinders, though this is not a strict relationship. This physical ordering maintains the ordering of partitions (except for a possible wraparound of internal numbers at one point in the internal number sequence for each level). Multilevel partitioning expressions are analogous with a single-level partitioning expression that is identical to the combined partitioning expression for multilevel partitioning, at least in terms of expressing how rows are logically ordered by the file system.

You define the order of the partitioning expressions, and that ordering implies the logically ordering by RowID. Because the partitions at each level are distributed among the partitions of the next higher level in the hierarchy, scanning a partition at a certain level requires skipping some internal partitions.

If the number of rows in each internal partition is large, then skipping to the next internal partition to be read incurs relatively little overhead.

If the system reads or skips only a few rows for each internal partition, performance might be worse than a full-table scan.

Partition expression order does not affect the ability to eliminate partitions, but does affect the efficiency of a partition scan. As a general rule, this should not be a concern if there are many rows or, more specifically, multiple datablocks in each of the nonempty internal partitions.

Row partition elimination at the lower partition levels of a row partition hierarchy requires more skipping, which can both cause more I/O operations and increase the CPU path length.

To achieve optimal performance, you should specify a partitioning expression that is more likely to evoke row partition elimination for queries at a higher level and specify those expressions that are not as likely to evoke row partition elimination either at a lower level, or not at all. You should also consider specifying the row partitioning expression with the greatest number of row partitions at the lowest level.

As previously noted, the order of the row partitioning expressions might not be a significant concern if there are many data blocks per nonempty combined partition.

You can use the following query to find the average number of rows per populated combined row partition.

SELECT AVG(pc)
FROM (SELECT COUNT(*) AS pc
      FROM t
      GROUP BY PARTITION) AS pt;

Assuming the average block size is b and the row size is r, you can use the following query to find the average number of data blocks per nonempty combined row partition. The ideal number of data blocks per nonempty combined row partition is 10 or more.

USING (b FLOAT, r FLOAT)
SELECT (:r / :b) * AVG(pc) 
FROM (SELECT COUNT(*) AS pc
      FROM t
      GROUP BY PARTITION) AS pt;