The DELETE_INDEX_STATS procedure is used to delete statistics related to indexes.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
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 | 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. |
| cascade_parts | If the index is partitioned and partname is NULL, setting this parameter to TRUE will delete the statistics for the index in all underlying partitions. The default value is TRUE. |
| no_invalidate | Specifies whether to invalidate the plan cache when collecting statistics. Setting this parameter to TRUE means that the plan cache will not be invalidated when collecting statistics. |
| force | Specifies whether to forcibly delete the statistics, ignoring the lock status. The default value is FALSE. If set to TRUE, the index statistics will be deleted even if they are locked. |
| tabname | The table name. |
Exceptions
| Error code | Description |
|---|---|
| OBE-20000 | The object does not exist or the user does not have sufficient privileges. |
| OBE-20005 | The statistics for the object are locked. |
Considerations
To call this procedure, you must be the owner of the table. For objects in the SYS tenant, you must be the owner of the table or have the SYSDBA privilege to call this procedure.
Examples
Delete all statistics for 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 OceanBase Database's Oracle mode, 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.