• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. ANALYZE

ANALYZE

Function

ANALYZE collects statistics about ordinary tables in a database, and stores the results in the PG_STATISTIC system catalog. The execution plan generator uses these statistics to determine which one is the most effective execution plan.

If no parameters are specified, ANALYZE analyzes each table and partitioned table in the current database. You can also specify table_name, column, and partition_name to limit the analysis to a specified table, column, or partitioned table.

Precautions

Non-temporary table cannot be analyzed in an anonymous block, transaction block, function, or stored procedure. You can do ANALYZE to a temporary table in a stored procedure.

Syntax

  • Collect statistics information about a table.
    { ANALYZE | ANALYSE } [ VERBOSE ]
        [ table_name [ ( column_name [, ...] ) ] ];
  • Collect statistics about a partitioned table.
    { ANALYZE | ANALYSE } [ VERBOSE ]
        [ table_name [ ( column_name [, ...] ) ] ]
        PARTITION ( patrition_name ) ;
    NOTE:

    An ordinary partitioned table supports the syntax but not the function of collecting statistics on specified partitions.

  • Collect statistics about a foreign table.
    { ANALYZE | ANALYSE } [ VERBOSE ]
        { foreign_table_name | FOREIGN TABLES };
  • Collect statistics about multiple columns.
    {ANALYZE | ANALYSE} [ VERBOSE ]
        table_name (( column_1_name, column_2_name [, ...] ));
    NOTE:
    • When collecting statistics about multiple columns, set default_statistics_target to a negative value to use the percentage sampling mode.
    • The statistics about a maximum of 32 columns can be collected at a time.
    • You are not allowed to collect statistics about multiple columns in system catalogs or foreign tables.

Parameter Description

  • VERBOSE

    Enables the display of progress messages.

    NOTE:

    If this parameter is specified, progress information is displayed by ANALYZE to indicate the table that is being processed, and statistics about the table are printed.

  • table_name

    Specifies the name (possibly schema-qualified) of a specific table to analyze. If omitted, all regular tables (but not foreign tables) in the current database are analyzed.

    Value range: an existing table name

  • column_name, column_1_name, column_2_name

    Specifies the name of a specific column to analyze. All columns are analyzed by default.

    Value range: an existing column name

  • partition_name

    Assumes the table is a partitioned table. You can specify partition_name following the key word PARTITION to analyze the statistics of this table. Currently the partitioned table supports the syntax of analyzing a partitioned table, but does not execute this syntax.

    Value range: a partition name in a table

  • foreign_table_name

    Specifies the name (possibly schema-qualified) of a specific table to analyze. The data of the table is stored in HDFS.

    Value range: an existing table name

  • FOREIGN TABLES

    Analyzes HDFS foreign tables stored in HDFS and accessible to the current user.

Example

-- Create a table:

CREATE TABLE customer
(
WR_RETURNED_DATE_SK       INTEGER                       ,
WR_RETURNED_TIME_SK       INTEGER                       ,
WR_ITEM_SK                INTEGER               NOT NULL,
WR_REFUNDED_CUSTOMER_SK   INTEGER
)
DISTRIBUTE BY HASH (WR_ITEM_SK);

-- Create a partitioned table:

 CREATE TABLE customer_par
(
WR_RETURNED_DATE_SK       INTEGER                       ,
WR_RETURNED_TIME_SK       INTEGER                       ,
WR_ITEM_SK                INTEGER               NOT NULL,
WR_REFUNDED_CUSTOMER_SK   INTEGER
)
DISTRIBUTE BY HASH (WR_ITEM_SK)
PARTITION BY RANGE(WR_RETURNED_DATE_SK)
(
PARTITION P1 VALUES LESS THAN(2452275),
PARTITION P2 VALUES LESS THAN(2452640),
PARTITION P3 VALUES LESS THAN(2453000),
PARTITION P4 VALUES LESS THAN(MAXVALUE)
)
ENABLE ROW MOVEMENT;

-- Do ANALYZE to update statistical information:

ANALYZE customer;

-- Run ANALYZE VERBOSE to update statistics and display table information:

ANALYZE VERBOSE customer;
INFO:  analyzing "cstore.pg_delta_3394584009"(cn_5002 pid=53078)
INFO:  analyzing "public.customer"(cn_5002 pid=53078)
INFO:  analyzing "public.customer" inheritance tree(cn_5002 pid=53078)
ANALYZE
NOTE:

If any fault related to the environment occurs, check the CN log.

-- Delete the customer table:

DROP TABLE customer;
DROP TABLE customer_par;