blocks|key|1200175|text|您可以尝试一下DBMS_UTILITY.COMMA_TO_TABLE过程，我想用户定义的解决方案应该更快一些。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1200176|文档链接：util.htm|offset|length|1200177|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_util.htm^0|0|5|8|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

You can try DBMS_UTILITY.COMMA_TO_TABLE procedures for this, I guess it should be more faster that user defined solutions.

Link for documentation : <a href="http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_util.htm" rel="nofollow">http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_util.htm</a>

blocks|key|29981|text|显然，兰迪提出的解决根本问题的建议将是理想的。如果这是不可能的，那么就有多种选择可供选择。许多人被列入这里名单。通常，一个可以提高性能的简单解决方案是在column1中找到最大数量的值，创建一个包含那么多列的临时表，然后将该临时表转换为所需的格式。也就是说，有一个中间步骤，其中的表看起来像id%7Cval1%7Cval2%7Cval3%7C..%7Cvaln|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|29982|entityMap|0|LINK|mutability|MUTABLE|url|http://lalitkumarb.com/2014/12/02/split-comma-delimited-string-into-rows-in-oracle/^0|41|P|1F|2|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@$9|Q|A|R|1|S]]|E|$]]|$1|F|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

Obviously Randy's suggestion to fix the root problem would be ideal. If that is not possible, than a variety of options are available. Many are listed <a href="http://lalitkumarb.com/2014/12/02/split-comma-delimited-string-into-rows-in-oracle/" rel="nofollow">here</a>
. In general though a simple solution that may improve your performance would be to find a max number of values in column1, create a temp table with that many columns, then convert that temp table into your desired format. I.e., have an intermediary step where the table looks like <code>id|val1|val2|val3|..|valn</code>

blocks|key|781180|text|规范化问题无法忍受，您的原始查询实际上无法工作。在测试数据上运行时：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|781181|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,'[%5E,]%2B',+1,+level)+from+Table_1
++7++connect+by+regexp_substr(column1,+'[%5E,]%2B',+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>|code-block|syntax|javascript|781182|因此，当您添加更多的值时，问题会成倍增长，从而降低性能。通过添加第三行进行测试。另外，分隔符是逗号空间，而不仅仅是逗号。不适用于空，应避免。。不幸的是，它是您在解析列表时看到的最常见的正则表达式。|offset|length|781183|尝试一下，它应该轻松地处理更大的列表：|781184|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+,'(.*?)(,+%7C$)',+1,+COLUMN_VALUE,+NULL,+1+)+AS+match_value
+++FROM
+++++Table_1,
+++++TABLE(
+++++++CAST(
+++++++++MULTISET(
+++++++++++SELECT+LEVEL
+++++++++++FROM+++DUAL
+++++++++++CONNECT+BY+LEVEL+<=+REGEXP_COUNT(column1+,+','+)%2B1
+++++++++)+AS+SYS.ODCINUMBERLIST
+++++++)
+++++);

++++++++ID+MATCH_VALUE
----------+-------------
++++++++10+abc
++++++++10+kdm
++++++++20+xyz
++++++++20+lop
++++++++20+nkk

SQL>|781185|基本上，这将使列表中的元素数(包含元素号)形成一个表，每行一行，并将其与主表连接。|781186|取消注释COLUMN_VALUE值行以查看元素的编号。它假定数据不包含逗号。|781187|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/31464275/split-comma-separated-values-to-columns-in-oracle/31464699#31464699^0|0|0|1O|A|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@$I|13|J|14|1|15]]|A|$]]|$1|K|3|L|5|6|7|16|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|18|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|19|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

Normalization issues not withstanding, your original query actually does not work. When run on your test data:

<pre><code>SQL&gt; 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&gt;
</code></pre>

So, when you add more values, the problem grows exponentially, thus your performance degrades. Test by adding a third row. Also, your delimiter is comma-space, not just a comma. <a href="https://stackoverflow.com/questions/31464275/split-comma-separated-values-to-columns-in-oracle/31464699#31464699">And the regex form used to parse the list <code>('[^,]+')</code> does not work with nulls and should be avoided</a>. Unfortunately, it's the most common regex you'll see given as an answer for parsing a list.

Try this instead, it should handle the larger lists with ease:

<pre><code>SQL&gt; 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 &lt;= REGEXP_COUNT(column1 , ',' )+1
 ) AS SYS.ODCINUMBERLIST
 )
 );

 ID MATCH_VALUE
---------- -------------
 10 abc
 10 kdm
 20 xyz
 20 lop
 20 nkk

SQL&gt;
</code></pre>

Basically this makes a table, one row per line, of the number of elements in the list (containing the element number) and joins it with the main table. 
Uncomment the COLUMN_VALUE value line to see the numbering of the elements.
It assumes the data will not contain a comma.

I have a table: Table_1 looks like the following:

<pre><code>id | column1
-----------------
10 | abc, kdm
20 | xyz, lop, nkk
</code></pre>

What I want is to convert the table looks like the following:

<pre><code>id | column1
-----------------
10 | abc
10 | kdm
20 | xyz 
20 | lop
20 | nkk
</code></pre>

To do this, I used a query like this:

<pre><code>select id, regexp_substr(column1,'[^,]+', 1, level) from Table_1 
connect by regexp_substr(column1, '[^,]+', 1, level) is not null;
</code></pre>

This query works fine as long as the number of comma separated values are less. But when it grows, it consumes more and more time to process.

One solution came to my mind was to create a separate table and then insert values by iterating through the values of Table_1.

Pseudo code as follows: 

<pre><code>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;
</code></pre>

But since this also consumes much time as the comma separated values grows, Is there any other optimal way to do this(preferred not to use another table, but a temporary/virtual table is OK)?

I'm using Oracle SQL.

Thanks in advance.

oracle SQL: improve connect by clause

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

问oracle SQL:改进connect by子句
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问oracle SQL:改进connect by子句EN