DROP STATISTICS (QCD Form) - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Drops sampled statistics on the specified table from the TableStatistics table in the specified QCD_name.

where:

 

Syntax Element …

Specifies …

FROM QCD_name

Name of the QCD database from which statistics on the specified table columns and indexes are to be dropped from the TableStatistics table.

database_name | user_name

Name of the containing database or user for table_name if different from the current database or user.

table_name

Name of the table for which sampled column and index statistics are to be dropped.

COLUMN column_name

Set of non-indexed columns for which sampled statistics are to be dropped.

You cannot drop statistics on a UDT column.

COLUMN PARTITION

Statistics are to be dropped on the system‑derived PARTITION column for a table.

You cannot reference the PARTITION#Ln columns of a table in a DROP STATISTICS request.

See “Collecting QCD Statistics on the PARTITION Column of a Table” on page 540 for more information about the PARTITION column.

INDEX index_name

Name of the index for which sampled statistics are to be dropped.

INDEX column_name

Column set for which sampled statistics are to be dropped.

DROP STATISTICS (QCD Form) is a Teradata extension to the ANSI SQL:2011 standard.

You must have the following privileges to perform DROP STATISTICS (QCD Form):

  • INDEX or DROP TABLE on table_name or its containing database
  • DELETE on the TableStatistics table or its containing QCD database
  • Normally invoked using client‑based database query analysis tools.

    Unlike the DROP STATISTICS (Optimizer Form) statement, DROP STATISTICS (QCD Form) does not drop the statistics kept in the data dictionary for use by the Optimizer. The DROP STATISTICS (QCD Form) drops statistics from the TableStatistics table of the specified QCD database.

    DROP STATISTICS (QCD Form) does not drop statistics from the QCD that were captured using an INSERT EXPLAIN WITH STATISTICS request. These statistics are dropped automatically whenever their corresponding query plan is deleted from the QCD.

    You can place a DROP STATISTICS (QCD Form) request anywhere within a transaction because it is not treated as a DDL statement by the Transaction Manager.

    You cannot DROP STATISTICS on a UDT column.

    The following example drops statistics on all columns and indexes for the orders table from the TableStatistics table of the QCD database named MyQCD.

         DROP STATISTICS FROM MyQCD
         ON orders;

    The following example drops statistics on a single‑column NUSI named orderDateNUPI from the TableStatistics table of the QCD database named MyQCD.

         DROP STATISTICS FROM MyQCD 
         ON orders INDEX orderDateNUPI; 

    The following DROP STATISTICS request drops statistics on the PARTITION column only for the table named table_1 from the TableStatistics table in the QCD named QCD_11:

         DROP STATISTICS FROM QCD_11 ON table_1 
         COLUMN PARTITION;

    The following DROP STATISTICS request drops statistics on the column named column_1 and the system‑derived PARTITION column for the table named table_2 from the TableStatistics table in the QCD named QCD_12:

         DROP STATISTICS FROM QCD_12 ON table_2 
         COLUMN (column_1, PARTITION);

    The following table-level DROP STATISTICS request drops all the statistics, including statistics on the system‑derived PARTITION column, for the table named table_3 from the TableStatistics table in the QCD named QCD_13:

         DROP STATISTICS FROM QCD_13 ON table_3;

    See these related statements for more information:

  • “COLLECT STATISTICS (QCD Form)” on page 537
  • “HELP STATISTICS (QCD Form)” in SQL Data Definition Language.