DELETE_INDEX_STATS procedure - Deletes index statistics

The DELETE_INDEX_STATS procedure deletes index statistics from the system catalog or from specified statistic tables.

Syntax

Parameters

ownname: An input argument of type VARCHAR(128) that specifies the schema of the table.; The argument is case-sensitive.
indname: An input argument of type VARCHAR(128) that specifies the name of the index.
partname: An input argument of type VARCHAR(128) that specifies the name of table partition.
stattab: An input argument of type VARCHAR(128) that specifies the identifier of the table where the current user statistics are to be saved.; The default value is NULL.; The argument is case-sensitive.
statid: An input argument of type VARCHAR(128) that specifies the identifier with which the statistics within stattab are associated.
cascade_parts: An input argument of type BOOLEAN that specifies the deletion of statistics for this table for all underlying partitions.
statown: An input argument of type VARCHAR(128) that specifies the schema that contains the stattab input argument.; The default value is NULL.; The argument is case-sensitive.
no_invalidate: An input argument of type BOOLEAN that, if it is set to TRUE, specifies whether the dependent cursor is to be invalidated.
stattype: An input argument of type VARCHAR(128) that specifies the type of statistical data that is stored in stattab.
force: An input argument of type BOOLEAN that specifies whether statistics are gathered about the object even if it is locked.

Authorization

EXECUTE privilege on the DBMS_STATS module.

Example 1

BEGIN
CALL DBMS_STATS.DELETE_INDEX_STATS(CURRENT SCHEMA,'STATS_INDEX');
END;

DB20000I  The SQL command completed successfully.

SELECT INDNAME, INDCARD, NLEAF, FULLKEYCARD FROM SYSSTAT.INDEXES WHERE INDNAME = 'STATS_INDEX' AND TABSCHEMA = CURRENT SCHEMA

INDNAME                                   INDCARD              NLEAF                FULLKEYCARD                                                                   
----------------------------------------- -------------------- -------------------- --------------------
STATS_INDEX                                                 -1                   -1                   -1

1 record(s) selected.

Example 2

BEGIN
CALL DBMS_STATS.DELETE_INDEX_STATS(
CURRENT SCHEMA,
'STATS_INDEX',
'PART1',
NO_INVALIDATE=>FALSE,
FORCE=>FALSE
);
END

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0438N  Application raised error or warning with diagnostic text:
"Statistics table must be specified.".  SQLSTATE=UD000

Example 3

BEGIN
CALL DBMS_STATS.DELETE_INDEX_STATS(
CURRENT SCHEMA,
'STATS_INDEX',
'PART1',
STATOWN=> CURRENT SCHEMA,
STATTAB=> 'STATSTABLE',
STATID=> 'TABLE2_STAT',
NO_INVALIDATE=>FALSE,
FORCE=>FALSE
);
END;

DB20000I  The SQL command completed successfully.

select STATID,C1,N2,N3,N4 from STATSTABLE WHERE STATID='TABLE2_STATS'

STATID                         C1                             N2                       N3                       N4
------------------------------ ------------------------------ ------------------------ ------------------------ ------------------------
TABLE2_STATS                   STATS_INDEX                                          -1                       -1                       -1

1 record(s) selected.