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 will be 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 | Whether to invalidate the Plan Cache when collecting statistics. Set to TRUE to not invalidate the Plan Cache when collecting statistics. |
| force | Whether to forcibly collect statistics and ignore the lock status. Default is FALSE. If set to TRUE, statistics will be collected even if they are locked. |
Exceptions
| Error Code | Description |
|---|---|
| HY000 |
|
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 theSYSDBAprivilege.The optimizer estimates the number of cached blocks for index or table access by caching data. The total cost of the 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 for the tbl1 table owned by the testUser01 user to 10,000.
obclient> CALL DBMS_STATS.SET_TABLE_STATS('testUser01', 'tbl1', numrows=>10000, force=>FALSE, no_invalidate=>FALSE);
Query OK, 0 rows affected