The GATHER_INDEX_STATS procedure is used to collect statistics on indexes.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
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 NULL is specified, the username that is used for logging in will be used by default. |
| indname | The index name. |
| partname | The partition name. |
| estimate_percent | The percentage of rows for estimation. The range is [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 parallelism. The default value is NULL. NULL means that the default value specified in the DEGREE clause of the CREATE TABLE or ALTER TABLE statement will be used. When you use the DEGREE=>NULL or DEGREE=>n option, if parallel execution cannot be guaranteed for the object, 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 specifies not to flush the plan cache. |
| 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 SYS, you must be the owner of the table or have the SYSDBA privilege.
Example
Call the GATHER_INDEX_STATS procedure to collect statistics on 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 the tabname parameter, because indexes are unique within a user. In MySQL mode, you must explicitly specify the table name, because indexes can be non-unique within a user.