GATHER_TABLE_STATS

The GATHER_TABLE_STATS procedure is used to collect statistics for a table and its columns.

Syntax

DBMS_STATS.GATHER_TABLE_STATS (
  ownname             VARCHAR2,
  tabname             VARCHAR2,
  partname            VARCHAR2 DEFAULT NULL,
  estimate_percent    NUMBER DEFAULT AUTO_SAMPLE_SIZE,
  block_sample        BOOLEAN  DEFAULT FALSE,
  method_opt          VARCHAR2 DEFAULT DEFAULT_METHOD_OPT,
  degree              NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
  granularity         VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
  cascade             BOOLEAN DEFAULT NULL,
  no_invalidate       BOOLEAN DEFAULT FALSE,
  stattype            VARCHAR2 DEFAULT 'DATA',
  force               BOOLEAN DEFAULT FALSE,
  hist_est_percent    NUMBER DEFAULT AUTO_SAMPLE_SIZE,
  hist_block_sample   BOOLEAN DEFAULT NULL
  );

Parameters

Parameter	Description
ownname	The schema where the table to be analyzed is located.
tabname	The name of the table.
partname	The name of the partition.
estimate_percent	The percentage of rows to estimate. The valid range is [0.000001,100].
block_sample	Specifies whether to use random block sampling (TRUE) or random row sampling (FALSE). The default value is FALSE. Random block sampling is more efficient, but if the data is not randomly distributed on the disk, the sample values may be correlated.
method_opt	When setting preferences at the global, schema, database, or dictionary level, only the FOR ALL syntax is allowed. Otherwise, method_opt can be one of the following options, or a combination of them: FOR ALL \[INDEXED | HIDDEN\] COLUMNS \[size_clause\] FOR COLUMNS \[column_clause\] where size_clause is defined as size_clause := SIZE \{integer | REPEAT | AUTO | SKEWONLY\}. column_clause is defined as column_clause := column_name | extension name | extension. integer: The number of histogram buckets. The valid range is [1,2048]. REPEAT: Collect histograms only for columns that already have histograms. AUTO: The seekdb optimizer decides whether to collect histograms for a column based on the column's usage. The default number of histogram buckets is 254. SKEWONLY: Collect histograms only for columns with skewed data distributions. The default number of histogram buckets is 254. column_name: The name of the column. extension: A column group or expression in the format (column_name, colume_name [, ...]). The default value is FOR ALL COLUMNS SIZE AUTO.
degree	The degree of parallelism. The default value is NULL. NULL indicates that the default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement will be used. If the size of the object is not guaranteed to support parallel execution, DBMS_STATS may use serial execution when DEGREE=>NULL or DEGREE=>n is specified.
granularity	The granularity of the statistics to be collected (applicable only when the table is partitioned). ALL: Collect statistics for all (subpartitions, partitions, and global) levels. APPROX_GLOBAL AND PARTITION: Similar to GLOBAL AND PARTITION, but in this case, global statistics are aggregated from partition-level statistics. This option aggregates all statistics except the count of distinct values and the count of distinct keys for indexes. However, it only aggregates partitions that have statistics. If partname is NULL or aggregation is not possible (for example, statistics are missing for one of the partitions), global statistics are collected. AUTO: Determine the granularity based on the partition type. This is the default value. DEFAULT: Collect statistics at the global and partition levels. This option is deprecated. We recommend using GLOBAL AND PARTITION instead. GLOBAL: Collect global statistics. GLOBAL AND PARTITION: Collect statistics at the global and partition levels. Subpartition-level statistics are not collected for composite partitioned objects. PARTITION: Collect statistics at the partition level. SUBPARTITION: Collect statistics at the subpartition level.
cascade	Collects statistics for indexes in addition to tables and columns, equivalent to running the GATHER_INDEX_STATS procedure on each index in the schema.
no_invalidate	Specifies whether to invalidate the plan cache when collecting statistics. Setting it to TRUE means the plan cache will not be invalidated when collecting statistics.
stattype	The current location where the statistics are stored.
force	Whether to force the collection of statistics, ignoring the lock status. The default is FALSE. If set to TRUE, statistics will be collected even if they are locked.
hist_est_percent	Specifies the sampling ratio for histograms.
hist_block_sample	Specifies the sampling method for histograms.

Exceptions

Error Code	Description
HY000	The table does not exist or the user does not have sufficient privileges. The input value is invalid.

Usage Notes

You must be the owner of the table to call this procedure.

Examples

Collect statistics for the tbl1 table of the testUser01 user.

CALL DBMS_STATS.GATHER_TABLE_STATS ('testUser01', 'tbl1', method_opt=>'FOR ALL COLUMNS SIZE
   5', granularity=>'ALL', degree=>4, no_invalidate=>FALSE);
Query OK, 0 rows affected