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 will be 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 value. |
| ORA-20005 | The statistics of the object are locked. |
Considerations
You must be the owner of the table to call this procedure. 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 caches data to estimate the number of cached blocks accessed by index or statistics table access. The total cost of an operation includes the I/O costs for reading uncached blocks from the disk and the CPU costs for accessing cached blocks in the buffer and processing data.
Examples
Set the index-related information of 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, the tabname parameter is not required because indexes are unique within the current user. In MySQL mode, you must specify the table name because indexes can be non-unique within the current user.