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 table where the statistics are stored. |
| statown | The name of the user who owns the table where the statistics are stored. |
| degree | The degree of parallelism. The default value is NULL. When NULL is specified, the default value specified by the DEGREE clause of the CREATE TABLE or ALTER TABLE statement is used. If the size of the object cannot be guaranteed to support parallel execution, DBMS_STATS may execute the operation in serial mode. |
| granularity | The granularity of the index statistics to collect. This parameter is applicable only when the table is partitioned.
|
| no_invalidate | Specifies whether to invalidate the plan cache when collecting statistics. If set to TRUE, the plan cache is not invalidated when collecting statistics. |
| force | Specifies whether to forcibly collect statistics and ignore the lock status. The default value is FALSE. If set to TRUE, index statistics are collected even if the statistics are locked. |
| tabname | The name of the table. |
Exceptions
| Error code | Description |
|---|---|
| HY000 |
|
Considerations
To call this procedure, you must be the owner of the table. For objects owned by SYS, you must be the owner of the table or have the SYSDBA privilege.
Examples
Collect statistics for the idx index of the tbl1 table of the testUser01 user.
obclient> CALL DBMS_STATS.GATHER_INDEX_STATS('testUser01', 'idx1', degree=>4, tabname=>'tbl1');
Query OK, 0 rows affected
Note
In OceanBase Database in Oracle mode, you do not need to specify the tabname parameter because indexes are unique under the current user. However, in MySQL mode, you must explicitly specify the table name because indexes are not unique under the current user.