COLLECT DEMOGRAPHICS - Teradata Database

Collects various table demographic estimates and writes the data to the DataDemographics table of a user‑defined QCD database for subsequent analysis by the Teradata Index Wizard.

where:

Syntax element …	Specifies …
database_name user_name	Containing database or user for table_name if something different from the current database or user.
table_name	Names of tables for which data demographic estimates are to be collected.
QCD_name	Name of the QCD database into which the collected data demographic estimates are to be written.
ALL	Collect primary and index subtable data demographic estimates. This is the default.
WITH NO INDEX	Exclude index subtable data demographic estimates from the collection and collect only primary data estimates.

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

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_name database.

SELECT on the specified tables or containing databases or users.

Normally invoked using the Teradata Index Wizard utility or the Visual Explain tool.

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 SQL Request and Transaction Processing.

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.

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

Collect demographic estimates on table_1 in database db_1 and write the results into the DataDemographics table of user-defined QCD MyQCD.

     COLLECT DEMOGRAPHICS FOR db_1.table_1 INTO MyQCD;

An entry already exists in the DataDemographics table for the specified table, then it is updated with the currently collected demographic estimates. The value of TimeStamp is updated to reflect the current time.

     COLLECT DEMOGRAPHICS FOR db_1.table_1 INTO MyQCD; /* Inserts data

     into DataDemographics table of MyQCD */

     COLLECT DEMOGRAPHICS FOR db_1.table_1 INTO MyQCD; /* Updates data

     in DataDemographics table of MyQCD */

For more information about index analysis, see:

“COLLECT STATISTICS (QCD Form)” on page 537

“INITIATE INDEX ANALYSIS” on page 560

“INSERT EXPLAIN” on page 577

“RESTART INDEX ANALYSIS” on page 590

SQL Request and Transaction Processing

Teradata Index Wizard User Guide