SET_INDEX_STATS procedure - Sets statistics for the index

The SET_INDEX_STATS procedure sets user-provided statistics for the index.

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.
numrows: An input argument of type INTEGER that indicates the number of rows in the index.
numblks: An input argument of type INTEGER that indicates number of pages that the index occupies.
numdist: An input argument of type INTEGER that indicates the number of distinct keys in the index.
avglblk: An input argument of type INTEGER that indicates the average integral number of leaf pages in which each distinct key appears for this index.
avgdblk: An input argument of type INTEGER that indicates the average integral number of data pages in the table that are pointed to by a distinct key for this index.
clstfct: An input argument of type INTEGER that indicates the clustering factor for the index.
indlevel: An input argument of type INTEGER that indicates the height of the index.
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.
guessq: An input argument of type INTEGER that indicates the guess quality for the index.
cachedblk: An input argument of type INTEGER that indicates the average number of pages in the buffer pool for the object.
cachehit: An input argument of type INTEGER that indicates the average cache hit ratio for the object.
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

Indexes for index partitions can be set only for a user-created statistics table.

BEGIN
CALL DBMS_STATS.SET_INDEX_STATS(
CURRENT SCHEMA,
'STATS_INDEX',
NUMROWS=> 4,
NUMLBLKS=> 3,
NUMDIST=> 4,
AVGLBLK=> 3,
AVGDBLK=> 3,
CLSTFCT=> 2,
INDLEVEL=> 1,
guessq=> 4,
CACHEDBLK=> 2,
CACHEHIT=> 30,
NO_INVALIDATE=> TRUE,
FORCE=> TRUE
);
END;

DB20000I  The SQL command completed successfully.

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

INDNAME                                                            INDCARD              NLEAF                FULLKEYCARD
------------------------------------------------------------------ -------------------- -------------------- --------------------
STATS_INDEX                                                        4                    3                    4

1 record(s) selected.

Example 2

BEGIN
CALL DBMS_STATS.SET_INDEX_STATS(
CURRENT SCHEMA,
'STATS_INDEX',
'PART0',
NUMROWS=> 2,
NUMLBLKS=> 1,
NUMDIST=> 2,
AVGLBLK=> 1,
AVGDBLK=> 1,
CLSTFCT=> 2,
INDLEVEL=> 1,
guessq=> 4,
CACHEDBLK=> 1,
CACHEHIT=> 10,
NO_INVALIDATE=> TRUE,
FORCE=> TRUE
);
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.SET_INDEX_STATS(
CURRENT SCHEMA,
'STATS_INDEX',
STATOWN=> CURRENT SCHEMA,
STATTAB=> 'STATSTABLE',
STATID=> 'TABLE1_STAT',
NUMROWS=> 5,
NUMLBLKS=> 3,
NUMDIST=> 2,
AVGLBLK=> 1,
AVGDBLK=> 1,
CLSTFCT=> 2,
INDLEVEL=> 1,
guessq=> 4,
CACHEDBLK=> 1,
CACHEHIT=> 10,
NO_INVALIDATE=> FALSE,
FORCE=> FALSE
);
END;

DB20000I  The SQL command completed successfully.

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

STATID                         C1                             N2                       N3                       N4
------------------------------ ------------------------------ ------------------------ ------------------------ ------------------------
TABLE1_STAT                    STATS_INDEX                                           5                        2                        3

1 record(s) selected.