DROP STATISTICS Examples | Teradata Vantage - DROP STATISTICS Examples (Optimizer Form) - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

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-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
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;