15.10 - PartitioningConstraintsV[X] - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

Integrity

DBC

 

View Column

Data Type

Format

Source Table.Column

DatabaseName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

TableName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

TVM.TVMName

IndexName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

X(128)

TableConstraints.Name

IndexNumber

SMALLINT

---,--9 (explicit)

Indexes.IndexNumber

ConstraintType

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

NOT NULL

X(1)

TableConstraints.ConstraintType

ConstraintText

VARCHAR(16000)

UNICODE

CASESPECIFIC

X(255)

TableConstraints.TableCheck

ConstraintCollation

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

NOT NULL

X(1)

TableConstraints.ConstraintCollation

CollationName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

X(128)

TableConstraints.CollName

CreatorName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

CreateTimeStamp

TIMESTAMP(0)

YYYY-MM-DDBHH:MI:SS

TVFields.CreateTimeStamp

CharSetID

BYTEINT

ZZ9

TableConstraints.CharSetID

SessionMode

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

X(1)

TableConstraints.SessionMode

ResolvedCurrent_Date

DATE

YY/MM/DD

TableConstraints.ResolvedCurrent_Date

ResolvedCurrent
TimeStamp

TIMESTAMP(6)

YYYY-MM-DDBHH:MI:SS.S(F)Z

TableConstraints.ResolvedCurrent_Time
Stamp

DefinedCombined
Partitions

BIGINT

NOT NULL

ZZZZZZZZZZZZZZZZZZ9 (explicit)

TableConstraints.DefinedCombined
Partitions

MaxCombined
Partitions

BIGINT

NOT NULL

ZZZZZZZZZZZZZZZZZZ9 (explicit)

TableConstraints.MaxCombined
Partitions

PartitioningLevels

SMALLINT

NOT NULL

ZZZZ9 (explicit)

TableConstraints.PartitioningLevels

ColumnPartitioning
Level

SMALLINT

NOT NULL

ZZZZ9 (explicit)

TableConstraints.ColumnPartitioning
Level

The X view references these additional tables:

  • DBC.AccessRights
  • DBC.Owners
  • DBC.RoleGrants
  • DBC.Roles
  • The PartitioningConstraintsV[X] system views provide information about partitioning constraints, which are constraints derived from a PARTITON BY clause for a table or join index that has partitioning.

    Use the SHOW TABLE statement, HELP COLUMN statement, and the ColumnsV[X] and DBC.PartitioningConstraintsV[X] views to obtain partitioning information for a table or join index.

    The HELP INDEX statement Ordered or Partitioned attribute shows if an index is partitioned. For a NoPI table, the HELP INDEX statement cannot be used to determine whether or not the table or join index is partitioned.

    For information about the possible values for the ConstraintType column, see Appendix A: “View Column Values.”

    ConstraintText

    This column contains the unresolved constraint text generated from the one or more partitioning levels.

    ConstraintType

    This column is always set to 'Q' and indicates this is a partitioning constraint.

    IndexName

    For a partitioning constraint of a partitioned table or join index with neither a primary index nor a primary AMP index, the IndexName is NULL. For a partitioning constraint of a partitioned table or join index with a primary index or a primary AMP index, the IndexName is NULL or not NULL depending on whether the index was given a name.

    IndexNumber

    This is an internal number assigned to the associated index, if any. This field is set to 1 for a primary index or primary AMP index. Otherwise, it is NULL.

    Possible Values for ColumnPartitioningLevel

     

    Value

    Description

    Between 1 and 62 (inclusive)

    The level number for the column partitioning level, if ConstraintType is ‘Q’

    0

    There is no column partitioning or ConstraintType is not ‘Q’

    Possible Values for ConstraintCollation

     

    Value

    Description

    A

    ASCII

    C

    CHARSET_COLL

    E

    EBCDIC

    J

    JIS_COLL

    M

    MULTINATIONAL

    U

    Use the session collation

    Possible Values for DefinedCombinedPartitions

  • This is the product of the number of defined partitions for each level used.
  • Zero if ConstraintType is not ‘Q’
  • Cannot exceed MaxCombinedPartitions
  • If the partitioning is altered when a table is nonempty, this value can change to be smaller or larger as long as it does not exceed MaxCombinedPartitions.

    Possible Values for MaxCombinedPartitions

  • Zero if ConstraintType is not ‘Q’
  • Greater than or equal to the DefinedCombinedPartitions column
  • Zero if DefinedCombinedPartitions is zero
  • Note: The MaxCombinedPartitions column value cannot change for a nonempty table.

    Possible Values for PartitioningLevels

     

    Value

    Description

    Between 1 and 62 (inclusive)

    The number of partitioning levels for the table or join index, if ConstraintType is ‘Q’.

    0

    The table or join index is not partitioned.

    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. For each example, this information is provided:

  • A brief description.
  • A query that could be used to retrieve the information.
  • Example : Partitioned Objects

    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 : Objects with Column Partitioning

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

    This query could be used to retrieve this information. It is very similar to the query used in “Example 1: Partitioned Objects,” 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 : Objects with 8-byte Multilevel Partitioning

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

  • Have 8-byte, multilevel partitioning, and
  • One of the levels is column parititioning.
  • 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 : Objects with 2-byte Single Level Column Partitioning

    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 parititioning.
  • 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;

    For more information about partitioning levels, see Database Design and SQL Data Definition Language Syntax and Examples.