GATHER_CATALOG_TABLE_STATS

The GATHER_CATALOG_TABLE_STATS statement is used to collect statistics on external tables in the catalog.

Syntax

DBMS_STATS.GATHER_CATALOG_TABLE_STATS (
    catname            VARCHAR(65535),
    dbname             VARCHAR(65535),
    tabname            VARCHAR(65535),
    partname           VARCHAR(65535) DEFAULT NULL,
    estimate_percent   DECIMAL(30, 10) DEFAULT AUTO_SAMPLE_SIZE,
    sample_type        VARCHAR(65535) DEFAULT NULL,
    method_opt         VARCHAR(65535) DEFAULT DEFAULT_METHOD_OPT,
    degree             DECIMAL(30, 10) DEFAULT NULL,
    granularity        VARCHAR(65535) DEFAULT DEFAULT_GRANULARITY,
    force              BOOLEAN DEFAULT FALSE
    );

Parameters

method_opt

method_opt can only be one of the following options, or a combination of both:

• FOR ALL COLUMNS [size_clause]: where size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}.

  • integer: The number of histogram buckets. Value range: [1,2048].
  • REPEAT: Collects histograms only for columns that already have histograms.
  • AUTO: Determines the columns for which histograms are to be collected based on data distribution and column workload.
  • SKEWONLY: Determines the columns for which histograms are collected based on the data distribution of the columns.

• FOR COLUMNS [column_clause]: where column_clause is defined as column_clause := column_name | extension.

  • column_name: the name of the column.
  • extension: A column group or expression in the (column_name, colume_name [, ...]) format.

Examples

Collect statistics on the lineitem table under the Catalog name hive_catalog and Database name test_oss. The table schema is as follows:

• Setting the partition name to NULL indicates the entire table.
• Setting the sampling percentage to 100 indicates a full sample.
• The sampling method is set to BLOCK, indicating block sampling.
• Column statistics collection method is set to NULL, indicating the use of the default.
• The degree of parallelism is set to 32.

obclient> CALL DBMS_STATS.GATHER_CATALOG_TABLE_STATS (
    'hive_catalog',
    'test_oss',
    'lineitem',
    NULL,
    100,
    'BLOCK',
    NULL,
    32
);