Purpose
You can use this statement to collect the statistics of a table or table partition.
Syntax
ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name_list WITH INT_NUM BUCKETS;
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 (parition_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 table to be analyzed. |
| INT_NUM | The number of buckets, which is an integer. For more information about the number of buckets, see method_opt. |
| use_partition | The name of the partition. If you set the value to 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 columns.
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 affected
- When 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