The SET_INDEX_STATS procedure is used to set index-related information.
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 resides. |
| indname | The index name. |
| partname | The partition name. |
| no_invalidate | Specifies whether to invalidate the plan cache during statistics collection. Set it to TRUE to invalidate the plan cache during statistics collection. |
| numrows | The number of rows (partitions) in the index. |
| force | Specifies whether to forcibly set the index statistics regardless of the lock status. The default value is FALSE. If set to TRUE, the statistics on the index are forcibly set even if they 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. |
Errors
| Error code | Description |
|---|---|
| ORA-20000 | The object does not exist or you do not have sufficient privileges. |
| ORA-20001 | Invalid input values. |
| ORA-20005 | The statistics on the object are locked. |
Considerations
To call this procedure, you must be the owner of the table. If the table is owned by the
SYSuser, you must be the owner of the table or have theSYSDBAprivilege to call this procedure.The optimizer estimates the number of cache blocks to be accessed during an index or statistics table access by caching data. The total cost of an operation includes the I/O costs of reading uncached blocks from the disk and the CPU costs of processing data in the buffer.
Example
Set the information about the idx1 index of the testUser01 user.
obclient> CALL DBMS_STATS.SET_INDEX_STATS('testUser01', 'idx1', numrows=>10, avgrlen=>10,
tabname=>'tbl1');
Query OK, 0 rows affected
Note
In Oracle mode of OceanBase Database, you do not need to specify the tabname parameter, because indexes of the current user are unique. In MySQL mode, you must specify the table name because indexes of the current user can be non-unique.