DROP STATISTICS Syntax Elements (Optimizer Form) - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

statistic

Named index for which statistics are to be dropped.

This does not drop SUMMARY statistics for the specified index. To drop SUMMARY statistics on a database object, you must drop all statistics for the object.

Note that you specify column names with a named index only when it is defined with an ORDER BY clause.

UNIQUE
The index for which statistics are to be dropped is unique.
index_name
Name of the index.
ALL
ALL was used to create the index.
column_name_1
Names of one or more columns on which the index exists and for which statistics are to be dropped.
ORDER BY
ORDER BY was used to create the index.
ORDER BY orders the index by the contents of a single column. If you specify ORDER BY but not VALUES or HASH, then VALUES is assumed.
VALUES
ORDER BY VALUES was used to create the index.
VALUES can order a single numeric column of four bytes or less.
HASH
ORDER BY HASH was used to create the index.
HASH hash-orders a single column instead of hash-ordering all columns (the default).
column_name_2
Name of a column on which the index was ordered.

indexed_item

TEMPORARY
Statistics are to be dropped for a materialized global temporary table.
This keyword is not valid for permanent tables and if you use it with a table defined as permanent, an error is returned.
If you do not specify TEMPORARY, but the referenced table is a global temporary table, the statistics are dropped for an empty table, but on the base global temporary table rather than its materialized instance.
If you specify TEMPORARY correctly, but no materialized instance of the global temporary table exists in the session, the system materializes an instance with all of the statistics defined on the base temporary table before dropping statistics for it.
database_name
Containing database for table_name, join_index_name, or hash_index_name if something other than the current database.
user_name
Containing user for table_name, join_index_name, or hash_index_name if something other than the current database.
table_name
Name of one of the following database objects whose statistics are to be dropped.
  • Table or global temporary table
  • Single-table view

    You can only request HELP STATISTICS reports for single-table views.

  • Single-table query

    You can only request HELP STATISTICS reports for single-table queries.

You cannot drop statistics on journal or global temporary trace tables because you cannot collect statistics on those database objects.
join_index_name
Name of the join index for which column or index statistics are to be dropped.
hash_index_name
Name of the hash index for which column or index statistics are to be dropped.

column_index

column_name
Names of the columns for which statistics are to be dropped.
expression
Column expression that was used to collect the statistics to be dropped.
statistics_name
Name of the statistics collected on column_name.
Name of the named column statistics to be dropped. Statistics to be dropped were saved as statistics_name.
PARTITION
Column partition statistics are to be dropped.