GET_INDEX_STATS procedure - Retrieves index statistics

The GET_INDEX_STATS procedure retrieves index 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

BEGIN
DECLARE v_numrows INTEGER;
DECLARE v_numlblks INTEGER;
DECLARE v_numdist INTEGER;
DECLARE v_avglblk INTEGER;
DECLARE v_avgdblk INTEGER;
DECLARE v_clstfct INTEGER;
DECLARE v_indlevel INTEGER;
DECLARE v_guessq INTEGER;
CALL DBMS_STATS.GET_INDEX_STATS(
CURRENT SCHEMA,
'STATS_INDEX',
NUMROWS=> v_numrows,
NUMLBLKS=> v_numlblks,
NUMDIST=> v_numdist,
AVGLBLK=> v_avglblk,
AVGDBLK=> v_avgdblk,
CLSTFCT=> v_clstfct,
INDLEVEL=> v_indlevel,
GUESSQ=> v_guessq);
CALL DBMS_OUTPUT.PUT_LINE('NUMROWS: ' || v_numrows);
CALL DBMS_OUTPUT.PUT_LINE('NUMLBLKS: ' || v_numlblks);
CALL DBMS_OUTPUT.PUT_LINE('NUMDIST: ' || v_numdist);
CALL DBMS_OUTPUT.PUT_LINE('AVGLBLK: ' || v_avglblk);
CALL DBMS_OUTPUT.PUT_LINE('AVGDBLK: ' || v_avgdblk);
CALL DBMS_OUTPUT.PUT_LINE('CLSTFCT: ' || v_clstfct);
CALL DBMS_OUTPUT.PUT_LINE('INDLEVEL: ' || v_indlevel);
CALL DBMS_OUTPUT.PUT_LINE('GUESSQ: ' || v_guessq);
END;

DB20000I  The SQL command completed successfully.

NUMROWS: 3
NUMLBLKS: 1
NUMDIST: 6
AVGLBLK: 0
AVGDBLK: 0
CLSTFCT: 66
INDLEVEL: 1
GUESSQ: 100

Example 2

BEGIN
DECLARE v_numrows INTEGER;
DECLARE v_numlblks INTEGER;
DECLARE v_numdist INTEGER;
DECLARE v_avglblk INTEGER;
DECLARE v_avgdblk INTEGER;
DECLARE v_clstfct INTEGER;
DECLARE v_indlevel INTEGER;
DECLARE v_guessq INTEGER;
CALL DBMS_STATS.GET_INDEX_STATS(
CURRENT SCHEMA,
'STATS_INDEX',
'PART0',
STATTAB => 'INDEXSTATSTAB',
STATOWN => CURRENT SCHEMA,
STATID => 'TABLE2_STAT',
NUMROWS=> v_numrows,
NUMLBLKS=> v_numlblks,
NUMDIST=> v_numdist,
AVGLBLK=> v_avglblk,
AVGDBLK=> v_avgdblk,
CLSTFCT=> v_clstfct,
INDLEVEL=> v_indlevel,
GUESSQ=> v_guessq);
CALL DBMS_OUTPUT.PUT_LINE('NUMROWS: ' || v_numrows);
CALL DBMS_OUTPUT.PUT_LINE('NUMLBLKS: ' || v_numlblks);
CALL DBMS_OUTPUT.PUT_LINE('NUMDIST: ' || v_numdist);
CALL DBMS_OUTPUT.PUT_LINE('AVGLBLK: ' || v_avglblk);
CALL DBMS_OUTPUT.PUT_LINE('AVGDBLK: ' || v_avgdblk);
CALL DBMS_OUTPUT.PUT_LINE('CLSTFCT: ' || v_clstfct);
CALL DBMS_OUTPUT.PUT_LINE('INDLEVEL: ' || v_indlevel);
END;

DB20000I  The SQL command completed successfully.

NUMROWS: 3
NUMLBLKS: 1
NUMDIST: 3
AVGLBLK: 0
AVGDBLK: 1
CLSTFCT: 66
INDLEVEL: 1
GUESSQ: 100