GET_COLUMN_STATS procedure - Retrieves column statistics

The GET_COLUMN_STATS procedure retrieves 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.
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.
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.
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.

Authorization

EXECUTE privilege on the DBMS_STATS module.

Example 1

Before you run the GET_COLUMN_STATS procedure, run the GATHER_TABLE_STATS procedure with the CASCADE parameter set to TRUE.

BEGIN
DECLARE v_distcnt INTEGER;
DECLARE v_nullcnt INTEGER;
DECLARE v_avgclen INTEGER;
DECLARE v_density INTEGER;
CALL DBMS_STATS.GET_COLUMN_STATS(
CURRENT SCHEMA,
'TEST_TABLE_STATS',
'COL0',
DISTCNT => v_distcnt,
NULLCNT => v_nullcnt,
AVGCLEN=> v_avgclen,
DENSITY=> v_density);
CALL DBMS_OUTPUT.PUT_LINE('NUMROWS: ' || v_distcnt);
CALL DBMS_OUTPUT.PUT_LINE('NUMBLKS: ' || v_nullcnt);
CALL DBMS_OUTPUT.PUT_LINE('AVGCLEN: ' || v_avgclen);
CALL DBMS_OUTPUT.PUT_LINE('DENSITY: ' || v_density);
END;

DB20000I  The SQL command completed successfully.

NUMROWS: 6
NUMBLKS: 0
AVGCLEN: 5
DENSITY: 0

Example 2

The following procedure gets the statistics of table partition PART 0 of COL 0.

BEGIN
DECLARE v_distcnt INTEGER;
DECLARE v_nullcnt INTEGER;
DECLARE v_avgclen INTEGER;
DECLARE v_density INTEGER;
CALL DBMS_STATS.GET_COLUMN_STATS(
CURRENT SCHEMA,
'TEST_TABLE_STATS',
'COL0',
'PART0',
STATTAB => 'STATSTAB',
STATOWN => CURRENT SCHEMA,
STATID => 'TABLE2_STAT',
DISTCNT => v_distcnt,
NULLCNT => v_nullcnt,
AVGCLEN=> v_avgclen,
DENSITY=> v_density);
CALL DBMS_OUTPUT.PUT_LINE('NUMROWS: ' || v_distcnt);
CALL DBMS_OUTPUT.PUT_LINE('NUMBLKS: ' || v_nullcnt);
CALL DBMS_OUTPUT.PUT_LINE('AVGCLEN: ' || v_avgclen);
CALL DBMS_OUTPUT.PUT_LINE('DENSITY: ' || v_density);
END;

DB20000I  The SQL command completed successfully.

NUMROWS: 6
NUMBLKS: 5
AVGCLEN: 0
DENSITY: 0