Purpose
You can use this statement to collect the statistics of a table or table partition.
Syntax
ANALYZE
TABLE [ schema. ]table_name
[ PARTITION (partition) | SUBPARTITION (subpartition) ]
{ compute_statistics_clause | estimate_statistics_clause } ;
compute_statistics_clause:
COMPUTE STATISTICS [ for_clause ]
estimate_statistics_clause:
ESTIMATE STATISTICS [ for_clause ]
[ SAMPLE integer { ROWS | PERCENT } ]
for_clause:
FOR
TABLE
| ALL [ INDEXED | HIDDEN ] COLUMNS [ SIZE integer ]
| COLUMNS [ SIZE integer ]
{ column | attribute } [ SIZE integer ]
[ { column | attribute }
[ SIZE integer ]
]
[ FOR
{ TABLE
| ALL [ INDEXED | HIDDEN ] COLUMNS
[ SIZE integer ]
| COLUMNS [ SIZE integer ]
{ column | attribute } [ SIZE integer ]
[ { column | attribute }
[ SIZE integer ]
]...
}
]...
Parameters
| Parameter | Description |
|---|---|
| schema | The schema where the table is located. If this parameter is omitted, the current schema prevails. |
| table_name | The name of the table to be analyzed. |
| PARTITION | SUBPARTITION | The partition or subpartition whose statistics are to be collected. If a partition is specified, the information of all subpartitions under this partition is also analyzed. |
| 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 certain 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 this parameter is omitted, 1064 rows are sampled.
|
Examples
Collect the statistics of the
tbl1table.obclient> ANALYZE TABLE tbl1 COMPUTE STATISTICS; Query OK, 0 rows affectedCollect the statistics of all indexed columns in the
tbl1table.obclient> ANALYZE TABLE tbl1 COMPUTE STATISTICS FOR ALL INDEXED COLUMNS SIZE 100; Query OK, 0 rows affected