Oracle存储过程执行陷入无限循环无法完成问题求助

首先咱们拆解下你遇到的问题：你写的FOR循环理论上只会执行2次，但实际看起来像无限循环，大概率不是真的无限，而是大表操作的性能极差导致执行时间超长，再加上你用的SAMPLE子句本身的特性，让问题雪上加霜。

问题根源分析 #

1. SAMPLE子句的局限性：Oracle的SAMPLE(n)是基于数据块的采样，不是行级随机采样。当你用极低的采样比例（0.00091%），很可能多次采样到同一个数据块，甚至已经被删除的行所在的块——要么重复拿到同一个RUN_VER_ISSUE_ID，要么直接触发NO_DATA_FOUND异常（如果该ID的记录已经被删）。
2. 无索引导致全表扫描：1TB的大表，如果RUN_VER_ISSUE_ID上没有索引，每次DELETE都会触发全表扫描，扫1TB数据的时间会非常久，两次循环的执行时间可能让你误以为是“无限循环”。
3. 缺乏异常处理：如果SAMPLE没返回任何行，SELECT INTO会直接抛出异常终止块执行，但如果是性能问题，你会看到会话一直在执行，没有终止。

优化后的解决方案 #

针对大表随机删除的场景，推荐用批量获取随机ID + 批量删除的方式，既避免重复采样，又提升执行效率：

DECLARE
    -- 定义存储ID的集合类型
    TYPE id_collection IS TABLE OF MV_RUN_VER_IES.RUN_VER_ISSUE_ID%TYPE;
    v_target_ids id_collection;
BEGIN
    -- 批量获取2个随机的RUN_VER_ISSUE_ID（真正的行级随机）
    SELECT RUN_VER_ISSUE_ID
    BULK COLLECT INTO v_target_ids
    FROM MV_RUN_VER_IES
    ORDER BY DBMS_RANDOM.VALUE  -- 用随机函数实现行级随机排序
    FETCH FIRST 2 ROWS ONLY;     -- 只取需要的行数

    -- 批量删除选中的记录，比逐行删高效得多
    FORALL i IN 1..v_target_ids.COUNT
        DELETE FROM MV_RUN_VER_IES 
        WHERE RUN_VER_ISSUE_ID = v_target_ids(i);

    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('表中已无可用记录可删除');
        COMMIT;
END;
/

额外优化建议 #

1. 给RUN_VER_ISSUE_ID加索引：这是提升DELETE速度的关键，索引能让数据库快速定位到要删除的行，避免全表扫描。
2. 删除大量行时用表重建替代删除：如果要删除的行数占比很高（比如超过30%），直接用CREATE TABLE new_table AS SELECT * FROM MV_RUN_VER_IES WHERE ...保留需要的行，然后重命名表、重建索引，这比删除操作快N倍（因为大表删除会产生巨量的undo和redo日志）。
3. 调整随机采样方式：如果要选大量随机行，DBMS_RANDOM.VALUE排序的开销会变大，可以结合SAMPLE先缩小范围，再随机排序：

   SELECT RUN_VER_ISSUE_ID
   FROM MV_RUN_VER_IES SAMPLE(0.1)  -- 先采样1%的数据缩小范围
   ORDER BY DBMS_RANDOM.VALUE
   FETCH FIRST 100 ROWS ONLY;
   

内容的提问来源于stack exchange，提问作者arsm4