COLLECT DEMOGRAPHICS | SQL Statements | Teradata Vantage - COLLECT DEMOGRAPHICS - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

Collects various table demographic estimates and writes the data to the DataDemographics table of a user-defined QCD database for subsequent analysis.

For more information about index analysis, see:

ANSI Compliance

COLLECT DEMOGRAPHICS is a Teradata extension to the ANSI SQL:2011 standard.

Required Privileges

You must have all of the following privileges to perform COLLECT DEMOGRAPHICS:
  • DELETE on the DataDemographics table in QCD_name.
  • INSERT on the DataDemographics table in QCD_name or INSERT on the QCD_namedatabase.
  • SELECT on the specified tables or containing databases or users.

Demographics Collected

COLLECT DEMOGRAPHICS determines and writes the following information for each specified table into the DataDemographics table of the specified query capture database:
  • Subtable type
  • Subtable ID
  • Estimated cardinality
  • Estimated average row length
  • Various system-related information

One row of information is collected in the appropriate DataDemographics subtable of the specified QCD for each AMP that is online at the time the request is performed.

COLLECT DEMOGRAPHICS does not capture information for the QCD table TableStatistics. TableStatistics is used only during the collection of statistics invoked by performing an INSERT EXPLAIN WITH STATISTICS or COLLECT STATISTICS (QCD Form) request.

For more detailed information, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.

Relationship to INSERT EXPLAIN WITH STATISTICS AND DEMOGRAPHICS

The information retrieved for COLLECT DEMOGRAPHICS and INSERT EXPLAIN WITH STATISTICS AND DEMOGRAPHICS is identical. The principal differences are:
  • INSERT EXPLAIN WITH STATISTICS AND DEMOGRAPHICS uses an SQL query to collect the information, while COLLECT DEMOGRAPHICS obtains the information directly.
  • Demographics captured by INSERT EXPLAIN WITH STATISTICS AND DEMOGRAPHICS are automatically deleted whenever you delete the relevant query plans.

    Demographics captured by COLLECT DEMOGRAPHICS are not deleted when you perform associated DROP actions on the subject table and must be deleted explicitly.

COLLECT DEMOGRAPHICS Not Supported From Macros

You cannot specify a COLLECT DEMOGRAPHICS request from a macro. If you execute a macro that contains a COLLECT DEMOGRAPHICS request, the database aborts the request and returns an error.