Purpose
You can use this statement to collect the statistics of a table or table partition.
Syntax
Update the histograms of specified columns in a table and specify the number of buckets for each column:
ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name_list WITH INT_NUM BUCKETS;Drop the histograms of specified columns in a table:
ANALYZE TABLE table_name DROP HISTOGRAM ON column_name_list;Collect the statistics of a table, including the row count and column data:
ANALYZE TABLE table_name [use_partition] analyze_statistics_clause;Collect the statistics of multiple tables:
ANALYZE TABLE tbl_name1 [, tbl_name2]...
When the session variable enable_sql_extension is set to TRUE, you can use the following syntax in Oracle mode:
ANALYZE TABLE table_name [use_partition] COMPUTE STATISTICS [ for_clause ];
use_partition:
PARTITION (partition_name [,partition_name,....])
| SUBPARTITION(subpartition_name, [,subpartition_name,...])
analyze_statistics_clause:
COMPUTE STATISTICS [analyze_for_clause]
| ESTIMATE STATISTICS [analyze_for_clause] [SAMPLE INTNUM {ROWS | PERCENTAGE}]
analyze_for_clause:
FOR TABLE
| FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
| FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]
size_clause:
SIZE integer
| SIZE REPEAT
| SIZE AUTO
| SIZE SKEWONLY
column:
column_name
| (column_name [, column_name])
Parameters
| Parameter | Description |
|---|---|
| table_name | The name of the target table. The statement analyzes this table to collect its statistics. |
| tbl_name1 [, tbl_name2]... | The names of multiple tables for batch analysis. Separate the table names with commas (,). |
| UPDATE HISTOGRAM ON column_name_list | The columns for which the histograms are to be updated. A histogram is a type of statistics. It can help the optimizer learn about data distribution details. |
| DROP HISTOGRAM ON column_name_list | The columns whose histograms are to be dropped. If a histogram can no longer reflect the actual distribution of data or is no longer needed, you can drop it. |
| INT_NUM | The number of buckets, which is an integer. For more information about the number of buckets, see method_opt. |
| WITH INT_NUM BUCKETS | The number of buckets for the histograms of each column. A bucket is a range in a histogram. Each bucket represents a range of data. |
| use_partition | Optional. The partition name. If the target table is a partitioned table, you can use this parameter to specify to analyze certain partitions or subpartitions. If this parameter is not specified, all partitions are analyzed. If you specify a table name, partition statistics are not collected. |
| compute_statistics_clause | You can specify COMPUTE STATISTICS to calculate the precise statistics of the analysis object and store the statistics in the data dictionary. When you analyze a table, the statistics of both the table and its columns are collected. |
| for_clause | Specifies whether to analyze the entire table or specified columns.
|
| estimate_statistics_clause | You can specify ESTIMATE STATISTICS to estimate the statistics of the analysis object and store the statistics in the data dictionary. |
| SAMPLE | The volume of sampled data for estimating the statistics of the analysis object. If you do not specify this parameter, a proper proportion for sampling is selected based on the database optimizer.
|
Examples
Collect statistics of the
testtable, with the number of buckets being 30 for each column.obclient> CREATE TABLE test (c1 NUMBER(30) PRIMARY KEY,c2 VARCHAR(50)); Query OK, 0 rows affected obclient> ANALYZE TABLE test UPDATE HISTOGRAM ON c1,c2 with 30 buckets; Query OK, 0 rows affectedWhen the session variable
enable_sql_extensionis set toTRUE, use the syntax in Oracle mode to collect the statistics of thetesttable of theuser01user, with the total number of buckets being 128 for each column.obclient> ALTER SYSTEM SET enable_sql_extension = TRUE; Query OK, 0 rows affected obclient> ANALYZE TABLE test COMPUTE STATISTICS FOR ALL COLUMNS SIZE 128; Query OK, 0 rows affected