The GATHER_INDEX_STATS procedure collects index statistics.
Syntax
PROCEDURE GATHER_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT AUTO_SAMPLE_SIZE,
stattab VARCHAR2 DEFAULT NULL,
statid 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 to which the table belongs. |
| indname | The name of the index. |
| partname | The name of the partition. |
| estimate_percent | The percentage of rows to be estimated. Value range: [0.000001,100]. |
| stattab | The name of the user statistics table for storing statistics. |
| statid | Note: The current version does not support this parameter. |
| statown | The username of the user statistics table for storing statistics. |
| degree | The degree of parallelism (DOP). Default value: NULL. NULL indicates that the default table DOP specified in the DEGREE clause of the CREATE TABLE or ALTER TABLE statement is used. When DEGREE=>NULL or DEGREE=>n is specified, DBMS_STATS may adopt serial execution if parallel execution cannot be guaranteed due to the object size. |
| granularity | The granularity of index statistics to be collected. This parameter is applicable only when the table is partitioned.
|
| force | Specifies whether to perform forced collection and ignore the lock status. Default value: FALSE. If you set the value to TRUE, index statistics will be collected even if statistics are locked. |
| tabname | The name of the table. |
Exceptions
| Error code | Description |
|---|---|
| ORA-20000 | The index does not exist, or you do not have the required privileges. |
| ORA-20001 | The input value is incorrect. |
Considerations
To call this procedure, you must be the owner of the table. If the object is in the sys tenant, you must be the owner of the table or have the SYSDBA privilege.
Examples
Collect the statistics of the idx index in 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 Oracle mode, you do not need to specify the tabname parameter because the index is unique under the current user. In MySQL mode, you must specify the table name because indexes are not unique under the current user.