Oracle PL/SQL如何高效生成10M至30M数值集并随机不重复抽取

一、存储方式选择建议 #

• 不要预存全量数据：10M到30M共2000万条数据，不管存在游标、自定义类型还是普通表，全量生成存储都会占用大量内存/磁盘资源，这也是你之前游标循环方案速度慢的核心原因。
• 按需选择存储：如果仅需抽取指定数量的不重复值，无需预存全部数据；如果必须保留所有数值，优先用全局临时表（GTT）——它的数据仅在会话或事务内存在，不会永久占用空间，Oracle对GTT的性能优化也更到位。

二、无重复随机抽取的高效方案 #

方案1：按需生成+集合跟踪（适合小批量抽取） #

核心逻辑：直接生成随机数，用集合记录已抽取的值，避免重复。无需预存全量数据，内存占用低。

DECLARE
    TYPE num_collection IS TABLE OF NUMBER;
    v_used_nums num_collection := num_collection();
    v_random_num NUMBER;
    v_min CONSTANT NUMBER := 10000000; -- 10M
    v_max CONSTANT NUMBER := 30000000; -- 30M
    v_extract_count CONSTANT NUMBER := 1000; -- 需要抽取的数量
BEGIN
    FOR i IN 1..v_extract_count LOOP
        LOOP
            -- 生成指定范围内的随机整数
            v_random_num := FLOOR(DBMS_RANDOM.VALUE(v_min, v_max + 1));
            -- 检查是否已被抽取
            IF v_random_num NOT MEMBER OF v_used_nums THEN
                v_used_nums.EXTEND;
                v_used_nums(v_used_nums.LAST) := v_random_num;
                -- 此处可添加业务逻辑，比如插入目标表
                DBMS_OUTPUT.PUT_LINE('抽取值：' || v_random_num);
                EXIT;
            END IF;
        END LOOP;
    END LOOP;
END;
/

注意：如果抽取量接近总范围（比如超过1000万），重复检查的概率会急剧上升，此时建议改用方案2。

方案2：全局临时表+批量生成+抽取即删除（适合大量抽取或需保留全量数据） #

1. 创建全局临时表

CREATE GLOBAL TEMPORARY TABLE temp_rand_nums (
    num NUMBER PRIMARY KEY
) ON COMMIT PRESERVE ROWS; -- 事务提交后保留数据，按需改为ON COMMIT DELETE ROWS

2. 批量生成并插入数值

用CONNECT BY批量生成连续数值，比游标循环快几个数量级：

INSERT INTO temp_rand_nums (num)
SELECT v_min + LEVEL - 1
FROM DUAL
CONNECT BY LEVEL <= v_max - v_min + 1;
-- 若使用ON COMMIT PRESERVE ROWS，需提交才能保留数据
COMMIT;

3. 无重复随机抽取

抽取后直接删除对应行，从根源避免重复：

DECLARE
    v_random_num NUMBER;
BEGIN
    -- 随机抽取一行
    SELECT num INTO v_random_num
    FROM temp_rand_nums
    ORDER BY DBMS_RANDOM.VALUE
    FETCH FIRST 1 ROW ONLY;

    -- 删除已抽取的行，确保不会再次抽到
    DELETE FROM temp_rand_nums WHERE num = v_random_num;
    COMMIT;

    DBMS_OUTPUT.PUT_LINE('抽取值：' || v_random_num);
END;
/

优化点：如果需要多次抽取，可一次性抽取多条（调整FETCH FIRST N ROWS ONLY），减少SQL执行次数；主键索引已默认创建，能大幅提升查询和删除的效率。

三、性能优化核心要点 #

• 拒绝单条循环：游标循环单条处理是性能杀手，尽量用批量SQL（如CONNECT BY、INSERT ... SELECT）完成数据生成。
• 减少内存占用：能不预存全量数据就不存，用集合或临时表按需处理。
• 降低重复检查成本：小批量用集合跟踪，大批量用临时表删除已抽取行，避免频繁的集合查找开销。

内容的提问来源于stack exchange，提问作者Freddy Ricaurte López