ANALYZE

Purpose

You can use this statement to collect the statistics of a table or table partition.

Syntax

ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name_list WITH INT_NUM BUCKETS;

When the session variable enable_sql_extension is set to TRUE, you can use the following syntax in Oracle mode:

ANALYZE TABLE table_name [use_partition] COMPUTE STATISTICS [ for_clause ];

use_partition:
    PARTITION (partition_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])

Parameters

Examples

• Collect statistics of the test table, with the number of buckets being 30 for columns.

  obclient> CREATE TABLE test (c1 NUMBER(30) PRIMARY KEY,c2 VARCHAR(50));
  Query OK, 0 rows affected
  
  obclient> ANALYZE TABLE test UPDATE HISTOGRAM ON c1,c2 with 30 buckets;
  Query OK, 0 rows affected
  

• When the session variable enable_sql_extension is set to TRUE, use the syntax in Oracle mode to collect the statistics of the test table of the user01 user, with the total number of buckets being 128 for each column.

  obclient> ALTER SYSTEM SET enable_sql_extension = TRUE;
  Query OK, 0 rows affected
  
  obclient> ANALYZE TABLE test COMPUTE STATISTICS FOR ALL COLUMNS SIZE 128;
  Query OK, 0 rows affected