在行之间随机播放一列

Question

如何有效地将大型（1m至5m记录）表的内容洗牌？该列已知具有唯一值，但您可以假设为此目的删除了所有约束。我的头痛主要是因为我正在更新我正在选择的同一列。我的目标是用PL/SQL来做到这一点，以便我可以通过编程方式执行其他操作，例如记录日志或更新其他表。

**Original table:** 
+----+-----------+ 
| id | fname  | 
+----+-----------+ 
| 1 | mike  | 
| 2 | ricky  | 
| 3 | jane  | 
| 4 | august | 
| 6 | dave  | 
| 9 | Jérôme | 
+----+-----------+ 

**Possible output:** 
+----+-----------+ 
| id | fname  | 
+----+-----------+ 
| 1 | dave  | 
| 2 | jane  | 
| 3 | mike  | 
| 4 | ricky  | 
| 6 | Jérôme | 
| 9 | august | 
+----+-----------+ 

我最新的尝试已经创建使用over (order by dbms_random.value)游标并尝试做一个合并或更新也许是基于ROWNUM。也许我可以通过创建一个临时表来排序修改自我约束？我相当有信心甲骨文有一些奇特的方式来做到这一点，但我的SQL能力受限于基本的CRUD命令。

完整的解决方案是在这里，根据戈登的回答是：

merge into t 
using (
select t.id, t2.name 
from (select t.*, rownum as seqnum 
     from t 
    ) t join 
    (select t.*, row_number() over (order by dbms_random.value) as seqnum 
     from t 
    ) t2 
    on t.seqnum = t2.seqnum 
) src 
on (t.id = src.id) 
when matched then update set t.name = src.name; 

Answer 1

你可以做一个自连接，使用随机行号：

select t.id, t2.name 
from (select t.*, row_number() over (order by dbms_random.value) as seqnum 
     from t 
    ) t join 
    (select t.*, row_number() over (order by dbms_random.value) as seqnum 
     from t 
    ) t2 
    on t.seqnum = t2.seqnum; 

其实，你并不需要为这两个被随机分配：

select t.id, t2.name 
from (select t.*, rownum as seqnum 
     from t 
    ) t join 
    (select t.*, row_number() over (order by dbms_random.value) as seqnum 
     from t 
    ) t2 
    on t.seqnum = t2.seqnum; 

Answer 2

这个答案直接回答（这是我的，所以我相信我被允许reus Ë吧）： https://community.oracle.com/thread/3995265

准备

create table original_table (id number, name varchar2(30)); 

insert into original_table 
    select 1, 'mike' from dual union all 
    select 2, 'ricky' from dual union all 
    select 3, 'jane' from dual union all 
    select 4, 'august' from dual union all 
    select 6, 'dave' from dual union all 
    select 9, 'Jérôme' from dual 
; 

select * from original_table; 

ID NAME 
-- ------ 
1 mike 
2 ricky 
3 jane 
4 august 
6 dave 
9 Jérôme 

更新与置换名行：

merge into original_table o 
    using (
    with 
     helper (id, rn, rand_rn) as (
      select id, 
        row_number() over (order by id), 
        row_number() over (order by dbms_random.value()) 
      from original_table 
     ) 
    select ot.name, h2.id 
    from original_table ot inner join helper h1 on  ot.id = h1.id 
          inner join helper h2 on h1.rand_rn = h2.rn 
) p 
on (o.id = p.id) 
when matched then update set o.name = p.name 
; 

select * from original_table; 

ID NAME 
-- ------ 
1 ricky 
2 dave 
3 Jérôme 
4 jane 
6 august 
9 mike