SET_TABLE_STATS procedure - Sets statistics for the table

The SET_TABLE_STATS procedure sets user-provided statistics for the table.

Syntax

Parameters

ownname: An input argument of type VARCHAR(128) that specifies the schema of the table.; The argument is case-sensitive.
tabname: An input argument of type VARCHAR(128) that specifies the name of the table.; The argument is case-sensitive.
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 output argument of type INTEGER that indicates the number of rows in the table.
numblks: An output argument of type INTEGER that indicates the number of pages that the table occupies.
avgrlen: An output argument of type INTEGER that indicates the average row length for the table.
flags: An input argument of type INTEGER for internal use.
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.
cachedblk: An output argument of type INTEGER that indicates the average number of pages in the buffer pool for the object.
cachehit: An output 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

BEGIN
CALL DBMS_STATS.SET_TABLE_STATS(
CURRENT SCHEMA,
'TEST_TABLE_STATS1',
NUMROWS=> 15,
NUMBLKS=> 3,
AVGRLEN=> 20,
CACHEDBLK=> 2,
CACHEHIT=> 30,
FLAGS=> 3,
NO_INVALIDATE=> TRUE,
FORCE=> TRUE
);
END;

DB20000I  The SQL command completed successfully.

SELECT CARD NUMROWS, NPAGES NUMBLKS FROM SYSSTAT.TABLES WHERE TABNAME = 'TEST_TABLE_STATS1' AND TABSCHEMA = CURRENT SCHEMA

NUMROWS              NUMBLKS
-------------------- --------------------
15                    3

1 record(s) selected.

Example 2

The statistics of the table partition are set on the STATSTAB table.

BEGIN
CALL DBMS_STATS.SET_TABLE_STATS(
CURRENT SCHEMA,
'TEST_TABLE_STATS1',
'PART0',
STATOWN=> CURRENT SCHEMA,
STATTAB=> 'STATSTABLE2',
STATID=> 'STATSID2',
NUMROWS=> 2,
NUMBLKS=> 1,
AVGRLEN=> 20,
CACHEDBLK=> 2,
CACHEHIT=> 30,
FLAGS=> 3,
NO_INVALIDATE=> FALSE,
FORCE=> FALSE
);
END;

DB20000I  The SQL command completed successfully.

select STATID,C1,N2,N3,N4 from STATSTABLE2 WHERE STATID='STATSID2'

STATID                     C1                             N2                       N3                       N4
-------------------------- ------------------------------ ------------------------ ------------------------ ------------------------
STATSID2                   TEST_TABLE_STATS1                                     2                       20                        1

1 record(s) selected.