ORACLE CONNECT BY LEVEL生成重复的行并造成性能问题

Question

由于重复记录，我在使用ORACLE CONNECT BY LEVEL时遇到性能问题。我的源表有大约5万条记录，其中一列(示例中的代码(可以有多个用分号分隔的值。最后，我应该得到一个out，为每个唯一的值(代码(创建一个记录。

来源数据：

CREATE TABLE TEST
(name varchar2(255),
code varchar2(255)
);
insert into test values ('ABC-Amherst','1179;348;403;407;408;412;413;426;428;430');
insert into test values ('ABC-Amherst','1179;352;401;403;412;413;416;420;433;A40');
insert into test values ('ABC-Ville','1179;352;401;403;412;413;416;420;433;A40');
commit;

预期输出：

Name        Code
ABC-Amherst 348
ABC-Amherst 408
ABC-Amherst 430
ABC-Amherst 428
ABC-Amherst 426
ABC-Amherst 433
ABC-Amherst A40
ABC-Amherst 407
ABC-Amherst 352
ABC-Amherst 401
ABC-Amherst 413
ABC-Amherst 403
ABC-Amherst 420
ABC-Amherst 416
ABC-Amherst 1179
ABC-Amherst 412
ABC-Ville   412
ABC-Ville   352
ABC-Ville   A40
ABC-Ville   420
ABC-Ville   416
ABC-Ville   401
ABC-Ville   1179
ABC-Ville   433
ABC-Ville   403
ABC-Ville   413

已使用查询-下面的查询返回重复项。当我在我的完整数据集上运行它时，它会永远运行，并最终冻结。我试着添加不同的/行号，但它们也一直在运行。是否有防止重复的方法或任何其他有效的方法来获得预期的输出？

SELECT name,
trim(regexp_substr(code,'(;|^)([^;]+)',1,level,NULL,2) ) AS code
FROM test
CONNECT BY prior name=name
AND level             <= 1 + regexp_count(code,';')
AND prior sys_guid()  IS NOT NULL;

Answer 1

我想知道json方法是否能提供更好的性能：

select distinct t.name, x.code
from test t
cross apply json_table(
'["' || replace(t.code, ';', '","') || '"]',
'$[*]' columns (code varchar(50) path '$')
) x
order by t.name, x.code

根据您所称的重复，在横向联接中而不是在外部查询中消除重复可能是可以接受的(我不确定这是您想要的，但仍然显示了您将如何进行(：

select t.name, x.code
from test t
cross apply (
select distinct code
from json_table(
'["' || replace(t.code, ';', '","') || '"]',
'$[*]' columns (code varchar(50) path '$')
) x
) x
order by t.name, x.code

DB Fiddle演示