做 OceanBase 运维或开发的同学，一定遇到过这样的场景： 执行了索引创建语句后，看着控制台一动不动，不知道索引到底建到哪了？是还在扫描数据，还是已经进入排序阶段？会不会中途失败了？

尤其是面对大数据量表，索引创建可能要耗时几小时甚至更久，“盲等”不仅浪费时间，还容易错过问题排查时机。

今天就给大家整理一份「OceanBase 索引创建进度查询指南」，新手也能轻松上手！

一、可用的监控视图概览

OceanBase 提供了多个视图来查询 DDL（含索引创建）的执行进度：

二、快速判断索引是否创建完成

方法 1：查看索引状态（最简单）

SELECT table_id, table_name, index_status


FROM oceanbase.__all_virtual_table


WHERE table_name 
like 
'__idx%索引名%';

index_status 含义：

示例：

__idx_3749302_idx_longtext 索引在创建中，index_status = 1;

__idx_3749302_idx_longtext 索引已构建完成， index_status = 2;

方法 2：查看 DDL 任务状态

SELECT task_id, 
status, ret_code, ddl_stmt_str


FROM oceanbase.__all_virtual_ddl_task_status


WHERE tenant_id = 

  
AND 
status 
NOT 
IN (
99, 
100);  
-- 排除已完成/已失败

status 关键值（源码 ObDDLTaskStatus 枚举，定义于 src/share/ob_ddl_common.h）：

查询示例:

方法 3：查看 LONGOPS 是否还有记录

SELECT 
COUNT(*) 
FROM oceanbase.GV$SESSION_LONGOPS;

无记录则所有 DDL 长操作已完成。

三、查看详细执行进度

3.1 GV$SESSION_LONGOPS（推荐）

SELECT 
SID, OPNAME, TARGET, ELAPSED_SECONDS, TIME_REMAINING, MESSAGE


FROM oceanbase.GV$SESSION_LONGOPS;

输出示例：

-- ROW_SCANNED 阶段输出示例
ocp_monitor@[oceanbase]>SELECT SID, OPNAME, TARGET, ELAPSED_SECONDS, TIME_REMAINING, MESSAGE FROM oceanbase.GV$SESSION_LONGOPS;
+-----+--------------+---------+-----------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SID | OPNAME       | TARGET  | ELAPSED_SECONDS | TIME_REMAINING | MESSAGE                                                                                                                                                                                                                                                                  |
+-----+--------------+---------+-----------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  -1 | create index | 3749303 |              21 |              0 | TENANT_ID: 1002, TASK_ID: 74233447, STATUS: REPLICA BUILD, SCANNING, PARALLELISM: 1, ROW_COUNT_INFO:{ ROW_SCANNED: 4315904, ROW_SORTED: 0, ROW_INSERTED: 0 }, SCAN_INFO:{ SCAN_TIME_ELAPSED: 21.000s, MAX_THREAD_ROW_SCANNED: 4315904, MIN_THREAD_ROW_SCANNED: 4315904 } |
+-----+--------------+---------+-----------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.376 sec)
-- ROW_SORTED 阶段输出示例
ocp_monitor@[oceanbase]>SELECT SID, OPNAME, TARGET, ELAPSED_SECONDS, TIME_REMAINING, MESSAGE FROM oceanbase.GV$SESSION_LONGOPS;
+-----+--------------+---------+-----------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SID | OPNAME       | TARGET  | ELAPSED_SECONDS | TIME_REMAINING | MESSAGE                                                                                                                                                                                                                                                                                                                                                                                                    |
+-----+--------------+---------+-----------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  -1 | create index | 3749303 |              51 |              0 | TENANT_ID: 1002, TASK_ID: 74233447, STATUS: REPLICA BUILD, SORT_PHASE1, PARALLELISM: 1, SORT_PHASE1_THREAD_NUM: 1, ROW_COUNT_INFO:{ ROW_SCANNED: 6402493, ROW_SORTED: 709701, ROW_INSERTED: 0 }, SORT_PHASE1_PROGRESS_INFO:{ SORT_PHASE1_TIME_ELAPSED: 19.139s, SORT_PHASE1_PROGRESS: 11.08%, SORT_PHASE1_TIME_REMAINING: 153.521s }, SLOWEST_THREAD_INFO:{ THREAD_ID: 2572679, SORTED_ROW_COUNT: 709701 } |
+-----+--------------+---------+-----------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.318 sec)
-- ROW_INSERTED 阶段输出示例：
ocp_monitor@[oceanbase]>SELECT SID, OPNAME, TARGET, ELAPSED_SECONDS, TIME_REMAINING, MESSAGE FROM oceanbase.GV$SESSION_LONGOPS;
+-----+--------------+---------+-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SID | OPNAME       | TARGET  | ELAPSED_SECONDS | TIME_REMAINING | MESSAGE                                                                                                                                                                                                                                                                                                                                                                           |
+-----+--------------+---------+-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  -1 | create index | 3749303 |             206 |              0 | TENANT_ID: 1002, TASK_ID: 74233447, STATUS: REPLICA BUILD, INSERT, PARALLELISM: 1, INSERT_THREAD: 1, ROW_COUNT_INFO:{ ROW_SCANNED: 6402493, ROW_SORTED: 6402493, ROW_INSERTED: 1842400 }, INSERT_PROGRESS_INFO:{ INSERT_TIME_ELAPSED: 23.460s, INSERT_PROGRESS: 28.78%, INSERT_TIME_REMAINING: 58.064s }, SLOWEST_THREAD_INFO:{ THREAD_ID: 2572679, INSERTED_ROW_COUNT: 1842400 } |
+-----+--------------+---------+-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.324 sec)

