The GATHER_INDEX_STATS procedure is used to collect index statistics.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this.
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 username. If the username is set to NULL, the current login username will be used by default. |
| indname | The index name. |
| partname | The partition name. |
| estimate_percent | The percentage of rows to estimate. Valid range: [0.000001,100]. |
| stattab | The name of the table to store the statistics. |
| statown | The username of the table to store the statistics. |
| degree | The parallelism. The default value is NULL. NULL indicates that the default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement will be used. If the object size cannot guarantee parallel execution, DBMS_STATS may execute in serial mode. |
| granularity | The granularity of the index statistics to collect (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 will not be invalidated when collecting statistics. |
| force | Specifies whether to forcibly collect statistics and ignore lock status. The default value is FALSE. If set to TRUE, index statistics will be collected even if they are locked. |
| tabname | The table name. |
Exceptions
| Error code | Description |
|---|---|
| OBE-20000 | The index does not exist or the user does not have sufficient privileges. |
| OBE-20001 | The input value is invalid. |
Considerations
To call this procedure, you must be the owner of the table. For objects owned by SYS, you must be the owner or have the SYSDBA privilege to call this procedure.
Examples
Collect statistics for the idx1 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 tabname 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.