The SET_COLUMN_STATS procedure is used to set basic column-level 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 |
Explanation |
|---|---|
| ownname | Username. If the username is set to NULL, the current login username will be used by default. |
| tabname | The name of the table to which this column belongs. |
| colname | Column name or extension. |
| partname | The name of the table partition that stores statistics. If the table is partitioned and partname is NULL, the statistics are stored at the global table level. |
| distcnt | The NDV value at the column level. |
| density | Column-level sparsity. If this value is NULL and distcnt No NULL, then the density value is distcnt. |
| nullcnt | Column-level NULL Number of values. |
| avgclen | The average length of columns in bytes. |
| no_invalidate | Whether to flush the plan cache when statistics are collected. Valid values: TRUE Indicates that the plan cache is not flushed during statistics collection. |
| force | Whether to forcibly collect, ignoring the lock status. Default is FALSE. If set to TRUE, statistics collection will continue even if the statistics are locked. |
Exceptions
Error Code |
Description |
|---|---|
| HY000 |
|
Usage instructions
When processing user-defined statistics, OceanBase AI Database requires that the statistical type specified by SET_COLUMN_STATS be one stored in a dictionary. If this statistical type is NULL, the statistical type associated with the index or column is used instead.
To call this procedure, you must be the owner of the table. For objects owned by SYS, you must be the table owner or have the SYSDBA privilege to call this procedure.
Examples
Set the NDV of column col1 in table tbl1 for 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