Examples - Teradata Vantage

Teradata® VantageCloud Lake

Deployment

VantageCloud

Edition

Lake

Product

Teradata Vantage

Published

January 2023

Language

English (United States)

Last Update

2024-04-03

dita:mapPath

phg1621910019905.ditamap

dita:ditavalPath

pny1626732985837.ditaval

dita:id

phg1621910019905

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 can be used to retrieve a list of partitioned objects, but 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 similar to the query used in the previous example, but 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 can 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 can 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;