The SET_INDEX_STATS procedure is used to set index-related information.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
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 | Username. If you set the username to NULL, the current username will be used by default. |
| indname | Index name. |
| partname | Partition name. |
| numrows | Number of rows (partitions) in the index. |
| no_invalidate | Specifies whether to invalidate the plan cache during statistics collection. Set it to TRUE to invalidate the plan cache during statistics collection. |
| 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 | Average row length (in bytes) of the index table. |
| nummacroblks | Number of macroblocks in the index table. |
| nummicroblks | Number of microblocks in the index table. |
| tabname | Table name. |
Exceptions
| 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. |
Remarks
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 cached blocks to be accessed during an index or statistics table access by using the cached data in the plan cache. 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 from the buffer and processing data.
For the parameters whose values are
TRUEandFALSE, you must call this procedure within aBEGIN ... ENDblock.
Example
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, 'tbl1');
Query OK, 0 rows affected
Note
In the Oracle mode of OceanBase Database, you do not need to specify the tabname parameter, because indexes are unique for each user in the current user. However, in the MySQL mode, you must specify the table name explicitly because indexes are non-unique for each user in the current user.