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 login 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, ignoring 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. |
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
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.The optimizer estimates the number of cached blocks to be accessed through index or statistics table by caching data. The total cost of an operation includes the I/O costs for reading uncached blocks from the disk and the CPU costs for fetching cached blocks from the buffer and processing data.
When the values of the parameters are
TRUEandFALSE, you must enclose the parameters in aBEGIN ... ENDblock.
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,'tbl1');
Query OK, 0 rows affected
Note
In OceanBase Database Oracle mode, the tabname parameter is not required because indexes are unique within the current user. However, in MySQL mode, you must explicitly specify the table name because indexes can be non-unique within the current user.