oracle SQL:改进connect by子句
我有一张桌子: Table_1看起来如下所示：
id | column1
-----------------
10 | abc, kdm
20 | xyz, lop, nkk我想要的是转换表，如下所示：
id | column1
-----------------
10 | abc
10 | kdm
20 | xyz
20 | lop
20 | nkk为此，我使用了如下查询：
select id, regexp_substr(column1,'[^,]+', 1, level) from Table_1
connect by regexp_substr(column1, '[^,]+', 1, level) is not null;只要逗号分隔的值较少，此查询就能正常工作。但是当它成长的时候，它消耗了越来越多的时间去处理。
我想到的一个解决方案是创建一个单独的表，然后通过迭代Table_1的值插入值。
伪码如下：
FOR r in each row
FOR i in 1..length(comma_separated_values)
insert into new_table values(id, select regexp_substr(column1,'[^,]+', 1, i) from Table_1
End LOOP;
End LOOP;但是，由于随着逗号分隔值的增长，这也会消耗大量时间，那么是否还有其他最佳方法来做到这一点(首选不使用另一个表，但临时/虚拟表可以)？
我在使用Oracle SQL。
提前谢谢。
回答 3
Stack Overflow用户
发布于 2015-08-31 20:04:31
规范化问题无法忍受，您的原始查询实际上无法工作。在测试数据上运行时：
SQL> with Table_1(id, column1) as (
2 select 10, 'abc, kdm' from dual
3 union
4 select 20, 'xyz, lop, nkk' from dual
5 )
6 select id, regexp_substr(column1,'[^,]+', 1, level) from Table_1
7 connect by regexp_substr(column1, '[^,]+', 1, level) is not null;
ID REGEXP_SUBSTR
---------- -------------
10 abc
10 kdm
20 nkk
20 lop
20 nkk
20 xyz
10 kdm
20 nkk
20 lop
20 nkk
10 rows selected.
SQL>因此，当您添加更多的值时，问题会成倍增长，从而降低性能。通过添加第三行进行测试。另外，分隔符是逗号空间，而不仅仅是逗号。不适用于空，应避免。。不幸的是，它是您在解析列表时看到的最常见的正则表达式。
尝试一下，它应该轻松地处理更大的列表：
SQL> with Table_1(id, column1) as (
select 10, 'abc, kdm' from dual
union
select 20, 'xyz, lop, nkk' from dual
)
SELECT id, -- column1,
-- COLUMN_VALUE AS match_nbr,
REGEXP_SUBSTR( column1 ,'(.*?)(, |$)', 1, COLUMN_VALUE, NULL, 1 ) AS match_value
FROM
Table_1,
TABLE(
CAST(
MULTISET(
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(column1 , ',' )+1
) AS SYS.ODCINUMBERLIST
)
);
ID MATCH_VALUE
---------- -------------
10 abc
10 kdm
20 xyz
20 lop
20 nkk
SQL>基本上，这将使列表中的元素数(包含元素号)形成一个表，每行一行，并将其与主表连接。
取消注释COLUMN_VALUE值行以查看元素的编号。它假定数据不包含逗号。
Stack Overflow用户
发布于 2015-08-31 19:31:22
显然，兰迪提出的解决根本问题的建议将是理想的。如果这是不可能的，那么就有多种选择可供选择。许多人被列入这里名单。通常，一个可以提高性能的简单解决方案是在column1中找到最大数量的值，创建一个包含那么多列的临时表，然后将该临时表转换为所需的格式。也就是说，有一个中间步骤，其中的表看起来像id|val1|val2|val3|..|valn
Stack Overflow用户
发布于 2015-08-31 19:20:11
您可以尝试一下DBMS_UTILITY.COMMA_TO_TABLE过程，我想用户定义的解决方案应该更快一些。
文档链接：util.htm
https://stackoverflow.com/questions/32317943
复制相似问题
腾讯云开发者
