Query the index status in OceanBase Database V1.4.x to V3.2.x

Index status

SQL layer semantics of index creation

OceanBase Database is a distributed database. Its index creation process differs from that of a relational database in the following aspects:

• When you create an index in OceanBase V2.2 or earlier, the DDL statement is asynchronously executed. That is, the DDL statement for creating the index is directly returned to the client, and the server side asynchronously executes the index creation task. In OceanBase V1.x, an index creation task is executed during a major compaction. In OceanBase V2.x or later, an index creation task is executed immediately.

• When you create an index in OceanBase Database V2.2 or later, the DDL statement is executed immediately. However, the query timeout mechanism on the client side is abandoned. When the OBServer node and ODP process the index change DDL statement, the OBServer node and ODP implicitly change the ob_query_timeout value to 0, which indicates no execution timeout. The query timeout parameter specified on the client side will be ignored. If the socket timeout parameter is specified on the client side, when the index creation takes longer than the socket timeout period, the client will close the connection. However, the database server continues the index creation process.

  Therefore, basically, the index creation process in OceanBase Database remains asynchronously. You can query an internal table to determine the status of an index creation task. This topic describes how to query the index status in OceanBase Database in the MySQL compatible mode and Oracle compatible mode.

Index creation process

The index creation process consists of the following four phases:

1. Preparation: The system generates index table metadata and sets the index table to a write-only state. The system then waits for the data insertion transactions of the index table to end and obtains the construction snapshot savepoints.

2. Construction: The system scans data in the primary table based on the obtained snapshot savepoints and write them to the baseline SSTable of the index table. The system also writes data generated by incremental transactions to the MemTable. Finally, baseline data is completed, that is, the system synchronizes DML operations performed after the snapshot point to the index.

3. Copy: After an index is created on a single replica, the system synchronizes the index to index table replicas by using the consistency algorithm.

4. Final: The system performs data verification, and additionally uniqueness verification for the unique index. After the verification is completed, the system sets the index table the read/write state. If the verification fails, the system sets the index table to the unavailable state.

Query index status

The internal table __all_table_v2 is used to maintain the metadata of each index. Enumerated values of index_status indicate the index status. In different OceanBase Database versions, index_status values and meanings vary. The following table describes the field in different OceanBase Database versions.

MySQL compatible mode

Query the index status under the business tenant. Here is the sample command:

select t.table_name as table_name, t.table_id as table_id,
  i.table_name as index_name, i.table_id as index_id,
  case i.index_status
    when 1 then 'UNAVAILABLE'
    when 2 then 'AVAILABLE'
    when 3 then 'UNIQUE_CHECK'
    when 5 then 'ERROR'
    else 'UNKNOWN'
  end as index_status
from
  oceanbase.__all_table_v2 t
  join oceanbase.__all_table_v2 i on t.table_id = i.data_table_id
  JOIN oceanbase.__all_database d on t.database_id = d.database_id
where
  d.database_name = 'test'
  and t.table_name = 't_test';

Here is the sample result:

Oracle compatible mode

Query the index status under the business tenant. Here is the sample command:

SELECT T.TABLE_NAME AS TABLE_NAME,
  I.TABLE_NAME AS INDEX_NAME,
  CASE I.INDEX_STATUS
    WHEN 1 THEN 'UNAVAILABLE'
    WHEN 2 THEN 'AVAILABLE'
    WHEN 3 THEN 'UNIQUE_CHECK'
    WHEN 5 THEN 'ERROR'
    ELSE 'UNKNOWN'
  END AS INDEX_STATUS
FROM
  SYS.ALL_VIRTUAL_TABLE_REAL_AGENT T
  JOIN SYS.ALL_VIRTUAL_TABLE_REAL_AGENT I ON T.TABLE_ID = I.DATA_TABLE_ID
  JOIN SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT D ON T.DATABASE_ID = D.DATABASE_ID
WHERE
  D.DATABASE_NAME = 'your_user_name'
  AND T.TABLE_NAME = 'your_table_name';

Query index tasks

You can query the virtual table __all_virtual_sys_task_status to check whether an index creation task exists. If the __all_virtual_sys_task_status table contains the corresponding task information, the index creation task is in the data completion phase, which is the most time-consuming phase in the index creation process. In multi-partition scenarios, each partition corresponds to a record in the __all_virtual_sys_task_status table.

In the business tenant, query the __all_virtual_sys_task_status table for the index creation task. Here is the sample command:

select start_time, task_type, svr_ip, comment
from __all_virtual_sys_task_status
where tenant_id=1014;

Query the __all_virtual_sys_task_status virtual table for the index task status:

• If the task can be found in the table, the index creation task is being executed normally. You can wait until the index creation is completed.

• If no tasks are found, the index creation task fails. Check the OBServer log for cause analysis.

  • The index is being created.
  • The index is created but does not take effect. The index takes effect after a major compaction.

You can view the task-related information in the comment field. An example of the comment field:

build index task: pkey={tid:1101710651081589, partition_id:0, part_cnt:0}
index_id=1101710651081594 snapshot_version=1686295718878029 parallelism=1