The SET_INDEX_STATS procedure is used to set information related to indexes.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
Syntax
DBMS_STATS.SET_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
numrows NUMBER DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE,
avgrlen NUMBER DEFAULT NULL,
nummacroblks NUMBER DEFAULT NULL,
nummicroblks NUMBER DEFAULT NULL,
tabname VARCHAR2 DEFAULT NULL);
Parameters
| Parameter | Description |
|---|---|
| ownname | The username. If the username is set to NULL, the current login username will be used by default. |
| indname | The index name. |
| partname | The partition name. |
| numrows | The number of rows in the index (partition). |
| no_invalidate | Specifies whether to invalidate the Plan Cache when collecting statistics. If set to TRUE, the Plan Cache will not be invalidated when collecting statistics. |
| force | Specifies whether to forcibly set the index statistics and ignore the lock status. The default value is FALSE. If set to TRUE, the index statistics will be set even if the statistics are locked. |
| avgrlen | The average row length of the index table, in bytes. |
| nummacroblks | The number of macroblocks in the index table. |
| nummicroblks | The number of microblocks in the index table. |
| tabname | The table name. |
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. |
| OBE-20005 | The statistics of the object are locked. |
Considerations
You must be the owner of the table to call this procedure. For objects owned by
SYS, you must be the owner or have theSYSDBAprivilege to call this procedure.The optimizer estimates the number of cached blocks accessed by an index or statistics table by caching data. The total cost of an operation includes the I/O cost of reading uncached blocks from disk, the CPU cost of retrieving cached blocks from the buffer, and the CPU cost of processing the data.
For parameters with values
TRUEandFALSE, you must execute the procedure within aBEGIN ... ENDblock.
Examples
Set the information related to the index idx1 for the user testUser01.
obclient> CALL DBMS_STATS.SET_INDEX_STATS('testUser01', 'idx1', numrows=>10, avgrlen=>10,'tbl1');
Query OK, 0 rows affected
Note
In Oracle mode of OceanBase Database, you do not need to specify tabname because the index is unique under the current user. However, in MySQL mode, you must explicitly specify the table name because the index is not unique under the current user.