Vertica Analytics Platform Version 9.2.x Documentation

DROP_STATISTICS

Removes statistical data on database projections previously generated by ANALYZE_STATISTICS. When you drop this data, the Vertica optimizer creates query plans using default statistics.

Regenerating statistics can incur significant overhead.

Syntax

DROP_STATISTICS ('[[[database.]schema.]table]' [, 'category'] [, 'column[,…]' )

Parameters

[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.

table

The table on which to drop statistics. If set to an empty string, Vertica drops statistics for all database tables and their projections.

category

The category of statistics to drop for the named table, one of the following:

  • BASE (default): Drop histograms and row counts (min/max column values, histogram).
  • HISTOGRAMS: Drop only histograms. Row count statistics remain.
  • ALL: Drop all statistics.
column

The name of a column in table, typically a predicate column. You can specify multiple comma-delimited columns. Vertica narrows the scope of dropped statistics to the specified columns.

Privileges

Non-superuser:

  • Schema: USAGE
  • Table: One of INSERT, DELETE, or UPDATE

Examples

Drop all base statistics for the table store.store_sales_fact:

=> SELECT DROP_STATISTICS('store.store_sales_fact'); 
 DROP_STATISTICS
-----------------
               0
(1 row)

Drop statistics for all table projections:

=> SELECT DROP_STATISTICS (''); 
 DROP_STATISTICS
-----------------
               0
(1 row)

See Also

DROP_STATISTICS_PARTITION