Spark SQL Guide

ANALYZE TABLE

Description

The ANALYZE TABLE statement 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 ( partition_col_name [ = partition_col_val ] [ , ... ] ) ]
    COMPUTE STATISTICS [ NOSCAN | FOR COLUMNS col [ , ... ] | FOR ALL COLUMNS ]

Parameters

table_name
The name of an existing table.
PARTITION ( partition_col_name [ = partition_col_val ] [ , ... ] )
Specifies one or more partition column and value pairs. The partition value is optional.
[ 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

 ANALYZE TABLE students COMPUTE STATISTICS NOSCAN;

 DESC EXTENDED students;
     ......
     Statistics	2820 bytes
     ......

 ANALYZE TABLE students COMPUTE STATISTICS;

 DESC EXTENDED students;
     ......
     Statistics	2820 bytes, 3 rows
     ......

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

 DESC EXTENDED students PARTITION (student_id = 111111);
     ......
     Partition Statistics	919 bytes, 1 rows
     ......

 ANALYZE TABLE students COMPUTE STATISTICS FOR COLUMNS name;

 DESC EXTENDED students name;
     =default tbl=students
     col_name	name
     data_type	string
     comment	NULL
     min	NULL
     max	NULL
     num_nulls	0
     distinct_count	3
     avg_col_len	11
     max_col_len	13
     histogram	NULL