Oracle ORA-22922: 并行合并CLOB数据时不存在LOB值

嗨，我在执行clob数据的并行合并时遇到问题。

我正在为解决方案的一部分开发一个新流程，我正在准备使用各种数据类型的随机数据进行用例测试的数据集。我使用一个函数来创建随机大小的CLOB:

create or replace function RANDOM_CLOB
( min_length number
, max_length number
, muliplier number default 1
) return clob
is 
  l_clob clob := empty_clob();
begin  
  for i in 1 .. trunc(dbms_random.value(round(min_length/muliplier,0), round(max_length/muliplier,0)),0)
  loop
    l_clob := l_clob || dbms_random.string('A', muliplier);
  end loop;
  return l_clob;
end;
/
复制

这很好。为了准备我的前三个测试用例，我为ucl创建了一个初始数据集，然后为uc2随机删除了28条记录的数据集。对于US3，我使用新的随机varchar，numeric和CLOB数据更新了25条记录。这发生在合并语句和抛出错误:

ORA-12801: 并行查询服务器P008中的错误信号
ORA-22922: 不存在的LOB值

我在下面使用的脚本是:

/* PREPARE TEST CASE ================================================================================================================================= */
/* Prepare Test Case 1 - 123 inserts */
create table reg3_test_case_1
( reg3_test_nk        varchar2(30)
, col_txt1            varchar2(100)
, col_txt2            varchar2(100)
, col_clob            clob
, col_num             number
, col_date            date
, constraint reg3_test_case_1_u01 unique (reg3_test_nk) enable
) 
parallel 
nologging;

insert into reg3_test_case_1 
( reg3_test_nk
, col_txt1
, col_txt2
, col_clob
, col_num
, col_date
)
select 
  'NK'|| to_char(level,'0000000') as reg3_test_nk
, 'Test 1 - Insert record '|| to_char(level,'0000000')
, DBMS_RANDOM.STRING('A', DBMS_RANDOM.value( 1,100 ))
, random_clob(32767,500000,100)
, round(DBMS_RANDOM.value( 100,10000 ),0)
, sysdate
from dual 
connect by level <= 123;
commit;

/* Prepare Test Case 2 - 28 deletes */
create table reg3_test_case_2 parallel nologging
as 
select * 
from reg3_test_case_1
where reg3_test_nk in
  ( select reg3_test_nk from (select reg3_test_nk from reg3_test_case_1 order by dbms_random.value) where rownum <= 95);

/* Prepare Test Case 3 - 25 updates */
create table reg3_test_case_3 parallel nologging
as select *
from reg3_test_case_2;

merge into reg3_test_case_3 t
using 
( select 
    reg3_test_nk
  , 'Update ' || to_char(rownum,'00') as COL_TXT1
  , DBMS_RANDOM.STRING('P', DBMS_RANDOM.value( 1,100 )) as COL_TXT2
  , random_clob(32767,100000,1000) as COL_CLOB
  , rownum as COL_NUM
  , sysdate as COL_DATE
  from
  ( select *
    from reg3_test_case_3 t
    order by dbms_random.value
  ) where rownum <= 25
) s
on (s.reg3_test_nk = t.reg3_test_nk)
when matched then
update
set t.COL_TXT1 = s.COL_TXT1
  , t.COL_TXT2 = s.COL_TXT2
  , t.COL_CLOB = s.COL_CLOB
  , t.COL_NUM = s.COL_NUM
  , t.COL_DATE = s.COL_DATE
;
commit;
复制

我在LiveSQL上有类似的脚本，但是出于存储考虑，我不得不减小CLOB条目的大小。在该系统上，它可以正常工作，但是使用上述值，我得到了
ORA-12801: 并行查询服务器P008中的错误信号
ORA-22922: 不存在的LOB值

错误。

奇怪的是，我发现如果我使用/* nomarallell * 运行合并？select中的子句然后它的工作原理。

merge into reg3_test_case_3 t
using 
( select /*+ noparallel */
    reg3_test_nk
  , 'Update ' || to_char(rownum,'00') as COL_TXT1
  , DBMS_RANDOM.STRING('P', DBMS_RANDOM.value( 1,100 )) as COL_TXT2
  , random_clob(32767,100000,1000) as COL_CLOB
  , rownum as COL_NUM
  , sysdate as COL_DATE
  from
  ( select *
    from reg3_test_case_3 t
    order by dbms_random.value
  ) where rownum <= 25
) s
on (s.reg3_test_nk = t.reg3_test_nk)
when matched then
update
set t.COL_TXT1 = s.COL_TXT1
  , t.COL_TXT2 = s.COL_TXT2
  , t.COL_CLOB = s.COL_CLOB
  , t.COL_NUM = s.COL_NUM
  , t.COL_DATE = s.COL_DATE
;
复制

These are small sample sets of test data. Ultimately I will be pushing this to millions or rows and GBs of storage for testing our new process. Can you explain what might be causing the issue of ORA-22922: 不存在的LOB值 when it comes to the parallel selection of data for use inside a merge statement and how to solve it?

这个问题已经是一段旅程了!

首先，我可以在12.2上重现此内容，但不能在19c上重现。但是在19c上运行此程序，获取计划，您会在底部找到以下行:

- PDML disabled because function is not pure and not declared parallel enabled

您需要在函数中添加parallel_enable子句:

create or replace function RANDOM_CLOB
( min_length number
, max_length number
, muliplier number default 1
) return clob parallel_enable 
is 
  l_clob clob := empty_clob();
begin  
  for i in 1 .. trunc(dbms_random.value(round(min_length/muliplier,0), round(max_length/muliplier,0)),0)
  loop
    l_clob := l_clob || dbms_random.string('A', muliplier);
  end loop;
  return l_clob;
end;
/
复制

在12.2和语句上执行此操作，合并为我运行而没有错误。但是该计划现在有以下说明:

- PDML disabled because table has one or more lob columns

正如医生所说:

Parallel UPDATE, DELETE, and MERGE operations on such tables are not supported.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/types-parallelism.html#GUID-6626C70C-876C-47A4-8C01-9B66574062D8

所以如果你想并行化整个语句，它需要一点重新思考。我建议研究DIY平行度https://blogs.oracle.com/oraclemagazine/on-working-in-parallel