DataDemographics - Advanced SQL Engine - Teradata Database

SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
ykx1561500561173.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1142
lifecycle
previous
Product Category
Teradata Vantage™

Function of DataDemographics

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

DataDemographics Table Definition

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);

Attribute Definitions for DataDemographics

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.

Spatial Distribution of a Table Across AMPs

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.

DataDemographics Related Topics

For more information about the Teradata Index Wizard and Teradata Visual Explain utilities, see the following:
  • Teradata® Index Wizard User Guide, B035-2506
  • Teradata® Visual Explain User Guide, B035-2504