ANALYZE TABLE (Databricks SQL)

Collects statistics about the table to be used by the query optimizer to find a better query execution plan.

Syntax

ANALYZE TABLE table_name [ partition_spec ]
    COMPUTE STATISTICS [ NOSCAN | FOR COLUMNS col1 [, ...] | FOR ALL COLUMNS ]

partition_spec
  PARTITION ( { partition_col_name1 [ = partition_col_val1 ] } [, ...] )

Parameters

• table_name

  Identifies the table to be analyzed. The name must not include a temporal specification.

• partition_spec

  An optional parameter that specifies a comma-separated list of key-value pairs for partitions. When specified, partition statistics are returned.

• [ NOSCAN | FOR COLUMNS col [, …] | FOR ALL COLUMNS ]

  If no analyze option is specified, ANALYZE TABLE collects the table’s number of rows and size in bytes.

  • NOSCAN

    Collect only the table’s size in bytes ( which does not require scanning the entire table ).

  • FOR COLUMNS col [, …] | FOR ALL COLUMNS

    Collect column statistics for each column specified, or alternatively for every column, as well as table statistics.

Examples

CREATE TABLE students (name STRING, student_id INT) PARTITIONED BY (student_id);
INSERT INTO students PARTITION (student_id = 111111) VALUES ('Mark');
INSERT INTO students PARTITION (student_id = 222222) VALUES ('John');

ANALYZE TABLE students COMPUTE STATISTICS NOSCAN;

DESC EXTENDED students;
+--------------------+--------------------+-------+
|            col_name|           data_type|comment|
+--------------------+--------------------+-------+
|                name|              string|   null|
|          student_id|                 int|   null|
|                 ...|                 ...|    ...|
|          Statistics|           864 bytes|       |
|                 ...|                 ...|    ...|
|  Partition Provider|             Catalog|       |
+--------------------+--------------------+-------+

ANALYZE TABLE students COMPUTE STATISTICS;

DESC EXTENDED students;
+--------------------+--------------------+-------+
|            col_name|           data_type|comment|
+--------------------+--------------------+-------+
|                name|              string|   null|
|          student_id|                 int|   null|
|                 ...|                 ...|    ...|
|          Statistics|   864 bytes, 2 rows|       |
|                 ...|                 ...|    ...|
|  Partition Provider|             Catalog|       |
+--------------------+--------------------+-------+

ANALYZE TABLE students PARTITION (student_id = 111111) COMPUTE STATISTICS;

DESC EXTENDED students PARTITION (student_id = 111111);
+--------------------+--------------------+-------+
|            col_name|           data_type|comment|
+--------------------+--------------------+-------+
|                name|              string|   null|
|          student_id|                 int|   null|
|                 ...|                 ...|    ...|
|Partition Statistics|   432 bytes, 1 rows|       |
|                 ...|                 ...|    ...|
|        OutputFormat|org.apache.hadoop...|       |
+--------------------+--------------------+-------+

ANALYZE TABLE students COMPUTE STATISTICS FOR COLUMNS name;

DESC EXTENDED students name;
+--------------+----------+
|     info_name|info_value|
+--------------+----------+
|      col_name|      name|
|     data_type|    string|
|       comment|      NULL|
|           min|      NULL|
|           max|      NULL|
|     num_nulls|         0|
|distinct_count|         2|
|   avg_col_len|         4|
|   max_col_len|         4|
|     histogram|      NULL|
+--------------+----------+