使用子查询的 Oracle 多行更新

Oracle Multi Row Update using Subquery(使用子查询的 Oracle 多行更新)

问题描述

我正在尝试洗牌"名称表中的 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 多行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！