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. If PROPERTIES is not specified, the default settings in the fe.conf file are used. The properties that are actually used can be viewed via the Properties 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 in fe.conf is used.

• PROPERTIES: custom parameters. If PROPERTIES is not specified, the default settings in fe.conf are used. The properties that are actually used can be viewed via the Properties 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.