The GATHER_INDEX_STATS procedure collects 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 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. |
| 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.
|
| no_invalidate | Specifies whether to refresh the plan cache during statistics collection. If you set the value to TRUE, the plan cache will not be refreshed during statistics collection. |
| 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 |
|---|---|
| HY000 |
|
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 on 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-compatible mode, you do not need to specify the tabname parameter because the index is unique under the current user. In MySQL-compatible mode, you must explicitly specify the table name because indexes are not unique under the current user.