OceanBase Database calls the stored procedures related to the DBMS_STATS package to delete the statistics stored in the internal table.
You can delete statistics by using the following methods:
Use the stored procedure
delete_table_statsto delete table-level statistics.Use the stored procedure
delete_column_statsto delete column-level statistics.Use the stored procedure
delete_schema_statsto delete statistics of all tables in a schema.
The stored procedures are defined as follows:
PROCEDURE delete_table_stats (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
cascade_parts BOOLEAN DEFAULT TRUE,
cascade_columns BOOLEAN DEFAULT TRUE,
cascade_indexes BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE
);
PROCEDURE delete_column_stats (
ownname VARCHAR2,
tabname VARCHAR2,
colname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
cascade_parts BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE,
col_stat_type VARCHAR2 DEFAULT 'ALL'
);
PROCEDURE delete_schema_stats (
ownname VARCHAR2,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE
);
The following table describes the parameters.
Parameter |
Description |
|---|---|
| ownname | The username. If the username is set to NULL, the current logon username is used by default. |
| tabname | The name of the table. |
| partname | The name of the partition. |
| colname | The name of the column. |
| stattab | This parameter is not available. |
| statid | This parameter is not available. |
| cascade_parts | Specifies whether to delete statistics at the partition level and the sub-partition level. Default value: TRUE. |
| cascade_columns | Specifies whether to delete statistics at the column level. Default value: TRUE. |
| cascade_indexes | This parameter is not available. |
| statown | This parameter is not available. |
| no_invalidate | This parameter is not available. |
| force | Specifies whether to perform forced deletion and ignore the lock status. Default value: FALSE. |
| col_stat_type | The type of the column-level statistics to be deleted.
|
The following examples show how to delete statistics:
Delete all statistics of table
tbl1for useruser1.CALL dbms_stats.delete_table_stats('user1', 'tbl1');Delete all statistics of the
col1column in tabletbl1for useruser1.CALL dbms_stats.delete_column_stats('user1', 'tbl1', 'col1');Delete all statistics of all tables of user
user1.CALL dbms_stats.delete_schema_stats('user1');