The SET_TABLE_STATS procedure is used to set basic statistics at the table level.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this.
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 ownname is set to NULL, the current login username is used by default. |
| tabname | The name of the table. |
| partname | The name of the partition where statistics are stored. If the table is partitioned and partname is NULL, 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 forcibly collect statistics and ignore the lock status. The default value is FALSE. If set to TRUE, statistics are collected even if they are locked. |
Exceptions
Error Code |
Description |
|---|---|
| OBE-20000 | The object does not exist or the user does not have sufficient privileges. |
| OBE-20001 | The input value is invalid or inconsistent. |
| OBE-20005 | The statistics of the object are locked. |
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 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
TRUEandFALSE, you need to execute the procedure within aBEGIN ... ENDblock.
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