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.
- If no analyze option is specified,
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