使用子查询的Oracle多行更新 [英] Oracle Multi Row Update using Subquery
问题描述
我正在尝试混洗"名称表中的last_names值.我想使用一个子查询来随机化名称的顺序并相应地更新它们.出于混淆的原因，我想这样做，但希望它仍然看起来像真实的数据集.
I am trying to "shuffle" last_names values in the names table. I am wanting to use a sub-select query that randomizes the order of the names and updates them accordingly. I wanting to do this for obfuscation reasons but want it to still look like a real data set.
下面的语句返回"ORA-01427:单行子查询返回多个行"
The statement below returns a "ORA-01427: single-row subquery returns more than one row"
我该如何进行这项工作?
How can I make this work?
UPDATE schema.names set last_name = (
SELECT *
FROM (
SELECT last_name
FROM schema.names
ORDER BY DBMS_RANDOM.RANDOM))
推荐答案
以下PL/SQL块使用Gordon答案中的查询来驱动循环.
the following PL/SQL block uses the query from Gordon's answer to drive a loop.
设置:
create table demo
( name_original varchar2(10)
, name_new varchar2(10) );
-- Two columns initially the same so we can check the results:
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并不是特别有用，但我仍然认为它在技术上很有趣:
My original answer is below, not especially helpful to the OP but I thought it was technically interesting anyway:
优化器意识到不相关的单行子查询只需要执行一次，因此您可以查找一个值并将其应用于所有行.可能有一个提示可以阻止这种情况，但我无法快速浏览(no_merge，no_unnest和rule无效).
The optimiser realises that an uncorrelated single-row subquery only needs to be executed once, so you get one value looked up and applied to all rows. Possibly there is a hint that prevents this but I couldn't find one from a quick look (no_merge, no_unnest and rule had no effect).
以下方法有效(Oracle 12.1)，但仅强加了一条新的规则，即旧名称和新名称必须不同，这使其成为一个关联的子查询，优化器必须为每行评估该子查询. (它还会生成重复项，因为每次随机查找都是独立的，因此对您可能没有用.)
The following worked (Oracle 12.1) but only by imposing an additional rule that old and new names had to be different, making it a correlated subquery which the optimiser has to evaluate for each row. (It also generates duplicates, because each random lookup is independent, so it may be no use to you.)
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如何从中更改执行计划:
At the risk of drifting off-topic, notice how adding the predicate where d2.name_new <> d.name_new changes the execution plan from this:
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)做两个相当不同的事情的精巧示例，如果您想要其中一个的话.
which is a neat example of the same execution plan (Plan hash value 1813657616) doing two rather different things, if ever you want one of those.
(如果有一个提示可以做同样的事情，那么它将成为一个提示更改结果的简洁示例.)
(If there is a hint that does the same thing, it would make a neat example of a hint changing the results.)
这篇关于使用子查询的Oracle多行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
