DELETE_TABLE_STATS procedure - Deletes table statistics

The DELETE_TABLE_STATS procedure deletes table 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.
cascade_columns: An input argument of type BOOLEAN that specifies the deletion of statistics for this table for all underlying columns.
cascade_indexes: An input argument of type BOOLEAN that specifies the deletion of statistics for this table for all underlying indexes.
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.
stattype: An input argument of type VARCHAR(128) that specifies the type of statistical data that is stored in stattab.
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.DELETE_TABLE_STATS(
CURRENT SCHEMA,
'TEST_TABLE_STATS2',
NO_INVALIDATE=>FALSE,
FORCE=>FALSE
);
END

DB20000I  The SQL command completed successfully.


SELECT TABNAME, CARD, NPAGES, AVGROWSIZE FROM SYSSTAT.TABLES WHERE TABNAME = 'TEST_TABLE_STATS2' AND TABSCHEMA = CURRENT SCHEMA

TABNAME                                  CARD                 NPAGES               AVGROWSIZE                                                        
---------------------------------------- -------------------- -------------------- ----------
TEST_TABLE_STATS2                                          -1                   -1         17                                                                       

1 record(s) selected.

Example 2

When you specify the CASCADE parameter, the COLUMNS statistics and the INDEX statistics are also deleted.

BEGIN
CALL DBMS_STATS.DELETE_TABLE_STATS(CURRENT SCHEMA,
'TEST_TABLE_STATS',
CASCADE_PARTS=> TRUE,
CASCADE_COLUMNS=> TRUE,
NO_INVALIDATE=>FALSE,
FORCE=>FALSE);
END;

DB20000I  The SQL command completed successfully.

SELECT TABNAME, CARD, NPAGES, AVGROWSIZE FROM SYSSTAT.TABLES WHERE TABNAME = 'TEST_TABLE_STATS' AND TABSCHEMA = CURRENT SCHEMA

TABNAME                                          CARD                 NPAGES               AVGROWSIZE                                                          
------------------------------------------------ -------------------- -------------------- ----------
TEST_TABLE_STATS                                                   -1                   -1         17                                                              

1 record(s) selected.

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 3

When you specify the STATTAB parameter, the statistics that are stored in the user-defined statistics table are also deleted.

BEGIN
CALL DBMS_STATS.DELETE_TABLE_STATS(CURRENT SCHEMA,
'TEST_TABLE_STATS2',
'PART0',
STATOWN=> CURRENT SCHEMA,
STATTAB=> 'STATSTAB',
STATID=> 'TABLE4_STAT',
CASCADE_PARTS=> TRUE,
CASCADE_COLUMNS=> FALSE,
CASCADE_INDEXES=> FALSE,
NO_INVALIDATE=>FALSE,
FORCE=>FALSE);
END;

DB20000I  The SQL command completed successfully.

select STATID,C1,N2,N3,N4 from STATSTAB WHERE STATID='TABLE4_STAT'

STATID                         C1                             N2                       N3                       N4
------------------------------ ------------------------------ ------------------------ ------------------------ ------------------------
TABLE4_STAT                    TEST_TABLE_STATS2                                    -1                       -1                       -1

1 record(s) selected.