修改:以下的PL/SQL块使用Gordon答案中的查询来驱动循环。
设置:
create table demo
( name_original varchar2(10)
, name_new varchar2(10) );
insert into demo
select column_value, column_value
from table(sys.dbms_debug_vc2coll('Jim','James','Joe','Jenny','Jane','Jacky'));
代码:
begin
for r in (
select n.rowid as rwd
, n.name_original
, n2.name_new as name_shuffled
from ( select n.*, row_number() over(order by dbms_random.value) as seqnum
from demo n ) n
join
( select n.*, row_number() over(order by dbms_random.value) as seqnum
from demo n ) n2
on n.seqnum = n2.seqnum
)
loop
update demo set name_new = r.name_shuffled
where rowid = r.rwd;
end loop;
end;
以下是我的原始答案,对OP没有特别的帮助,但我认为它在技术上很有趣:
优化器意识到一个不相关的单行子查询只需要执行一次,因此您可以获得一个值并将其应用于所有行。可能有一个提示可以防止这种情况,但我在快速查看中找不到一个(no_merge
,no_unnest
和rule
没有效果)。
以下方法适用于Oracle 12.1,但只是通过强制规定旧名称和新名称必须不同来实现的,使其成为一个相关子查询,优化器必须为每一行评估它。(它还会生成重复项,因为每个随机查找都是独立的,所以可能对您没有用。)
update demo d set name_new =
( select name_new
from demo d2
where d2.name_new <> d.name_new
order by dbms_random.random
fetch first row only );
select * from demo;
NAME_ORIGINAL NAME_NEW
------------- ----------
Jim Jenny
James Jane
Joe Jacky
Jenny Jane
Jane Jacky
Jacky Jim
冒着偏离主题的风险,请注意添加谓词
where d2.name_new <> d.name_new
如何改变执行计划,如下所示:
Plan hash value: 1813657616
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:00.01 | 27 |
| 1 | UPDATE | DEMO | 1 | | 0 |00:00:00.01 | 27 |
| 2 | TABLE ACCESS STORAGE FULL | DEMO | 1 | 82 | 6 |00:00:00.01 | 7 |
|* 3 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 7 |
|* 4 | WINDOW SORT PUSHED RANK | | 1 | 82 | 1 |00:00:00.01 | 7 |
| 5 | TABLE ACCESS STORAGE FULL| DEMO | 1 | 82 | 6 |00:00:00.01 | 7 |
Predicate Information (identified by operation id):
3 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=1)
4 - filter(ROW_NUMBER() OVER ( ORDER BY "DBMS_RANDOM"."RANDOM"())<=1)
转换为:
Plan hash value: 1813657616
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:00.01 | 62 | | | |
| 1 | UPDATE | DEMO | 1 | | 0 |00:00:00.01 | 62 | | | |
| 2 | TABLE ACCESS STORAGE FULL | DEMO | 1 | 82 | 6 |00:00:00.01 | 7 | 1025K| 1025K| |
|* 3 | VIEW | | 6 | 1 | 6 |00:00:00.01 | 42 | | | |
|* 4 | WINDOW SORT PUSHED RANK | | 6 | 4 | 6 |00:00:00.01 | 42 | 2048 | 2048 | 2048 (0)|
|* 5 | TABLE ACCESS STORAGE FULL| DEMO | 6 | 4 | 30 |00:00:00.01 | 42 | 1025K| 1025K| |
Predicate Information (identified by operation id):
3 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=1)
4 - filter(ROW_NUMBER() OVER ( ORDER BY "DBMS_RANDOM"."RANDOM"())<=1)
5 - filter("D2"."NAME_NEW"<>:B1)
这是一个很好的例子,展示了相同的执行计划(计划哈希值为1813657616)在两个不同的情况下执行着截然不同的操作,如果你需要其中之一,可以参考这个例子。
(如果有一个提示可以做到同样的事情,那么它将成为一个很好的例子,说明提示如何改变结果。)
where rownum = 1
添加到外部的SELECT
。 - Gordon Linoff