The SET_COLUMN_STATS procedure is used to set basic column-level statistics.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this.
Syntax
DBMS_STATS.SET_COLUMN_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
colname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
distcnt NUMBER DEFAULT NULL,
density NUMBER DEFAULT NULL,
nullcnt NUMBER DEFAULT NULL,
avgclen NUMBER DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE);
Parameters
| Parameter | Description |
|---|---|
| ownname | The username. If the username is set to NULL, the current login username is used by default. |
| tabname | The name of the table to which this column belongs. |
| colname | The column name or extension. |
| partname | The name of the partition where the statistics are stored. If the table is partitioned and partname is NULL, the statistics are stored at the global table level. |
| distcnt | The NDV value for the column. |
| density | The density for the column. If this value is NULL and distcnt is not NULL, the density value is distcnt. |
| nullcnt | The number of NULL values for the column. |
| avgclen | The average length (in bytes) for the column. |
| no_invalidate | Specifies whether to invalidate the Plan Cache when collecting statistics. If set to TRUE, the Plan Cache is not invalidated when collecting statistics. |
| force | Specifies whether to force the collection of statistics, ignoring lock status. The default is FALSE. If set to TRUE, statistics are collected even if they are locked. |
Exceptions
| Error code | Description |
|---|---|
| OBE-20000 | The object does not exist or the user does not have sufficient privileges. |
| OBE-20001 | Invalid or inconsistent input values. |
| OBE-20005 | The statistics for the object are locked. |
Considerations
In the current version of OceanBase Database, when handling user-defined statistics, the statistics type specified by SET_COLUMN_STATS must be one that is stored in a dictionary. If this statistics type is NULL, the statistics type associated with the index or column is stored.
To call this procedure, you must be the owner of the table. For objects owned by SYS, you must be the owner or have the SYSDBA privilege to call this procedure.
For parameters with values of TRUE and FALSE, you must execute the procedure within a BEGIN ... END block.
Examples
Set the NDV of the col1 column in the tbl1 table for the testUser01 user to 10 and the number of NULL values to 10.
obclient> CALL DBMS_STATS.SET_COLUMN_STATS('testUser01', 'tbl1', 'col1', distcnt=>10, nullcnt=>10);
Query OK, 0 rows affected