The GATHER_INDEX_STATS procedure collects index statistics.
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 the parameter is set to NULL, the current login username is used by default. |
| 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. The default value is 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 |
|---|---|
| OBE-20000 | The index does not exist, or you do not have the required privileges. |
| OBE-20001 | The input value is incorrect. |
Considerations
To call this procedure, you must be the owner of the table. To call this procedure on a table object owned by the SYS user, you must be the table owner or have the SYSDBA privilege.
Examples
Collect the statistics on the idx1 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 mode, you do not need to specify the tabname parameter because the index is unique under the current user. In MySQL mode, you must specify the table name because indexes are not unique under the current user.