ANALYZE TABLE

Description

Creates a manual collection task for collecting CBO statistics. Manual collection is an asynchronous operation. The result for running this command is returned immediately after you run this command. However, the collection task will be running in the background. You can check the status of the task by running SHOW ANALYZE STATUS. Manual collection tasks are run only once after creation. You do not need to delete manual collection tasks.

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 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.

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 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.

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.