关键字段解读：

四、索引创建的完整执行流程

索引创建在 REDEFINITION（status=3）阶段完成数据回填，内部分为以下子阶段：

SCAN（扫描源表）
  │
  ▼
SORT_PHASE1（内存排序，生成有序块写入临时文件）
  │
  ▼
SORT_PHASE2（外部归并排序，可能多轮）
  │
  ▼
INSERT（将排序结果写入索引表）
  │
  ▼
VALIDATE_CHECKSUM → TAKE_EFFECT（校验 + 生效）

源码位置： src/rootserver/ddl_task/ob_index_build_task.cpp 的 ObIndexBuildTask::collect_longops_stat()。

五、ROW_SORTED 的正确理解

5.1 ROW_SORTED 可能大于 ROW_SCANNED

这是正常现象。源码（ src/share/ob_ddl_common.cpp）中：

// 显示的 ROW_SORTED = row_sorted_ + row_merge_sorted_

row_scanned_, row_sorted_ + row_merge_sorted_, row_inserted_file_

• row_sorted_：Phase1 内存排序处理的行数（≈ N）
• row_merge_sorted_：Phase2 归并排序 额外处理的行数

归并排序每轮都会处理全部 N 行，如果有 R 轮，则 row_merge_sorted_ ≈ N × R。

六、常用 SQL 速查

查看所有正在执行的 DDL 任务

SELECT task_id, tenant_id, ddl_type, 
status, trace_id,

       gmt_create, gmt_modified


FROM oceanbase.__all_virtual_ddl_task_status


WHERE 
status 
NOT 
IN (
99, 
100);

查看索引创建实时进度

SELECT 
now(), 
SID, TARGET 
AS index_table_id,

       ELAPSED_SECONDS, TIME_REMAINING, MESSAGE


FROM oceanbase.GV$SESSION_LONGOPS


WHERE OPNAME = 
'create index';

查看索引是否已可用

SELECT table_id, table_name, index_status


FROM oceanbase.__all_virtual_table


WHERE table_name 
like 
'__idx%索引名%';

查看排序线程级详情

SELECT PROCESS_NAME, PLAN_LINE_ID, PLAN_OPERATION,

       OUTPUT_ROWS,

       OTHERSTAT_1_VALUE 
AS row_sorted,

       OTHERSTAT_2_VALUE 
AS merge_sort_round,

       OTHERSTAT_6_VALUE 
AS dump_size_bytes,

       OTHERSTAT_7_VALUE 
AS 
row_count,

       OTHERSTAT_8_VALUE 
AS expected_round_count,

       FIRST_CHANGE_TIME, LAST_CHANGE_TIME


FROM GV$SQL_PLAN_MONITOR


WHERE TRACE_ID = 
''

  
AND PLAN_OPERATION = 
'PHY_SORT'


ORDER 
BY PROCESS_NAME;

> trace_id 可从 __all_virtual_ddl_task_status 或 GV$SESSION_LONGOPS 获取。

查看 DDL 失败原因

SELECT * 
FROM oceanbase.__all_virtual_ddl_error_message


WHERE task_id = ;

常见失败原因：磁盘空间不足

七、关键源码文件索引

八、注意事项与最佳实践

1. 并行度选择：可以在索引创建时间和集群负载之间选择合适的并行度。
2. ROW_SORTED 不能直接算百分比：需要结合 expected_rounds 才能计算准确进度。直接用 ROW_SORTED / ROW_SCANNED 在 SORT_PHASE2 阶段会超过 100%。
3. 排序行长 ≠ 表行长：估算排序内存用量时，应使用索引列 + 主键列的 未压缩大小，而非表级别的压缩后平均行长。
   
   

如果你在实际操作中遇到问题，可以在评论区留言交流。后续我们会持续分享更多运维干货，记得  关注不迷路，下次见~