Oracle SQL为现有表添加取自另一表的随机抽样列

这个问题我碰到过好多次啦——Oracle的查询优化器会把那个没关联主表的子查询只执行一次，结果所有行都拿到同一个随机数。下面给你几个靠谱的解决办法：

方法1：给子查询加主表关联条件 #

给子查询加一个和主表绑定的条件（哪怕是个恒成立的条件），强迫Oracle为每行重新执行随机抽样：

-- 先添加目标列
ALTER TABLE MYORIGTABLE ADD mynum NUMBER;

-- 逐行更新随机值
UPDATE MYORIGTABLE
SET mynum = (
    SELECT randval 
    FROM (
        SELECT randval 
        FROM RANDNUM10 
        ORDER BY dbms_random.value
    ) 
    WHERE rownum = 1
    AND MYORIGTABLE.rowid IS NOT NULL -- 绑定主表，确保每行单独执行子查询
);

这里用MYORIGTABLE.rowid IS NOT NULL作为关联条件，Oracle会认为子查询依赖主表的每一行，所以会逐行去RANDNUM10里抽随机值。

方法2：用CROSS APPLY（Oracle 12c+适用） #

如果你的Oracle版本是12c或更高，CROSS APPLY是更简洁的方案，它天生就是为主表每行执行一次子查询设计的：

ALTER TABLE MYORIGTABLE ADD mynum NUMBER;

-- 用MERGE结合CROSS APPLY更新
MERGE INTO MYORIGTABLE t
USING (
    SELECT t.rowid, r.randval
    FROM MYORIGTABLE t
    CROSS APPLY (
        SELECT randval 
        FROM RANDNUM10 
        ORDER BY dbms_random.value
        FETCH FIRST 1 ROW ONLY -- 替代rownum=1的写法，12c+支持
    ) r
) src
ON (t.rowid = src.rowid)
WHEN MATCHED THEN UPDATE SET t.mynum = src.randval;

这个写法逻辑更清晰，也不容易被优化器误判。

方法3：通过随机行号关联匹配 #

如果RANDNUM10的数据量不大，还可以给两个表都生成随机行号，再通过取模关联，实现循环抽取随机值：

ALTER TABLE MYORIGTABLE ADD mynum NUMBER;

MERGE INTO MYORIGTABLE t
USING (
    SELECT 
        t.rowid,
        r.randval
    FROM (
        SELECT 
            rowid,
            ROW_NUMBER() OVER (ORDER BY dbms_random.value) AS rn
        FROM MYORIGTABLE
    ) t
    JOIN (
        SELECT 
            randval,
            ROW_NUMBER() OVER (ORDER BY dbms_random.value) AS rn,
            COUNT(*) OVER () AS total_count
        FROM RANDNUM10
    ) r ON MOD(t.rn - 1, r.total_count) + 1 = r.rn
) src
ON (t.rowid = src.rowid)
WHEN MATCHED THEN UPDATE SET t.mynum = src.randval;

这种方法适合需要重复利用RANDNUM10里的值，同时保持随机性的场景。

为啥你的原语句不行？ #

你的原查询里，那个抽样子查询和主表MYORIGTABLE没有任何关联，Oracle的优化器会把它当成常量子查询——只执行一次，然后把结果复用给所有行，所以所有行的mynum都是同一个值。

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