DROP STATISTICS Examples | Teradata Vantage - DROP STATISTICS Examples (Optimizer Form) - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

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

Example: Dropping Statistics on a Composite Index

This request drops statistics on a composite index defined on the emp_no and name columns of the employee table.

     DROP STATISTICS INDEX(emp_no, name) ON employee;

Example: Dropping Statistics on a Named Index

This example drops any statistics on an index named unique_1.

     DROP STATISTICS INDEX unique_1 ON table_1;

Example: Dropping Statistics on an Unnamed Index

This example drops any statistics on an unnamed composite index defined on column_1 and column_2.

     DROP STATISTICS INDEX (column_1,column_2) ON table_1;

Example: Dropping All Statistics for a Table

This request drops any statistics that exist for the employee table.

     DROP STATISTICS ON employee;

Example: Dropping PARTITION Statistics

This DROP STATISTICS request drops statistics on the PARTITION column only from the PPI table named table_1.

     DROP STATISTICS COLUMN PARTITION ON table_1;

This DROP STATISTICS request drops statistics on the column named column_1 and the PARTITION column from the PPI table named table_2:

     DROP STATISTICS COLUMN (column_1, PARTITION) ON table_2;

This table-level DROP STATISTICS request drops all the statistics, including PARTITION statistics, from the PPI table named table_3:

     DROP STATISTICS ON table_3;

Example: Dropping a Mix of Single-Column and Multicolumn Statistics

Assume that you collect the following statistics on orders.

     COLLECT STATISTICS COLUMN o_orderdatetime,
                        COLUMN o_orderkey,
                        COLUMN (o_orderdatetime, o_orderkey),
                        COLUMN (o_orderkey, o_orderdatetime) 
     ON orders;

The following request drops the single-column statistics on (o_orderdatetime ), (o_orderkey ) and multicolumn statistics on (o_orderdatetime , o_orderkey ) from orders.

     DROP STATISTICS COLUMN o_orderdatetime,
                     COLUMN o_orderkey,
                     COLUMN (o_orderdatetime, o_orderkey) 
     ON orders;

Example: Dropping Statistics on a Geospatial Column

This DROP STATISTICS request drops statistics on the geospatial column sp in table stat_t2.

     DROP STATISTICS COLUMN(sp) ON stat_t2;