SET_COLUMN_STATS

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 login username will be used by default.
tabname	The name of the table to which this column belongs.
colname	The column name or alias.
partname	The name of the partition in which 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 of the column. If this value is NULL and distcnt is not NULL, the density value is distcnt.
nullcnt	The number of NULL values in the column.
avgclen	The average length of the column in bytes.
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 will be collected even if they are locked.

Exceptions

Error Code	Description
HY000	The object does not exist or the user does not have sufficient privileges. The value is invalid or inconsistent. The statistics for the object are locked.

Usage Notes

When handling user-defined statistics, seekdb only allows the use of statistics types stored in the dictionary. If the specified statistics type is NULL, it will store the statistics type associated with the index or column.

To call this procedure, you must be the owner of the table.

Examples

Set the NDV of the col1 column in the tbl1 table for the user testUser01 to 10 and the number of NULL values to 10.

CALL DBMS_STATS.SET_COLUMN_STATS('testUser01', 'tbl1', 'col1', distcnt=>10, nullcnt=>10);
Query OK, 0 rows affected