The DELETE_INDEX_STATS procedure is used to delete 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 | 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 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 it to TRUE means the Plan Cache will not be invalidated when collecting statistics. |
| force | Specifies whether to forcibly delete statistics, ignoring the lock status. The default value is FALSE. If set to TRUE, statistics will be deleted even if they are locked. |
| tabname | The table name. |
Exceptions
| Error Code | Description |
|---|---|
| HY000 |
|
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.
Examples
Delete all statistics for the idx1 index on 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 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.