Purpose
This statement is used to collect statistics for a table or a table 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 configuration parameter enable_sql_extension is set to TRUE, you can use the Oracle mode syntax as follows:
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. Supports cross-database tables in the format database_name.table_name. |
| column_name_list | Specifies the list of column names to update or drop histograms, separated by commas. |
| INT_NUM | An integer indicating the number of buckets. The value ranges from 1 to 1024. If this parameter is omitted, the default value is used. For more information about the value of buckets, see method_opt. |
| use_partition | Specifies the partition name in the format PARTITION (partition_name [, partition_name, ...]). If a partition name is specified, statistics are collected only for the specified partition. If the table name is specified, partition statistics are not collected. |
| analyze_statistics_clause | Specifies the method for collecting statistics.
|
| opt_analyze_for_clause | Specifies whether to analyze the entire table or only specific columns. This parameter is optional.
|
| for_all | Specifies to collect statistics for all columns.
|
| for_columns | Specifies to collect statistics for specified columns. The format is FOR COLUMNS [for_columns_list], where for_columns_list can be a column name, a size clause, or a combination of both, separated by commas. |
| for_columns_item | Specifies the column statistics item, which can be a column name, a size clause, or a combination of both. |
| column_clause | Specifies the column name, which can be a single column name or a list of column names enclosed in parentheses. |
| opt_size_clause | An optional histogram size clause. |
| size_clause | Specifies the maximum number of buckets or size option in the histogram.
|
| opt_analyze_sample_clause | An optional sampling clause for specifying the amount of sample data 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 use for estimating statistics. 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 buckets per column.
obclient> CREATE TABLE test (c1 INT, c2 INT, c3 INT);
obclient> ANALYZE TABLE test UPDATE HISTOGRAM ON c1, c2 WITH 30 BUCKETS;
- Drop the histogram for the specified column.
obclient> ANALYZE TABLE test DROP HISTOGRAM ON c1, c2;
- When the tenant-level configuration parameter
enable_sql_extensionis set toTRUE, use the Oracle mode syntax to collect statistics for thetesttable with 128 buckets per column.
obclient> ALTER SYSTEM SET enable_sql_extension = TRUE;
obclient> ANALYZE TABLE test COMPUTE STATISTICS FOR ALL COLUMNS SIZE 128;