ANALYZE TABLE
Description
Creates a manual collection task for collecting CBO statistics. By default, manual collection is a synchronous operation. You can also set it to an asynchronous operation. In asynchronous mode, after you run ANALYZE TABLE, the system immediately returns whether this statement is successful. However, the collection task will be running in the background and you do not have to wait for the result. You can check the status of the task by running SHOW ANALYZE STATUS. Asynchronous collection is suitable for tables with large data volume, whereas synchronous collection is suitable for tables with small data volume.
Manual collection tasks are run only once after creation. You do not need to delete manual collection tasks.
This statement is supported from v2.4.
Manually collect basic statistics
For more information about basic statistics, see Gather statistics for CBO.
Syntax
ANALYZE [FULL|SAMPLE] TABLE tbl_name (col_name [,col_name])
[WITH SYNC | ASYNC MODE]
PROPERTIES (property [,property])
Parameter description
-
Collection type
- FULL: indicates full collection.
- SAMPLE: indicates sampled collection.
- If no collection type is specified, full collection is used by default.
-
col_name
: columns from which to collect statistics. Separate multiple columns with commas (,
). If this parameter is not specified, the entire table is collected. -
[WITH SYNC | ASYNC MODE]: whether to run the manual collection task in synchronous or asynchronous mode. Synchronous collection is used by default if you do not specify this parameter.
-
PROPERTIES
: custom parameters. IfPROPERTIES
is not specified, the default settings in thefe.conf
file are used. The properties that are actually used can be viewed via theProperties
column in the output of SHOW ANALYZE STATUS.
PROPERTIES | Type | Default value | Description |
---|---|---|---|
statistic_sample_collect_rows | INT | 200000 | The minimum number of rows to collect for sampled collection.If the parameter value exceeds the actual number of rows in your table, full collection is performed. |
Examples
Example 1: Manual full collection
-- Manually collect full stats of a table using default settings.
ANALYZE TABLE tbl_name;
-- Manually collect full stats of a table using default settings.
ANALYZE FULL TABLE tbl_name;
-- Manually collect stats of specified columns in a table using default settings.
ANALYZE TABLE tbl_name(c1, c2, c3);
Example 2: Manual sampled collection
-- Manually collect partial stats of a table using default settings.
ANALYZE SAMPLE TABLE tbl_name;
-- Manually collect stats of specified columns in a table, with the number of rows to collect specified.
ANALYZE SAMPLE TABLE tbl_name (v1, v2, v3) PROPERTIES(
"statistic_sample_collect_rows" = "1000000"
);
Manually collect histograms
For more information about histograms, see Gather statistics for CBO.
Syntax
ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name]
[WITH SYNC | ASYNC MODE]
[WITH N BUCKETS]
PROPERTIES (property [,property]);
Parameter description
-
col_name
: columns from which to collect statistics. Separate multiple columns with commas (,
). If this parameter is not specified, the entire table is collected. This parameter is required for histograms. -
[WITH SYNC | ASYNC MODE]: whether to run the manual collection task in synchronous or asynchronous mode. Synchronous collection is used by default if you do not specify this parameter.
-
WITH N BUCKETS
:N
is the number of buckets for histogram collection. If not specified, the default value infe.conf
is used. -
PROPERTIES: custom parameters. If
PROPERTIES
is not specified, the default settings infe.conf
are used. The properties that are actually used can be viewed via theProperties
column in the output of SHOW ANALYZE STATUS.
PROPERTIES | Type | Default value | Description |
---|---|---|---|
statistic_sample_collect_rows | INT | 200000 | The minimum number of rows to collect. If the parameter value exceeds the actual number of rows in your table, full collection is performed. |
histogram_buckets_size | LONG | 64 | The default bucket number for a histogram. |
histogram_mcv_size | INT | 100 | The number of most common values (MCV) for a histogram. |
histogram_sample_ratio | FLOAT | 0.1 | The sampling ratio for a histogram. |
histogram_max_sample_row_count | LONG | 10000000 | The maximum number of rows to collect for a histogram. |
The number of rows to collect for a histogram is controlled by multiple parameters. It is the larger value between statistic_sample_collect_rows
and table row count * histogram_sample_ratio
. The number cannot exceed the value specified by histogram_max_sample_row_count
. If the value is exceeded, histogram_max_sample_row_count
takes precedence.
Examples
-- Manually collect histograms on v1 using the default settings.
ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON v1;
-- Manually collect histograms on v1 and v2, with 32 buckets, 32 MCVs, and 50% sampling ratio.
ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON v1,v2 WITH 32 BUCKETS
PROPERTIES(
"histogram_mcv_size" = "32",
"histogram_sample_ratio" = "0.5"
);
References
SHOW ANALYZE STATUS: view the status of a manual collection task.
KILL ANALYZE: cancel a manual collection task that is running.
For more information about collecting statistics for CBO, see Gather statistics for CBO.