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 |
Explanation |
|---|---|
| ownname | The schema to which the table belongs. |
| indname | Index name. |
| partname | Partition name. |
| no_invalidate | Whether to flush the plan cache when statistics are collected. Valid values: TRUE Indicates that the plan cache is not flushed during statistics collection. |
| numrows | Number of rows (partitions) in the index. |
| force | Whether to forcibly set index statistics and ignore the lock status. Default: FALSE. If set to TRUE, index statistics are still collected even if the statistics themselves are locked. |
| avgrlen | Average row length of the index table, in bytes. |
| nummacroblks | Number of macroblocks in the index table. |
| nummicroblks | The number of microblocks in the index table. |
| tabname | Table name. |
Exceptions
Error Code |
Description |
|---|---|
| HY000 |
|
Usage instructions
To call this procedure, you must be the owner of the table. For objects owned by
SYS, you must be the table owner or have theSYSDBAprivilege to call this procedure.The optimizer estimates the number of cache blocks accessed by an index or a table based on cached data. The total cost of an operation includes the I/O cost for reading uncached blocks from disk, the CPU cost for retrieving cached blocks from the buffer pool, and the CPU cost for processing the data.
Examples
Set the index idx1 information 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
You must explicitly specify the table name in OceanBase AI Database because indexes are not unique under the current user.