SET_INDEX_STATS

The SET_INDEX_STATS procedure is used to set information related to an index.

Syntax

DBMS_STATS.SET_INDEX_STATS (
  ownname            VARCHAR2,
  indname            VARCHAR2,
  partname           VARCHAR2  DEFAULT NULL,
  numrows            NUMBER    DEFAULT NULL,
  force              BOOLEAN   DEFAULT FALSE,
  avgrlen            NUMBER    DEFAULT NULL,
  nummacroblks       NUMBER    DEFAULT NULL,
  nummicroblks       NUMBER    DEFAULT NULL,
  no_invalidate      BOOLEAN   DEFAULT FALSE,
  tabname            VARCHAR2  DEFAULT NULL);

Parameters

Parameter	Description
ownname	The schema where the table is located.
indname	The name of the index.
partname	The name of the partition.
no_invalidate	Specifies whether to invalidate the plan cache when collecting statistics. If set to TRUE, the plan cache will not be invalidated when collecting statistics.
numrows	The number of rows in the index (partition).
force	Specifies whether to forcibly set the index statistics and ignore the lock status. The default is FALSE. If set to TRUE, the index statistics will be set even if the statistics are locked.
avgrlen	The average row length of the index table, in bytes.
nummacroblks	The number of macroblocks in the index table.
nummicroblks	The number of microblocks in the index table.
tabname	The name of the table.

Exceptions

Error Code	Description
HY000	The object does not exist or the user does not have sufficient privileges. The input value is invalid. The statistics of the object are locked.

Usage Notes

• You must be the owner of the table to call this procedure.

• The optimizer estimates the number of cached blocks accessed by an index or statistics table by using cached data. The total cost of an operation includes the I/O cost for reading uncached blocks from disk, the CPU cost for retrieving cached blocks from the buffer, and the CPU cost for processing data.

Examples

Set the information related to the index idx1 for the user testUser01.

CALL DBMS_STATS.SET_INDEX_STATS('testUser01', 'idx1', numrows=>10, avgrlen=>10,
    tabname=>'tbl1');
Query OK, 0 rows affected

info

seekdb requires the table name to be explicitly specified because the index is not unique under the current user.