The GATHER_INDEX_STATS procedure collects statistics on indexes.
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 name of the table. |
| indname | The index name. |
| partname | The partition name. |
| estimate_percent | The estimated percentage of rows. It ranges from [0.000001,100]. |
| stattab | The name of the user table that stores the statistics. |
| statown | The username of the user table that stores the statistics. |
| degree | The degree of parallelism. The default value is NULL, which indicates to use the default value specified in the CREATE TABLE or ALTER TABLE statement. NULL means that if the size of the object does not guarantee parallel execution, the DBMS_STATS package will perform serial execution. |
| granularity | The granularity of the index statistics to be collected (applicable only if the table is partitioned).
|
| no_invalidate | Specifies whether to flush the plan cache during statistics collection. The value TRUE means do not flush the plan cache during statistics collection. |
| force | Specifies whether to forcibly collect statistics regardless of the lock status. The default value is FALSE. If set to TRUE, statistics will be forcibly collected even if they are locked. |
| tabname | The table name. |
Exceptions
| Error code | Description |
|---|---|
| ORA-20000 | The specified index does not exist or you do not have sufficient privileges. |
| ORA-20001 | Invalid input value. |
Remarks
To call this procedure, you must be the owner of the table. If you want to call this procedure on an object owned by the SYS user, you must either be the owner of the table or have the SYSDBA privilege.
Examples
Call the GATHER_INDEX_STATS procedure to collect statistics on the idx index of the tbl1 table owned by 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 of OceanBase Database, you do not need to specify the tabname parameter, because indexes are unique for the current user. In MySQL mode, you must explicitly specify the table name because indexes are non-unique for the current user.