奇怪的Oracle行为

vptzau2j  于 2023-04-11  发布在  Oracle
关注(0)|答案(2)|浏览(286)

我尝试为现有表中的每一行生成一个uuid,并为两个不同的列设置相同的值。
我写了一个update语句,我认为应该这样做,但它为每一列设置了一个 * 不同的 * uuid。为什么?

CREATE TABLE tbl (
    n number,
    r1 raw(32),
    r2 raw(32)
);

insert into tbl (n) values (1);
insert into tbl (n) values (2);
insert into tbl (n) values (3);

update (select r1, r2, sys_guid() as uuid FROM tbl) 
    set r1 = uuid, r2 = uuid;
    
select n, rawtohex(r1), rawtohex(r2) from tbl;
NRAWTOHEX(R1)RAWTOHEX(R2)
1F89B6F66D7C7A52FE050020A02583951F89B6F66D7C8A52FE050020A02583951
F89B6F66D7C9A52FE050020A02583951F89B6F66D7CAA52FE050020A02583951
F89B6F66D7CBA52FE050020A02583951F89B6F66D7CCA52FE050020A02583951
dgiusagp

dgiusagp1#

这与this questionthis question的问题相同,因为SYS_GUID函数调用没有在子查询中具体化,而是被推送到update语句并每行调用两次(每列一次);但是,这些问题的答案并不完全适用于UPDATE语句。
相反,您可以使用MERGE语句:

MERGE INTO tbl d
USING (
  SELECT ROWID AS rid,
         SYS_GUID() AS uuid
  FROM   tbl s
) s
ON (s.rid = d.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET d.r1 = uuid,
      d.r2 = uuid;

MERGE之后,您的示例数据可能包含:
| N|RAWTOHEX(R1)|RAWTOHEX(R2)|
| --------------|--------------|--------------|
| 1|F89CAC01141C1982E053182BA8C018B2|F89CAC01141C1982E053182BA8C018B2|
| 二|F89CAC01141D1982E053182BA8C018B2|F89CAC01141D1982E053182BA8C018B2|
| 三|F89CAC01141E1982E053182BA8C018B2|F89CAC01141E1982E053182BA8C018B2|
另一种方法是使用两个UPDATE语句:

UPDATE tbl
SET r1 = SYS_GUID();

UPDATE tbl
SET r2 = r1;

fiddle

ep6jt1vc

ep6jt1vc2#

如前所述,这是因为Oracle不存储计算值,而是将原始表达式传播到外部查询。而在join的情况下,它不会这样做。
为了避免在merge期间进行连接,您可以使用update(column [, column ]...) = (subquery)分支,根据需要使用left joindual生成尽可能多的相同计算值。

update tbl 
set (r1, r2) = (
  select a.uuid, a.uuid
  from dual
    left join (
      select sys_guid() as uuid
      from dual
    ) a
    on 1 = 1
  where tbl.r1 = tbl.r1
    or tbl.r1 is null
)
select n, rawtohex(r1), rawtohex(r2)
from tbl
where r1 = r2
NRAWTOHEX(R1)RAWTOHEX(R2)
1F89DCA00DD9D1CE0E053182BA8C0E348F89DCA00DD9D1CE0E053182BA8C0E348
F89DCA00DD9E1CE0E053182BA8C0E348F89DCA00DD9E1CE0E053182BA8C0E348
F89DCA00DD9F1CE0E053182BA8C0E348F89DCA00DD9F1CE0E053182BA8C0E348

UPD。上面的文本中有一个错误,因为我在子查询中丢失了一个强制逐行执行的相关性。所以代码被更新了。

您也可以将rownum添加到set ... = (subquery)中的子查询中,而不是join

update /*+gather_plan_statistics*/ tbl 
set (r1, r2) = (
  select a.uuid, a.uuid
  from (
      select sys_guid() as uuid
      from dual
      where rownum = 1
    ) a
  where tbl.r1 = tbl.r1
    or tbl.r1 is null
)
select n, rawtohex(r1), rawtohex(r2)
from tbl
where r1 = r2
NRAWTOHEX(R1)RAWTOHEX(R2)
1F89DCA00DDA01CE0E053182BA8C0E348F89DCA00DDA01CE0E053182BA8C0E348
F89DCA00DDA11CE0E053182BA8C0E348F89DCA00DDA11CE0E053182BA8C0E348
F89DCA00DDA21CE0E053182BA8C0E348F89DCA00DDA21CE0E053182BA8C0E348

fiddle

相关问题