SET_TABLE_STATS

The SET_TABLE_STATS procedure is used to set 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

Exceptions

Considerations

• To call this procedure, you must be the owner of the table. For tables owned by SYS, you must be the owner or have the SYSDBA privilege.

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

• For parameters with values TRUE and FALSE, you need to execute the procedure within a BEGIN ... END block.

Examples

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

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