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 you set it 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 to call this procedure.The optimizer caches data to estimate the number of cache blocks to be accessed during 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 from the buffer and processing data.
For parameters of the
TRUEorFALSEtype, you must enclose theBEGIN ... ENDblock when you call this procedure.
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 OceanBase Database Oracle mode, the tabname parameter is not required because indexes are unique within the current user. In MySQL mode, you must explicitly specify the table name because indexes can be non-unique within the current user.