SET_COLUMN_STATS procedure - Sets column statistics

The SET_COLUMN_STATS procedure sets column 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.
tabname: An input argument of type VARCHAR(128) that specifies the name of the table.; The argument is case-sensitive.
colname: An input argument of type VARCHAR(128) that specifies the name of the column.
partname: An input argument of type VARCHAR(128) that specifies the name of the 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.
distcnt: An output argument of type INTEGER that indicates the number of distinct values.
density: An output argument of type INTEGER that indicates the column density.
nullcnt: An output argument of type INTEGER that indicates number of NULL values.
avgclen: An output argument of type INTEGER that indicates the average length of columns.
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.
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

You can set column statistics for table partitions only for user-defined statistics tables.

BEGIN
CALL DBMS_STATS.SET_COLUMN_STATS(
CURRENT SCHEMA,
'TEST_TABLE_STATS',
'COL0',
DISTCNT=> 6,
DENSITY=> 5,
NULLCNT=> 5,
AVGCLEN=> 5,
FLAGS=> 3,
NO_INVALIDATE=> TRUE,
FORCE=> TRUE
);
END;

DB20000I  The SQL command completed successfully.

SELECT COLNAME,COLCARD NUMROWS, NUMNULLS NULLCNT,AVGCOLLEN AVGCLEN FROM SYSSTAT.COLUMNS WHERE TABNAME='TEST_TABLE_STATS' AND TABSCHEMA= CURRENT SCHEMA AND COLNAME= 'COL0'

COLNAME                                           NUMROWS              NULLCNT              AVGCLEN                                                               
------------------------------------------------- -------------------- -------------------- -----------
COL0                                                                 6                    5           5                                                             

1 record(s) selected.

Example 2

BEGIN
CALL DBMS_STATS.SET_COLUMN_STATS(
CURRENT SCHEMA,
'TEST_TABLE_STATS',
'COL0',
'PART0',
DISTCNT=> 3,
DENSITY=> 5,
NULLCNT=> 5,
AVGCLEN=> 5,
FLAGS=> 3,
NO_INVALIDATE=> TRUE,
FORCE=> TRUE,
STATOWN=> CURRENT SCHEMA,
STATTAB=> 'STATSTAB',
STATID=> 'TABLE2_STAT');
END;

DB20000I  The SQL command completed successfully.

SELECT STATID,C4,N2,N3,N4 from STATSTAB WHERE STATID='TABLE2_STAT' AND C4='COL0'

STATID                         C4                             N2                       N3                       N4
------------------------------ ------------------------------ ------------------------ ------------------------ ------------------------
TABLE2_STAT                    COL0                                                  3                        5                        5

1 record(s) selected.