The SET_INDEX_STATS procedure is used to set information related to indexes.
Syntax
DBMS_STATS.SET_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
numrows NUMBER DEFAULT NULL,
force BOOLEAN DEFAULT FALSE,
avgrlen NUMBER DEFAULT NULL,
nummacroblks NUMBER DEFAULT NULL,
nummicroblks NUMBER DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE,
tabname VARCHAR2 DEFAULT NULL);
Parameters
| Parameter | Description |
|---|---|
| ownname | The schema where the table is located. |
| indname | The index name. |
| partname | The partition name. |
| no_invalidate | Specifies whether to clear the plan cache when collecting statistics. If set to TRUE, the plan cache will not be cleared when collecting statistics. |
| numrows | The number of rows in the index (partition). |
| 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 |
|---|---|
| HY000 |
|
Considerations
To call this procedure, you must be the owner of the table. For objects owned by
SYS, you must be the owner of the table 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 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 information related to the index idx1 for the user testUser01.
obclient> CALL DBMS_STATS.SET_INDEX_STATS('testUser01', 'idx1', numrows=>10, avgrlen=>10,
tabname=>'tbl1');
Query OK, 0 rows affected
Note
In OceanBase Database's Oracle mode, 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.