The SET_TABLE_STATS procedure is used to set basic table-level statistics.
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 |
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. |
| 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. |
| numrows | Total number of rows in the table. |
| numblks | The number of blocks occupied at the table level. |
| avgrlen | The average length of columns in bytes. |
| no_invalidate | Specifies 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 the data, ignoring the lock status. Default value: FALSE. If set to TRUE, statistics collection will continue even if the statistics are locked. |
Exceptions
Error Code |
Description |
|---|---|
| HY000 |
|
Usage instructions
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 theSYSDBAprivilege to call this procedure.The optimizer estimates the number of cache blocks accessed for an index or a table based on cached data. The total cost of an operation includes the I/O cost of reading uncached blocks from disk, the CPU cost of fetching cached blocks from the buffer pool, and the CPU cost of processing the data.
Examples
Set the total number of rows in table tbl1 for user testUser01 to 10,000.
obclient> CALL DBMS_STATS.SET_TABLE_STATS('testUser01', 'tbl1', numrows=>10000, force=>FALSE, no_invalidate=>FALSE);
Query OK, 0 rows affected