The DELETE_INDEX_STATS procedure deletes statistics related to indexes.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Syntax
DBMS_STATS.DELETE_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
cascade_parts BOOLEAN DEFAULT TRUE,
no_invalidate BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE,
tabname VARCHAR2 DEFAULT NULL);
Parameters
| Parameter | Description |
|---|---|
| ownname | Username. If you set ownname to NULL, the current username will be used by default. |
| indname | Index name. |
| partname | Partition name. |
| cascade_parts | If the index is partitioned and partname is NULL, set this parameter to TRUE to delete the statistics of the index from all underlying partitions. The default value is TRUE. |
| no_invalidate | Specifies whether to flush the plan cache during statistics collection. Set it to TRUE to prevent the plan cache from being flushed during statistics collection. |
| force | Specifies whether to delete the statistics regardless of lock status. The default value is FALSE. If set to TRUE, the statistics will be deleted even if they are locked. |
| tabname | Table name. |
Errors
| Error code | Description |
|---|---|
| ORA-20000 | The object does not exist or you do not have sufficient privileges. |
| ORA-20005 | The statistics of the object are locked. |
Considerations
You must own the table to call this procedure. To call this procedure on an object in the SYS tenant, you must own the table or have the SYSDBA privilege.
Example
The following sample code deletes all statistics related to the idx1 index of the tbl1 table owned by the testUser01 user.
obclient> CALL DBMS_STATS.DELETE_INDEX_STATS('testUser01', 'idx1', 'tbl1');
Query OK, 0 rows affected
Note
In the Oracle mode of OceanBase Database, you do not need to specify the tabname parameter, because indexes are unique within the current user. In the MySQL mode, you need to specify the table name explicitly because indexes are non-unique within the current user.