15.10 - DataDemographics - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

Contains table demographic information for use by the Teradata Index Wizard and Visual EXPLAIN client utilities.

The following CREATE TABLE request defines the DataDemographics table.

   CREATE TABLE DataDemographics (
     MachineName    VARCHAR(30) CHARACTER SET UNICODE 
                    UPPERCASE NOT CASESPECIFIC NOT NULL,
     TableName      VARCHAR(128) CHARACTER SET UNICODE 
                    UPPERCASE NOT CASESPECIFIC NOT NULL,
     DatabaseName   VARCHAR(128) CHARACTER SET UNICODE 
                    UPPERCASE NOT CASESPECIFIC NOT NULL,
     DBSize         INTEGER NOT NULL,
     CollectedTime  TIMESTAMP(6) NOT NULL,
     AMPNumber      INTEGER NOT NULL,
     ClusterNumber  INTEGER NOT NULL,
     SubTableID     SMALLINT NOT NULL,
     SubTableType   VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC,
     RowCount       DECIMAL(18,0) NOT NULL,
     AvgRowSize     INTEGER NOT NULL,
     QueryID        INTEGER,
     IndexName      VARCHAR(128) CHARACTER SET UNICODE 
                    UPPERCASE NOT CASESPECIFIC,
     DemographicsID INTEGER)
   PRIMARY INDEX (MachineName, TableName, DatabaseName); 

The following table defines the DataDemographics table attributes.

 

Attribute

Definition

MachineName

  • The name of the system to which TableName belongs.
  • Partial NUPI for the table.
  • TableName

  • The name of the table on which demographic data has been collected.
  • Partial NUPI for the table.
  • DatabaseName

  • The name of the containing database for TableName.
  • Partial NUPI for the table.
  • DBSize

    The size in KB of data blocks in TableName.

    CollectedTime

    The timestamp value when the data demographics were collected.

    AMPNumber

    The AMP Vproc number to which the row information pertains.

    ClusterNumber

    The number of the cluster to which AMPNumber belongs.

    SubTableID

    The unique identifier for the subtable in which the data the row describes is stored.

    SubTableType

    The subtable data in text format.

    RowCount

    The cardinality of the subtable.

    AvgRowSize

    The average size of a row in the subtable.

    QueryID

    The value for QueryID depends on how the demographics are captured.

  • If demographics are captured by a COLLECT DEMOGRAPHICS request, QueryID is null.
  • If demographics are captured by an INSERT EXPLAIN request, QueryID is the unique ID of the query.
  • IndexName

    A comma‑separated list of the names of the primary and secondary indexes for the table.

    If an index has no name, then it is represented in this column by a comma‑separated list of the names of the columns that compose it.

    DemographicsID

    Set to 1 if the demographics are captured by a COLLECT DEMOGRAPHICS or INSERT EXPLAIN AND DEMOGRAPHICS statement.

    1 indicates that the capture is on the system on which the row is inserted.

    DemographicsID has different values if the demographics are imported rather than captured directly.

    DataDemographics does not contain information about the spatial distribution of tables across the AMPs.

    You can view those details by querying the system view DBC.TableSizeVX.

    See the following chapter and manuals for more information about the Teradata Index Wizard and Teradata Visual Explain utilities.

  • Teradata Index Wizard User Guide
  • Teradata Visual Explain User Guide
  • Chapter 8: “The Teradata Index Wizard”