Examples - Advanced SQL Engine - Teradata Database

This section contains examples of the use of this view to retrieve information about objects that have different types of partitioning and different levels of partitioning.

Example: Retrieving Any Partitioned Objects from PartitioningConstraintsV

The PartitioningConstraintsV view is used to retrieve a list of partitioned objects, regardless of the type of partitioning.

This query could be used to retrieve this information. It does not contain any clauses to specify the type of partitioning.

SELECT DatabaseName, TableName (TITLE 'Table/Join Index Name')
  FROM DBC.PartitioningConstraintsV
  ORDER BY DatabaseName, TableName;

Example: Retrieving Objects with Column Partitioning from PartitioningConstraintsV

The PartitioningConstraintsV view is used to retrieve a list of objects that have column partitioning.

This query is very similar to the query used in Example: Retrieving Any Partitioned Objects from PartitioningConstraintsV, but it contains a WHERE clause that specifies the type of partitioning.

SELECT DatabaseName, TableName (TITLE 'Table/Join Index Name')
  FROM DBC.PartitioningConstraintsV
  WHERE ColumnPartitioningLevel >= 1
  ORDER BY DatabaseName, TableName;

Example: Retrieving Objects with 8-byte Multilevel Partitioning and Column Partitioning from PartitioningConstraintsV

The PartitioningConstraintsV view is used to retrieve a list of object that:

• Have 8-byte, multilevel partitioning, and
• One of the levels is column partitioning.

This query could be used to retrieve this information.

SELECT DatabaseName, TableName (TITLE 'Table/Join Index Name')
  FROM DBC.PartitioningConstraintsV
  WHERE MaxCombinedPartitions >= 65536
    AND PartitioningLevels >= 2 AND ColumnPartitioningLevel >= 1
  ORDER BY DatabaseName, TableName;

Example: Retrieving Objects with 2-byte Single Level Column Partitioning from PartitioningConstraintsV

The PartitioningConstraintsV view is used to retrieve a list of objects that:

• Have 2-byte, single-level partitioning, and
• The type of partitioning is column partitioning.

This query could be used to retrieve this information.

SELECT DatabaseName, TableName (TITLE 'Table/Join Index Name')
  FROM DBC.PartitioningConstraintsV
  WHERE MaxCombinedPartitions <= 65535
    AND PartitioningLevels = 1 AND ColumnPartitioningLevel = 1
  ORDER BY DatabaseName, TableName;