DELETE_INDEX_STATS

The DELETE_INDEX_STATS procedure is used to delete statistics related to an index.

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 is used by default.
indname	The index name.
partname	The 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 in all underlying partitions. The default value is TRUE.
no_invalidate	Specifies whether to clear the Plan Cache when collecting statistics. Set to TRUE to not clear the Plan Cache when collecting statistics.
force	Specifies whether to forcibly delete the statistics and ignore 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
HY000	The table does not exist or the user does not have sufficient privileges. The statistics of the object are locked.

Usage

To call this procedure, you must be the owner of the table.

Examples

Delete all statistics of the idx1 index of the tbl1 table for the user testUser01.

CALL DBMS_STATS.DELETE_INDEX_STATS('testUser01', 'idx1', tabname=>'tbl1');
Query OK, 0 rows affected

info

seekdb requires an explicit table name because the index is not unique under the current user.