The SET_COLUMN_STATS procedure is used to set column-level basic statistics.
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 logged-in username will be 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 table 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 level. |
| density | The density for the column level. If this value is NULL and distcnt is not NULL, the density value is distcnt. |
| nullcnt | The number of NULL values for the column level. |
| avgclen | The average length (in bytes) for the column level. |
| no_invalidate | Whether to invalidate the Plan Cache when collecting statistics. Setting it to TRUE means not to invalidate the Plan Cache when collecting statistics. |
| force | Whether to forcibly collect statistics and ignore the lock status. The default is FALSE. If set to TRUE, statistics will be collected even if they are locked. |
Exceptions
| Error code | Description |
|---|---|
| HY000 |
|
Considerations
In the current version of OceanBase Database, when handling user-defined statistics, the statistics type specified by SET_COLUMN_STATS can only be a type stored in the dictionary. If this statistics type is NULL, the statistics type associated with the index or column will be 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.
Examples
Set the NDV of the col1 column in the tbl1 table of user testUser01 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