Description
This statement is used to collect statistics for a table or partition.
Syntax
ANALYZE TABLE table_name [, table_name, ...]
ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name_list [WITH INT_NUM BUCKETS]
ANALYZE TABLE table_name DROP HISTOGRAM ON column_name_list
When the tenant-level parameter enable_sql_extension is set to TRUE, you can use the following syntax:
ANALYZE TABLE table_name [use_partition] analyze_statistics_clause
use_partition:
PARTITION (partition_name [, partition_name, ...])
analyze_statistics_clause:
COMPUTE STATISTICS [opt_analyze_for_clause]
| ESTIMATE STATISTICS [opt_analyze_for_clause] [opt_analyze_sample_clause]
opt_analyze_for_clause:
FOR TABLE
| for_all
| for_columns
for_all:
FOR ALL [INDEXED | HIDDEN] COLUMNS [opt_size_clause]
for_columns:
FOR COLUMNS [for_columns_list]
for_columns_list:
for_columns_item
| for_columns_list [,] for_columns_item
for_columns_item:
column_clause
| size_clause
| column_clause size_clause
column_clause:
column_name
| (column_name_list)
opt_size_clause:
/* empty */
| size_clause
size_clause:
SIZE AUTO
| SIZE REPEAT
| SIZE SKEWONLY
| SIZE number_literal
opt_analyze_sample_clause:
/* empty */
| SAMPLE INTNUM sample_option
sample_option:
ROWS
| PERCENTAGE
Parameters
Parameter |
Description |
|---|---|
| table_name | Specifies the table to analyze. You can specify multiple tables, separated by commas. Cross-database tables are supported. The format is database_name.table_name. |
| column_name_list | Specify the list of column names to update or delete histograms for. Separate multiple column names with commas. |
| INT_NUM | An integer indicating the number of Buckets. The value range is [1,1024]. If this parameter is omitted, the default value is used. For information about the valid values of Buckets, see Collect statistics manually. |
| use_partition | Specify the partition name in the format of PARTITION (partition_name [, partition_name, ...]). If a partition name is specified, only statistics for the specified partition are collected. If a table name is specified, partition statistics are not collected. |
| analyze_statistics_clause | Specifies the statistics collection method.
|
| opt_analyze_for_clause | Specify whether to analyze the entire table or only specific columns. This parameter is optional.
|
| for_all | Specify to collect statistics for all columns.
|
| for_columns | Specifies to collect statistics for the specified columns. The format is FOR COLUMNS [for_columns_list], where for_columns_list It can be a column name, a size clause, or a combination of both. Multiple items are separated by commas. |
| for_columns_item | Specify the column statistics item, which can be a column name, a size clause, or a combination of both. |
| column_clause | Specify the column name, which can be a single column name or a list of column names enclosed in parentheses. |
| opt_size_clause | Optional histogram size clause. |
| size_clause | Specify the maximum number or size of buckets in the histogram.
|
| opt_analyze_sample_clause | Optional sampling clause, used to specify the amount of data to sample when estimating statistics. If this parameter is omitted, the database optimizer automatically selects an appropriate sampling ratio. |
| SAMPLE | Specifies the amount of sample data to be used for estimating statistics in the analysis objects. The format is SAMPLE INTNUM sample_option, where INTNUM must be greater than or equal to 1. |
| sample_option | Specifies the sampling method.
|
Examples
- Collect statistics for the
testtable with 30 column buckets.
obclient> CREATE TABLE test (c1 INT, c2 INT, c3 INT);
obclient> ANALYZE TABLE test UPDATE HISTOGRAM ON c1, c2 WITH 30 BUCKETS;
- Drops a histogram for a specified column.
obclient> ANALYZE TABLE test DROP HISTOGRAM ON c1, c2;
- When the tenant-level parameter
enable_sql_extensionis set toTRUE, use the following syntax to collect statistics for tabletest, setting the number of buckets for all columns to 128.
obclient> ALTER SYSTEM SET enable_sql_extension = TRUE;
obclient> ANALYZE TABLE test COMPUTE STATISTICS FOR ALL COLUMNS SIZE 128;