ANALYZE

Description

This statement is used to collect statistics for a table or a table partition.

Syntax

ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name_list WITH INT_NUM BUCKETS;

When the enable_sql_extension parameter is set to TRUE, you can use the following syntax:

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])

Parameter description

Parameter	Description
table_name	Specifies the table to analyze.
INT_NUM	An integer indicating the number of buckets. For more information about the value of buckets, see method_opt.
use_partition	Specifies the partition name. If the value is set to the table name, statistics for partitions are not collected.
compute_statistics_clause	Specifies that COMPUTE STATISTICS calculates precise statistics for the analysis target and stores them in the data dictionary. When analyzing a table, statistics for both the table and its columns are collected.
for_clause	Specifies whether to analyze the entire table or only specific columns. FOR TABLE: Specifies to collect statistics for the table only. FOR COLUMNS: Specifies to collect statistics for the specified columns and scalar object attribute columns only. Here, attribute specifies a specific column name in the object. FOR ALL COLUMNS: Specifies to collect statistics for all columns and scalar object attribute columns. FOR ALL INDEXED COLUMNS: Specifies to collect statistics for all indexed columns. Column statistics can be based on the full column or use a histogram specified by SIZE. FOR ALL HIDDEN COLUMNS: Specifies to collect statistics for all hidden columns. Column statistics can be based on the full column or use a histogram specified by SIZE. SIZE: Specifies the maximum number of buckets in the histogram. The value ranges from [1,2048], with a default value of 256.
estimate_statistics_clause	Specifies that ESTIMATE STATISTICS estimates statistics for the analysis target and stores them in the data dictionary.
SAMPLE	Specifies the amount of sampling data used to estimate statistics. If this parameter is omitted, the database optimizer automatically selects an appropriate sampling ratio. ROWS: Specifies the number of rows to sample from the table. PERCENT: Specifies the percentage of rows to sample from the table.

Examples

• Collect statistics for the test table, with 30 buckets per column.

CREATE TABLE test (c1 NUMBER(30) PRIMARY KEY,c2 VARCHAR(50));
Query OK, 0 rows affected (0.001 sec)

ANALYZE TABLE test UPDATE HISTOGRAM ON c1,c2 with 30 buckets;
Query OK, 0 rows affected (0.001 sec)

• When the enable_sql_extension parameter is set to TRUE, use the following syntax to collect statistics for the test table of user user01, with 128 buckets per column.

ALTER SYSTEM SET enable_sql_extension = TRUE;
Query OK, 0 rows affected (0.001 sec)

ANALYZE TABLE test COMPUTE STATISTICS FOR ALL COLUMNS SIZE 128;
Query OK, 0 rows affected (0.001 sec)