ANALYZE TABLE … DROP STATISTICS

Applies to: Databricks SQL Databricks Runtime 18.1 and above

The ANALYZE TABLE … DROP STATISTICS command removes optimizer statistics from a Unity Catalog table. Use this command to clear stale or incorrect statistics so the query optimizer no longer relies on them. To collect new statistics, see ANALYZE TABLE … COMPUTE STATISTICS.

Syntax

ANALYZE TABLE table_name DROP [ MANUAL | AUTO | ALL ] STATISTICS

Parameters

• table_name

  Identifies the table whose statistics you want to drop. The name must not include a temporal specification or options specification or path. If the table cannot be found Databricks raises a TABLE_OR_VIEW_NOT_FOUND error condition.

• { MANUAL | AUTO | ALL }

  An optional qualifier that selects which statistics to drop. If you omit the qualifier, the command drops only manual statistics.

  Qualifier	Description
  MANUAL	The default. Drops statistics collected by ANALYZE TABLE … COMPUTE STATISTICS.
  AUTO	Drops statistics collected by auto-stats and Predictive optimization for Unity Catalog managed tables.
  ALL	Drops both manual and automatic statistics.

Requirements

• The table must be registered in Unity Catalog. Running this command against a Hive metastore table raises an AnalysisException.
• You must have MODIFY privilege on the table.

Examples

SQL

-- Drop only manual statistics. This is the default behavior.
> ANALYZE TABLE main.sales.orders DROP STATISTICS;

-- Drop only statistics collected by auto-stats or predictive optimization.
> ANALYZE TABLE main.sales.orders DROP AUTO STATISTICS;

-- Drop all statistics for the table.
> ANALYZE TABLE main.sales.orders DROP ALL STATISTICS;

Related statements

• ANALYZE TABLE … COMPUTE STATISTICS
• ANALYZE TABLE … COMPUTE STORAGE METRICS
• Predictive optimization for Unity Catalog managed tables
• Cost-based optimizer