1、背景
在 OceanBase 早期版本的 Oracle 兼容模式中，对带有全局索引的 Interval 分区表执行 ALTER TABLE ... TRUNCATE/DROP PARTITION 操作时，存在以下问题：
- 全局索引失效：执行 DDL 后全局索引状态变为 UNUSABLE，导致依赖该索引的查询无法走索引扫描，业务性能严重劣化甚至报错。
- DDL 耗时极长：若选择同步维护全局索引，DDL 执行时间随数据量线性增长，长时间锁表阻塞业务。
而在OceanBase V4.4.2.1 版本针对该场景进行了优化，支持在 TRUNCATE/DROP PARTITION 操作中使用 UPDATE GLOBAL INDEXES 子句，实现异步维护全局索引，保证 DDL 秒级返回且索引全程可用。
应现场客户要求验证该新版本特性是否可靠，对于数据库性能是否有重大提升以及未来能否放心在业务高峰期间使用的问题准备了以下测试案例。
2、目标
- 验证 4.4.2.1 版本在 Interval 分区表上执行 TRUNCATE/DROP PARTITION ... UPDATE GLOBAL INDEXES 时，全局索引是否全程保持 VALID。
- 验证 DDL 执行期间，业务命中全局索引查询的 QPS 是否不受影响。
- 与 4.4.2.0 版本进行同场景 A/B 对比，量化新特性的性能收益。
3、测试方法
| 会话 |
操作 |
| 会话① |
执行 qps.sh 压测脚本，持续输出每秒瞬时 QPS |
| 会话② |
执行预先准备的存储过程，每秒记录索引状态 |
| 会话③ |
在压测脚本运行稳定后对目标分区执行 TRUNCATE/DROP PARTITION ... UPDATE GLOBAL INDEXES |
4、测试数据准备
创建interval分区表
CREATE TABLE "T_BIG" (
"ID" NUMBER(18) NOT NULL,
"P_DATE" DATE NOT NULL,
"K" NUMBER(18) NOT NULL,
"PAD" VARCHAR2(200),
CONSTRAINT "PK_T_BIG" PRIMARY KEY ("ID", "P_DATE")
)
PARTITION BY RANGE ("P_DATE")
INTERVAL (INTERVAL '1' MONTH)
(
PARTITION "P_202401" VALUES LESS THAN (TO_DATE('2024-02-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
);
创建全局索引
obclient(SYS@oracle)[SYS] > create index gi_k on t_big(k) global;
Query OK, 0 rows affected (0.116 sec)
通过 PL/SQL 匿名块分批插入 6400 万行数据，自动触发 Interval 分区扩展
declare
v_base number;
v_sql varchar2(4000);
begin
for i in 0..127 loop
v_base := i*500000;
v_sql := 'insert into t_big(id,p_date,k,pad) '
|| 'select level + ' || v_base || ','' '
|| 'to_date(''2024-01-01'',''YYYY-MM-DD'') + mod(level,32) '
|| '+ numtoyminterval(mod(level,32),''MONTH''), '
|| 'mod(level,10000000), '
|| 'lpad(''x'',200,''x'') '
|| 'from dual connect by level <= 500000';
execute immediate v_sql;
commit;
if mod(i+1,16) = 0 then
DBMS_OUTPUT.PUT_LINE('Batch ' || (i+1) || '/128 done at ' || TO_CHAR(SYSDATE,'HH24:MI:SS'));
END IF;
end loop;
end;
/
select count(*) from t_big
+----------+
| COUNT(*) |
+----------+
| 64000000 |
+----------+
1 row in set (27.284 sec)
obclient(SYS@oracle)[SYS]>; select partition_name,high_value from user_tab_partitions where table_name = 'T_BIG' order by partition_position;
+----------------+-----------------------------------------------------------------------------------------------------------+
| PARTITION_NAME | HIGH_VALUE |
+----------------+-----------------------------------------------------------------------------------------------------------+
| P_202401 | TO_DATE(' 2024-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500008 | TO_DATE(' 2024-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500010 | TO_DATE(' 2024-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500011 | TO_DATE(' 2024-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500012 | TO_DATE(' 2024-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500013 | TO_DATE(' 2024-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500014 | TO_DATE(' 2024-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500015 | TO_DATE(' 2024-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500016 | TO_DATE(' 2024-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500017 | TO_DATE(' 2024-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500018 | TO_DATE(' 2024-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500019 | TO_DATE(' 2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500020 | TO_DATE(' 2025-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500021 | TO_DATE(' 2025-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500022 | TO_DATE(' 2025-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500023 | TO_DATE(' 2025-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500024 | TO_DATE(' 2025-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500025 | TO_DATE(' 2025-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500026 | TO_DATE(' 2025-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500027 | TO_DATE(' 2025-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500028 | TO_DATE(' 2025-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500029 | TO_DATE(' 2025-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500030 | TO_DATE(' 2025-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500031 | TO_DATE(' 2026-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500032 | TO_DATE(' 2026-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500033 | TO_DATE(' 2026-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500034 | TO_DATE(' 2026-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500035 | TO_DATE(' 2026-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500036 | TO_DATE(' 2026-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500037 | TO_DATE(' 2026-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500038 | TO_DATE(' 2026-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
| SYS_P500039 | TO_DATE(' 2026-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') |
+----------------+-----------------------------------------------------------------------------------------------------------+
32 rows in set (0.035 sec)
确认执行计划命中全局索引
obclient(SYS@oracle)[SYS]> explain select /*+ index(t gi_k) */ id,pad from t_big t where k = 12345;
+------------------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------------------+
|================================================================================================|
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
|================================================================================================|
| 0|DISTRIBUTED TABLE RANGE SCAN|T_GI_K|128 |3669 |
|================================================================================================|
Outputs & filters:
-------------------------------------
0 - output([T.ID], [T.PAD]), filter(nil), rows
access([T.ID], [T.P_DATE], [T.PAD]), partitions(p0)
is_index_back=true, is_global_index=true,
range_key([T.K], [T.ID], [T.P_DATE]), range(12345,MIN,MIN ; 12345,MAX,MAX),
range_cond([T.K = cast(12345, DECIMAL_INT(18, 0))])
12 rows in set (0.006 sec)
对即将被 TRUNCATE/DROP 的分区追加 400 万行数据，增大该分区数据量以放大 DDL 维护索引的开销
obclient [SYS]> DECLARE
v_batch NUMBER := 100000;
v_total NUMBER := 4000000;
BEGIN
FOR i IN 1..(v_total/v_batch) LOOP
INSERT /*+ APPEND */ INTO t_big (id, p_date, k, pad)
SELECT 90000000 + (i-1)*v_batch + LEVEL,
TO_DATE('2024-03-15','YYYY-MM-DD'),
MOD(LEVEL,10000000),
LPAD('x',200,'x')
FROM dual CONNECT BY LEVEL ; select count(*) from t_big partition(SYS_P500020);
+----------+
| COUNT(*) |
+----------+
| 6100000 |
+----------+
1 row in set (1.823 sec)
5、准备测试工具
准备业务压测脚本使用 10 个并发 Shell 进程，持续 300 秒，每秒统计全局索引查询的瞬时 QPS
#!/usr/bin/env bash
DSN="-h10.211.xx.xx -P2883 -uSYS@test_oracle#xxx -pxxx -Ac"
SEC=300
CONC=10
BV=12345
echo "[INFO] Start: $(date '+%H:%M:%S') SEC=$SEC CONC=$CONC BV=$BV"
rm -f /tmp/qps_result_*.txt /tmp/qps_snap_*.txt err.log
for i in $(seq 1 $CONC); do
(
end=$(( $(date +%s) + SEC ))
n=0
snap_ts=$(date +%s)
while [ "$(date +%s)" -lt "$end" ]; do
obclient $DSN -N -e "select /*+ INDEX(t gi_k) */ id,pad from t_big t where k=$BV;" > /dev/null 2>>err.log
ret=$?
if [ $ret -eq 0 ];then
n=$((n+1))
fi
now_ts=$(date +%s)
if [ "$now_ts" -ne "$snap_ts" ];then
echo "$now_ts $n" > /tmp/qps_snap_${i}.txt
snap_ts=$now_ts
fi
done
echo "$(date +%s) $n" > /tmp/qps_snap_${i}.txt
echo "$n" > /tmp/qps_result_${i}.txt
) &
echo "[INFO] Started worker $i"
done
prev_total=0
main_end=$(( $(date +%s) + SEC ))
while [ "$(date +%s)" -le "$main_end" ]; do
sleep 1
curr_total=0
for i in $(seq 1 $CONC); do
f=/tmp/qps_snap_${i}.txt
if [ -f "$f" ];then
val=$(tail -n1 "$f"|awk '{print $2}')
curr_total=$((curr_total + val))
fi
done
delta=$(( curr_total - prev_total ))
ts=$(date '+%H:%M:%S')
echo "[QPS_SNAP] $ts inst_qps=$delta total_done=$curr_total"
prev_total=$curr_total
done
wait
total=0
for i in $(seq 1 $CONC); do
[ -f /tmp/qps_result_${i}.txt ] && total=$((total + $(cat /tmp/qps_result_${i}.txt)))
done
echo "[INFO] End: $(date '+%H:%M:%S')"
echo "TOTAL=$total QPS=$(( total / SEC ))"
在独立数据库会话中创建存储过程以 1 秒间隔持续轮询全局索引状态，精确捕捉索引失效瞬间
CREATE OR REPLACE PROCEDURE poll_index_forever(
p_table_name IN VARCHAR2,
p_rounds IN NUMBER DEFAULT 60,
p_interval IN NUMBER DEFAULT 1
)
AS
CURSOR cur_idx IS
SELECT index_name, status
FROM user_indexes
WHERE table_name = UPPER(p_table_name)
ORDER BY index_name;
BEGIN
FOR i IN 1 .. p_rounds LOOP
FOR r IN cur_idx LOOP
DBMS_OUTPUT.PUT_LINE(
TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF3') || ' | ' || r.index_name || ' >' || r.status
);
END LOOP;
DBMS_LOCK.SLEEP(p_interval);
END LOOP;
END;
/
6、测试结果
6.1、OceanBase 4.4.2.0
在压测脚本运行过程中对600w行数据的分区执行DDL操作,执行耗时约3分钟
alter table t_big truncate partition p_202401 update global indexes;
alter table t_big drop partition SYS_P500008 update global indexes;
Query OK, 0 rows affected (2 min 59.874 sec)
在执行分区DDL操作后 QPS 骤降至 0，持续约 3 分钟后开始逐步恢复呈现典型的"业务雪崩—缓慢恢复"曲线
[user_root@mgrsrv1 ~]$ ./qps.sh[INFO] Start: 16:27:18 SEC=300 CONC=10 BV=12345..........[QPS_SNAP] 16:27:35 inst_qps=620 total_done=9880[QPS_SNAP] 16:27:36 inst_qps=610 total_done=10490[QPS_SNAP] 16:27:37 inst_qps=620 total_done=11110[QPS_SNAP] 16:27:38 inst_qps=610 total_done=11720[QPS_SNAP] 16:27:39 inst_qps=620 total_done=12340[QPS_SNAP] 16:27:40 inst_qps=600 total_done=13560[QPS_SNAP] 16:27:42 inst_qps=610 total_done=14170[QPS_SNAP] 16:27:43 inst_qps=620 total_done=14790[QPS_SNAP] 16:27:44 inst_qps=630 total_done=15420[QPS_SNAP] 16:27:45 inst_qps=620 total_done=16040[QPS_SNAP] 16:27:46 inst_qps=620 total_done=16040[QPS_SNAP] 16:27:47 inst_qps=0 total_done=16040[QPS_SNAP] 16:27:48 inst_qps=0 total_done=16040[QPS_SNAP] 16:27:49 inst_qps=0 total_done=16040[QPS_SNAP] 16:27:50 inst_qps=0 total_done=16040[QPS_SNAP] 16:27:51 inst_qps=0 total_done=16040[QPS_SNAP] 16:27:52 inst_qps=0 total_done=16040[QPS_SNAP] 16:27:53 inst_qps=0 total_done=16040[QPS_SNAP] 16:27:54 inst_qps=0 total_done=16040[QPS_SNAP] 16:27:55 inst_qps=0 total_done=16040[QPS_SNAP] 16:27:56 inst_qps=487 total_done=16527[QPS_SNAP] 16:27:57 inst_qps=0 total_done=16527[QPS_SNAP] 16:27:58 inst_qps=0 total_done=16527[QPS_SNAP] 16:27:59 inst_qps=0 total_done=16527[QPS_SNAP] 16:28:00 inst_qps=0 total_done=16527[QPS_SNAP] 16:28:01 inst_qps=0 total_done=16527[QPS_SNAP] 16:28:02 inst_qps=10 total_done=16537[QPS_SNAP] 16:28:03 inst_qps=0 total_done=16537[QPS_SNAP] 16:28:04 inst_qps=0 total_done=16537[QPS_SNAP] 16:28:05 inst_qps=0 total_done=16537[QPS_SNAP] 16:28:06 inst_qps=10 total_done=16547[QPS_SNAP] 16:28:07 inst_qps=0 total_done=16547[QPS_SNAP] 16:28:08 inst_qps=0 total_done=16547..........[QPS_SNAP] 16:30:45 inst_qps=1 total_done=16858[QPS_SNAP] 16:30:46 inst_qps=58 total_done=16916[QPS_SNAP] 16:30:47 inst_qps=245 total_done=17161[QPS_SNAP] 16:30:48 inst_qps=353 total_done=17514[QPS_SNAP] 16:30:49 inst_qps=497 total_done=18011[QPS_SNAP] 16:30:50 inst_qps=569 total_done=18580[QPS_SNAP] 16:30:51 inst_qps=620 total_done=19200[QPS_SNAP] 16:30:52 inst_qps=640 total_done=19840[QPS_SNAP] 16:30:53 inst_qps=630 total_done=20470[QPS_SNAP] 16:30:54 inst_qps=640 total_done=21110[QPS_SNAP] 16:30:55 inst_qps=630 total_done=21740[QPS_SNAP] 16:30:56 inst_qps=630 total_done=22370[QPS_SNAP] 16:30:57 inst_qps=630 total_done=23000[QPS_SNAP] 16:30:58 inst_qps=648 total_done=23648
全局索引 GI_K 在执行了分区DDL操作后立刻变为 UNUSABLE，直到DDL操作完成后才恢复为 VALID，不可用时长约 3 分钟。
16:27:37.164 | PK_T_BIG > VALID
16:27:38.165 | GI_K > VALID
16:27:38.165 | PK_T_BIG > VALID
16:27:39.166 | GI_K > VALID
16:27:39.166 | PK_T_BIG > VALID
16:27:40.168 | GI_K > VALID
16:27:40.168 | PK_T_BIG > VALID
16:27:41.169 | GI_K > VALID
16:27:41.169 | PK_T_BIG > VALID
16:27:42.171 | GI_K > VALID
16:27:42.171 | PK_T_BIG > VALID
16:27:43.172 | GI_K > VALID
16:27:43.172 | PK_T_BIG > VALID
16:27:44.174 | GI_K > VALID
16:27:44.174 | PK_T_BIG > VALID
16:27:45.175 | GI_K > VALID
16:27:45.175 | PK_T_BIG > VALID
16:27:46.177 | GI_K > UNUSABLE
16:27:46.177 | PK_T_BIG > VALID
16:27:47.179 | GI_K > UNUSABLE
16:27:47.179 | PK_T_BIG > VALID
16:27:48.180 | PK_T_BIG > VALID
16:27:49.182 | GI_K > UNUSABLE
16:27:49.182 | PK_T_BIG > VALID
16:27:50.184 | GI_K > UNUSABLE
16:27:50.184 | PK_T_BIG > VALID
16:27:51.185 | GI_K > UNUSABLE
16:27:51.186 | PK_T_BIG > VALID
16:27:52.187 | GI_K > UNUSABLE
16:27:52.187 | PK_T_BIG > VALID
16:27:53.189 | GI_K > UNUSABLE
16:27:53.189 | PK_T_BIG > VALID
16:27:54.190 | GI_K > UNUSABLE
16:27:54.191 | PK_T_BIG > VALID
16:27:55.192 | GI_K > UNUSABLE
16:27:55.192 | PK_T_BIG > VALID
16:27:56.194 | GI_K > UNUSABLE
16:27:56.194 | PK_T_BIG > VALID
16:27:57.196 | GI_K > UNUSABLE
16:27:57.196 | PK_T_BIG > VALID
16:27:58.197 | GI_K > UNUSABLE
16:27:58.198 | PK_T_BIG > VALID
16:27:59.199 | GI_K > UNUSABLE
16:27:59.199 | PK_T_BIG > VALID
16:28:00.201 | GI_K > UNUSABLE
16:28:00.201 | PK_T_BIG > VALID
16:28:01.203 | GI_K > UNUSABLE
16:28:01.203 | PK_T_BIG > VALID
16:28:02.204 | GI_K > UNUSABLE
16:28:02.204 | PK_T_BIG > VALID
16:28:03.206 | GI_K > UNUSABLE
16:28:03.206 | PK_T_BIG > VALID
16:28:04.208 | GI_K > UNUSABLE
16:28:04.208 | PK_T_BIG > VALID
16:28:05.209 | GI_K > UNUSABLE
16:28:05.210 | PK_T_BIG > VALID
16:28:06.211 | GI_K > UNUSABLE
16:28:06.211 | PK_T_BIG > VALID
16:28:07.213 | GI_K > UNUSABLE
16:28:07.213 | PK_T_BIG > VALID
16:28:08.214 | GI_K > UNUSABLE
16:28:08.215 | PK_T_BIG > VALID
16:28:09.216 | GI_K > UNUSABLE
16:28:09.216 | PK_T_BIG > VALID
16:28:10.218 | GI_K > UNUSABLE
16:28:10.218 | PK_T_BIG > VALID
16:28:11.219 | GI_K > UNUSABLE
16:28:11.220 | PK_T_BIG > VALID
16:28:12.221 | GI_K > UNUSABLE
16:28:13.223 | GI_K > UNUSABLE
16:28:13.223 | PK_T_BIG > VALID
16:28:14.225 | GI_K > UNUSABLE
16:28:14.225 | PK_T_BIG > VALID
16:28:15.227 | GI_K > UNUSABLE
16:28:15.227 | PK_T_BIG > VALID
16:28:16.228 | GI_K > UNUSABLE
16:28:16.228 | PK_T_BIG > VALID
16:28:17.230 | GI_K > UNUSABLE
16:28:17.230 | PK_T_BIG > VALID
16:28:18.232 | GI_K > UNUSABLE
16:28:18.232 | PK_T_BIG > VALID
16:28:19.233 | GI_K > UNUSABLE
16:28:19.233 | PK_T_BIG > VALID
16:28:20.235 | GI_K > UNUSABLE
16:28:20.235 | PK_T_BIG > VALID
16:28:21.237 | GI_K > UNUSABLE
16:28:21.237 | PK_T_BIG > VALID
16:28:22.239 | GI_K > UNUSABLE
16:28:22.239 | PK_T_BIG > VALID
..........
16:30:44.488 | PK_T_BIG > VALID
16:30:45.490 | GI_K > VALID
16:30:45.490 | PK_T_BIG > VALID
16:30:46.492 | GI_K > VALID
16:30:46.492 | PK_T_BIG > VALID
16:30:47.493 | GI_K > VALID
16:30:47.493 | PK_T_BIG > VALID
16:30:48.495 | GI_K > VALID
16:30:48.495 | PK_T_BIG > VALID
16:30:49.496 | GI_K > VALID
6.2、OceanBase 4.4.2.1
沿用完全相同的测试案例和数据集，在 4.4.2.1 版本数据库中重新执行。
在压测脚本运行过程中对600w行数据的分区执行DROP操作,执行耗时仅0.09秒、执行truncate操作仅0.56秒
alter table t_big truncate partition p_202401 update global indexes;
Query OK, 0 rows affected (0.056 sec)
alter table t_big drop partition SYS_P500008 update global indexes;
Query OK, 0 rows affected (0.09 sec)
业务QPS全程稳定在 700 左右，DDL 执行期间未出现任何 QPS 跌落至 0 的情况，业务完全无感知。
[user_root@mgrsrv1 ~]$ ./qps.sh[INFO] Start: 14:47:30 SEC=300 CONC=10 BV=12345[QPS_SNAP] 14:47:56 inst_qps=720 total_done=18014[QPS_SNAP] 14:47:57 inst_qps=719 total_done=18733[QPS_SNAP] 14:47:58 inst_qps=721 total_done=19454[QPS_SNAP] 14:47:59 inst_qps=720 total_done=20174[QPS_SNAP] 14:48:00 inst_qps=719 total_done=20893[QPS_SNAP] 14:48:01 inst_qps=722 total_done=21615[QPS_SNAP] 14:48:02 inst_qps=720 total_done=22335[QPS_SNAP] 14:48:03 inst_qps=726 total_done=23061[QPS_SNAP] 14:48:04 inst_qps=728 total_done=23789[QPS_SNAP] 14:48:05 inst_qps=726 total_done=24515[QPS_SNAP] 14:48:06 inst_qps=717 total_done=25232[QPS_SNAP] 14:48:07 inst_qps=726 total_done=25958[QPS_SNAP] 14:48:08 inst_qps=724 total_done=26682[QPS_SNAP] 14:48:09 inst_qps=731 total_done=27413[QPS_SNAP] 14:48:10 inst_qps=732 total_done=28145[QPS_SNAP] 14:48:11 inst_qps=723 total_done=28868[QPS_SNAP] 14:48:12 inst_qps=724 total_done=29592[QPS_SNAP] 14:48:14 inst_qps=1170 total_done=30762[QPS_SNAP] 14:48:15 inst_qps=1010 total_done=31772[QPS_SNAP] 14:48:16 inst_qps=722 total_done=32494[QPS_SNAP] 14:48:17 inst_qps=724 total_done=33218[QPS_SNAP] 14:48:18 inst_qps=730 total_done=33948[QPS_SNAP] 14:48:19 inst_qps=731 total_done=34679[QPS_SNAP] 14:48:20 inst_qps=727 total_done=35406[QPS_SNAP] 14:48:21 inst_qps=733 total_done=36139[QPS_SNAP] 14:48:22 inst_qps=725 total_done=36864[QPS_SNAP] 14:48:23 inst_qps=726 total_done=37590[QPS_SNAP] 14:48:24 inst_qps=730 total_done=38320[QPS_SNAP] 14:48:25 inst_qps=724 total_done=39044[QPS_SNAP] 14:48:26 inst_qps=730 total_done=39774[QPS_SNAP] 14:48:27 inst_qps=729 total_done=40503[QPS_SNAP] 14:48:28 inst_qps=730 total_done=41233[QPS_SNAP] 14:48:29 inst_qps=727 total_done=41960[QPS_SNAP] 14:48:30 inst_qps=729 total_done=42689[QPS_SNAP] 14:48:31 inst_qps=731 total_done=43420[QPS_SNAP] 14:48:32 inst_qps=722 total_done=44142[QPS_SNAP] 14:48:33 inst_qps=729 total_done=44871[QPS_SNAP] 14:48:34 inst_qps=736 total_done=45607[QPS_SNAP] 14:48:35 inst_qps=729 total_done=46336[QPS_SNAP] 14:48:36 inst_qps=725 total_done=47061[QPS_SNAP] 14:48:37 inst_qps=729 total_done=47790[QPS_SNAP] 14:48:38 inst_qps=730 total_done=48520[QPS_SNAP] 14:48:39 inst_qps=734 total_done=49254[QPS_SNAP] 14:48:40 inst_qps=736 total_done=49990[QPS_SNAP] 14:48:41 inst_qps=732 total_done=50722[QPS_SNAP] 14:48:42 inst_qps=721 total_done=51443[QPS_SNAP] 14:48:43 inst_qps=727 total_done=52170[QPS_SNAP] 14:48:44 inst_qps=734 total_done=52904[QPS_SNAP] 14:48:45 inst_qps=727 total_done=53631[QPS_SNAP] 14:48:46 inst_qps=679 total_done=54310[QPS_SNAP] 14:48:47 inst_qps=721 total_done=55031[QPS_SNAP] 14:48:48 inst_qps=718 total_done=55749[QPS_SNAP] 14:48:49 inst_qps=730 total_done=56479[QPS_SNAP] 14:48:50 inst_qps=729 total_done=57208[QPS_SNAP] 14:48:51 inst_qps=724 total_done=57932[QPS_SNAP] 14:48:52 inst_qps=727 total_done=58659[QPS_SNAP] 14:48:53 inst_qps=726 total_done=59385[QPS_SNAP] 14:48:54 inst_qps=723 total_done=60108[QPS_SNAP] 14:48:56 inst_qps=1158 total_done=61266[QPS_SNAP] 14:48:57 inst_qps=1009 total_done=62275[QPS_SNAP] 14:48:58 inst_qps=729 total_done=63004. . . . . . . . . . .[QPS_SNAP] 14:52:24 inst_qps=722 total_done=212747[QPS_SNAP] 14:52:25 inst_qps=731 total_done=213478[QPS_SNAP] 14:52:26 inst_qps=722 total_done=214200[QPS_SNAP] 14:52:27 inst_qps=721 total_done=214921[QPS_SNAP] 14:52:29 inst_qps=731 total_done=215652[QPS_SNAP] 14:52:30 inst_qps=1300 total_done=216952[QPS_SNAP] 14:52:31 inst_qps=143 total_done=217095
全局索引 GI_K 在整个 DDL 执行期间全程保持 VALID，未出现任何 UNUSABLE 状态。
14:47:44.891 | PK_T_BIG > VALID
14:47:45.892 | GI_K > VALID
14:47:45.892 | PK_T_BIG > VALID
14:47:46.893 | GI_K > VALID
14:47:46.893 | PK_T_BIG > VALID
14:47:47.894 | GI_K > VALID
14:47:47.894 | PK_T_BIG > VALID
14:47:48.895 | GI_K > VALID
14:47:48.895 | PK_T_BIG > VALID
14:47:49.896 | GI_K > VALID
14:47:49.897 | PK_T_BIG > VALID
14:47:50.898 | GI_K > VALID
14:47:50.898 | PK_T_BIG > VALID
14:47:51.899 | GI_K > VALID
14:47:51.899 | PK_T_BIG > VALID
14:47:52.900 | GI_K > VALID
14:47:52.900 | PK_T_BIG > VALID
14:47:53.901 | GI_K > VALID
14:47:53.901 | PK_T_BIG > VALID
14:47:54.902 | GI_K > VALID
14:47:54.902 | PK_T_BIG > VALID
14:47:55.904 | GI_K > VALID
14:47:55.904 | PK_T_BIG > VALID
14:47:56.905 | GI_K > VALID
14:47:56.905 | PK_T_BIG > VALID
14:47:57.906 | GI_K > VALID
14:47:57.906 | PK_T_BIG > VALID
14:47:58.907| GI_K > VALID
14:47:58.907 | PK_T_BIG > VALID
14:47:59.908 | GI_K > VALID
14:47:59.909 | PK_T_BIG > VALID
14:48:00.910 | GI_K > VALID
14:48:00.910 | PK_T_BIG > VALID
14:48:01.911 | GI_K > VALID
14:48:01.911 | PK_T_BIG > VALID
14:48:02.912 | GI_K > VALID
14:48:02.912 | PK_T_BIG > VALID
14:48:03.913 | GI_K > VALID
14:48:03.914 | PK_T_BIG > VALID
14:48:04.915 | GI_K > VALID
14:48:04.915 | PK_T_BIG > VALID
14:48:05.916 | GI_K > VALID
14:48:05.916 | PK_T_BIG > VALID
14:48:06.917 | GI_K > VALID
14:48:06.917 | PK_T_BIG > VALID
14:48:07.918 | GI_K > VALID
14:48:07.918 | PK_T_BIG > VALID
14:48:08.919 | GI_K > VALID
14:48:08.920 | PK_T_BIG > VALID
14:48:09.921 | GI_K > VALID
14:48:09.921 | PK_T_BIG > VALID
14:48:10.922 | GI_K > VALID
14:48:10.922 | PK_T_BIG > VALID
14:48:11.923 | GI_K > VALID
14:48:11.923 | PK_T_BIG > VALID
14:48:12.924 | GI_K > VALID
14:48:12.924 | PK_T_BIG > VALID
14:48:13.925 | GI_K > VALID
14:48:13.926 | PK_T_BIG > VALID
14:48:14.927 | GI_K > VALID
14:48:14.927 | PK_T_BIG > VALID
14:48:15.928 | GI_K > VALID
14:48:15.928 | PK_T_BIG > VALID
14:48:16.929 | GI_K > VALID
14:48:16.929 | PK_T_BIG > VALID
. . . . . . . .
14:52:26.250 | GI_K > VALID
14:52:26.250 | PK_T_BIG > VALID
14:52:27.251 | GI_K > VALID
14:52:27.251 | PK_T_BIG > VALID
14:52:28.252 | GI_K > VALID
14:52:28.252 | PK_T_BIG > VALID
14:52:29.253 | GI_K > VALID
14:52:29.254 | PK_T_BIG > VALID
14:52:30.255 | GI_K > VALID
14:52:30.255 | PK_T_BIG > VALID
14:52:31.256 | GI_K > VALID
14:52:31.256 | PK_T_BIG > VALID
7、结论
| 对比维度 |
4.4.2.0 |
4.4.2.1 |
提升幅度 |
| DROP PARTITION 耗时 |
179.874 秒 |
0.09 秒 |
约2000 倍 |
| GI_K 索引不可用时长 |
约 179 秒 |
0 秒（全程 VALID） |
彻底消除 |
| 业务 QPS 最低值 |
0（完全中断） |
≈ 620 |
业务零中断 |
| QPS 恢复模式 |
阶梯式缓慢恢复 |
无需恢复 |
— |
- OceanBase 4.4.2.1 在 Oracle 兼容模式下，对 Interval 分区表执行 ALTER TABLE ... TRUNCATE/DROP PARTITION ... UPDATE GLOBAL INDEXES 时，全局索引全程保持 VALID，功能符合预期。
- 同一场景下，DROP PARTITION 操作耗时从 4.4.2.0 的 179.874 秒 降低至 4.4.2.1 的 0.09 秒，性能提升约 2000 倍。
- 4.4.2.0 中业务 QPS 降至 0 并持续约 3 分钟的"雪崩"现象，在 4.4.2.1 中被彻底消除。DDL 执行期间业务查询 QPS 稳定在 720 左右，无任何中断或显著劣化。
- 该特性使得大规模 Interval 分区表的日常分区维护（如历史数据清理、过期分区删除）可以在业务高峰期安全执行，无需安排维护窗口，显著降低了运维复杂度和业务影响风险。
