GATHER_INDEX_STATS

The GATHER_INDEX_STATS procedure is used to collect index statistics.

Syntax

DBMS_STATS.GATHER_INDEX_STATS (
  ownname            VARCHAR2,
  indname            VARCHAR2,
  partname           VARCHAR2 DEFAULT NULL,
  estimate_percent   NUMBER DEFAULT AUTO_SAMPLE_SIZE,
  stattab            VARCHAR2 DEFAULT NULL,
  statown            VARCHAR2 DEFAULT NULL,
  degree             NUMBER DEFAULT NULL,
  granularity        VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
  no_invalidate      BOOLEAN DEFAULT FALSE,
  force              BOOLEAN DEFAULT FALSE,
  tabname            VARCHAR2 DEFAULT NULL
);

Parameters

Parameter	Description
ownname	The schema where the table is located.
indname	The name of the index.
partname	The name of the partition.
estimate_percent	The percentage of rows to estimate. Valid range: [0.000001,100].
stattab	The name of the user table where the statistics are stored.
statown	The name of the user who owns the user table where the statistics are stored.
degree	The degree of parallelism. The default value is NULL. When NULL is specified, the default value of the table, specified by the DEGREE clause of the CREATE TABLE or ALTER TABLE statement, is used. If the size of the object cannot guarantee parallel execution, DBMS_STATS may use serial execution.
granularity	The granularity of the index statistics to collect. This parameter is applicable only when the table is partitioned. ALL: Collects statistics for all (subpartitions, partitions, and global) indexes. APPROX_GLOBAL AND PARTITION: Similar to GLOBAL AND PARTITION, but in this case, global index statistics are aggregated at the partition level. This option aggregates all index statistics except the number of distinct values and the number of distinct keys. However, it only aggregates partitions that have index statistics. If partname is NULL or if aggregation is not possible (for example, if the index statistics for one of the partitions are missing), global index statistics are collected. AUTO: Determines the granularity based on the partition type. This is the default value. DEFAULT: Collects index statistics at the global and partition levels. This option is deprecated. We recommend that you use GLOBAL AND PARTITION instead. GLOBAL: Collects global index statistics. GLOBAL AND PARTITION: Collects index statistics at the global and partition levels. Subpartition-level index statistics are not collected for composite partitioned objects. PARTITION: Collects index statistics at the partition level. SUBPARTITION: Collects index statistics at the subpartition level.
no_invalidate	Specifies whether to invalidate the plan cache when collecting statistics. If TRUE is specified, the plan cache is not invalidated when collecting statistics.
force	Specifies whether to force the collection of statistics, ignoring the lock status. The default value is FALSE. If TRUE is specified, index statistics are collected even if the statistics are locked.
tabname	The name of the table.

Exceptions

Error Code	Description
HY000	The index 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 the statistics for the idx index of the tbl1 table of user testUser01.

CALL DBMS_STATS.GATHER_INDEX_STATS('testUser01', 'idx1', degree=>4, tabname=>'tbl1');
Query OK, 0 rows affected

info

seekdb requires you to explicitly specify the table name because the index is not unique under the current user.