SET_TABLE_STATS

The SET_TABLE_STATS procedure is used to set the basic statistics at the table level.

Syntax

DBMS_STATS.SET_TABLE_STATS (
  ownname         VARCHAR2,
  tabname         VARCHAR2,
  partname        VARCHAR2 DEFAULT NULL,
  numrows         NUMBER   DEFAULT NULL,
  numblks         NUMBER   DEFAULT NULL,
  avgrlen         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.
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.
numrows	The total number of rows at the table level.
numblks	The number of blocks occupied at the table level.
avgrlen	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 and ignore the lock status. The default is FALSE. If set to TRUE, statistics are 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 input value is invalid or inconsistent. The statistics of the object are locked.

Usage notes

• You must be the owner of the table to call this procedure.

• The optimizer estimates the number of cached blocks for an index or a statistics table by using cached data. The total cost of an operation includes the I/O cost for reading uncached blocks from disk, the CPU cost for retrieving cached blocks from the buffer, and the CPU cost for processing the data.

Examples

Set the total number of rows of the tbl1 table of the testUser01 user to 10,000.

CALL DBMS_STATS.SET_TABLE_STATS('testUser01', 'tbl1', numrows=>10000, force=>FALSE, no_invalidate=>FALSE);
Query OK, 0 rows affected