The DELETE_INDEX_STATS procedure deletes statistics related to indexes.
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 NULL, the username used for login is 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 forcibly delete the statistics regardless of the lock status. The default value is FALSE. If set to TRUE, the statistics will be deleted even if they are locked. |
| tabname | Table name. |
Error codes
| 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 be the owner of the table to call this procedure. To call this procedure on an object in the SYS tenant, you must be the owner of the table or have the SYSDBA privilege.
Examples
Delete all statistics on the idx1 index of the tbl1 table owned by the testUser01 user.
obclient> CALL DBMS_STATS.DELETE_INDEX_STATS('testUser01', 'idx1', tabname=>'tbl1');
Query OK, 0 rows affected
Note
In Oracle mode of OceanBase Database, the tabname parameter is not required because indexes are unique within the current user. In MySQL mode, you must explicitly specify the table name because indexes can be non-unique within the current user.