DELETE_COLUMN_STATS procedure - Deletes column statistics

The DELETE_COLUMN_STATS procedure deletes 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.
cascade_parts: An input argument of type BOOLEAN that specifies the deletion of statistics for this table for all underlying partitions.
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.
col_stat_type: An input argument of type VARCHAR(128) that specifies the type of column statistics that are to be deleted.

Authorization

EXECUTE privilege on the DBMS_STATS module.

Example 1

DELETE_COLUMN_STATS WITH STATABLE value NULL

BEGIN
     CALL DBMS_STATS.DELETE_COLUMN_STATS(CURRENT SCHEMA,
     'TEST_TABLE_STATS',
     'COL0',
     CASCADE_PARTS=>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     -1       -1       -1

          1 record(s) selected.

Example 2

DELETE_COLUMN_STATS WITH STATABLE value NOT NULL

        BEGIN
                                  CALL DBMS_STATS.DELETE_COLUMN_STATS(CURRENT SCHEMA,
                                  'TEST_TABLE_STATS2',
                                  'COL0',
                                  'PART0',
                                  STATOWN=> CURRENT SCHEMA,
                                  STATTAB=> 'STATSTABLE1',
                                  STATID=> 'TABLE4_STAT',
                                  CASCADE_PARTS=> TRUE,
                                  NO_INVALIDATE=>FALSE,
                                  FORCE=>FALSE);
                                END;

        DB20000I  The SQL command completed successfully.


select STATID,C1,C3,C5 from STATSTAB WHERE STATID='STATID4'

        STATID  C1   C3  C5  
        ------- ---- --- ----

          0 record(s) selected.