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 |
Explanation |
|---|---|
| ownname | The schema to which the table belongs. |
| indname | Index name. |
| partname | Partition name. |
| estimate_percent | Estimated percentage of rows. The valid range is [0.000001,100]. |
| stattab | Specifies the table name of the user table for storing statistics. |
| statown | Specifies the username of the user table for storing statistics. |
| degree | Degree of parallelism. Default value is NULL. NULL Indicates the use of a CREATE TABLE or ALTER TABLE In the statement, DEGREE The default value of the table specified by the clause. When DEGREE=>NULL or DEGREE=>n is used, if the object size does not guarantee parallel execution, DBMS_STATS may use serial execution. |
| granularity | The granularity of index statistics to be collected (only applicable when the table is partitioned).
|
| no_invalidate | Whether to flush the plan cache during statistics collection. Valid values: TRUE Indicates that the plan cache is not flushed during statistics collection. |
| force | Whether to forcibly collect and ignore the lock status. Default is FALSE. If set to TRUE, index statistics are collected even if the statistics themselves are locked. |
| tabname | Table name. |
Exceptions
Error Code |
Description |
|---|---|
| HY000 |
|
Usage instructions
To call this procedure, you must be the owner of the table. For objects owned by SYS, you must be the table owner or have the SYSDBA privilege to call this procedure.
Examples
Gather statistics on index idx of table tbl1 for user testUser01.
obclient> CALL DBMS_STATS.GATHER_INDEX_STATS('testUser01', 'idx1', degree=>4, tabname=>'tbl1');
Query OK, 0 rows affected
Note
The table name must be explicitly specified for OceanBase AI Database because indexes are not unique under the current user.