The IMPORT_INDEX_STATS procedure retrieves the statistics of a specified index from user information statistics tables and stores them in the dictionary.
Syntax
DBMS_STATS.IMPORT_INDEX_STATS (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE,
tabname VARCHAR2 DEFAULT NULL);
Parameters
| Parameter | Description |
|---|---|
| ownname | Username. If ownname is set to NULL, the username of the current login user is used by default. |
| tabname | Table name. |
| indname | Index name |
| partname | Partition name. If the table is partitioned and partname is set to NULL, statistics of all partitions and columns are imported. |
| stattab | The name of the user statistics table from which to import statistics. |
| statown | The username of the user statistics table from which to import statistics. |
| no_invalidate | Specifies whether to invalidate the plan cache during statistics collection. Set it to TRUE to specify not to invalidate the plan cache during statistics collection. |
| force | Specifies whether to import statistics regardless of the lock status. The default value is FALSE. If set to TRUE, statistics will be imported even if they are locked. |
Errors
| Error code | Description |
|---|---|
| ORA-20000 | The object does not exist or you do not have sufficient privileges. |
| ORA-20001 | The values in the user information statistics table are invalid or inconsistent. |
| ORA-20005 | The statistics of the object are locked. |
Considerations
Statistics cannot be exported or imported between columns of different character sets.
Examples
Import the statistics of the idx1 index from the tbl1 table of the testUser01 user into the dictionary.
obclient> CALL DBMS_STATS.CREATE_STAT_TABLE('testUser01', 'test_stat');
Query OK, 0 rows affected
obclient> CALL DBMS_STATS.IMPORT_INDEX_STATS('testUser01', 'idx1',stattab=>'test_stat',tabname=>'tbl1',no_invalidate=>FALSE);
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 in the current user. In the MySQL mode, you need to specify the table name because indexes can be non-unique in the current user.