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_identifier [ partition_spec ]
    COMPUTE STATISTICS [ NOSCAN | FOR COLUMNS col [ , ... ] | FOR ALL COLUMNS ]

Parameters

table_identifier

Specifies a table name, which may be optionally qualified with a database name.

Syntax: [ database_name. ] table_name

partition_spec

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

Syntax: PARTITION ( partition_col_name [ = partition_col_val ] [ , ... ] )

[ 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