如何高效能拆分一个字段为多行

如何高效能拆分一个字段为多行 我本意是将表中的一个字段拆分成多行，例如aaa,bbb拆分为 aaa bbb 现在测试如下： [sql] with t1 as ( select 3 c1,eee,fff,ggg c2 from dual UNION ALL select 2 c1,ccc,ddd c2 from dual UNION ALL SELECT 1 c1,aaa,bbb c2 F

如何高效能拆分一个字段为多行

我本意是将表中的一个字段拆分成多行，例如&＃39;aaa,bbb&＃39;拆分为

&＃39;aaa&＃39;

&＃39;bbb&＃39;

现在测试如下：

[sql]

with t1 as

(

select 3 c1,&＃39;eee,fff,ggg&＃39; c2 from dual UNION ALL

select 2 c1,&＃39;ccc,ddd&＃39; c2 from dual UNION ALL

SELECT 1 c1,&＃39;aaa,bbb&＃39; c2 FROM dual

)

select c1,LEVEL,replace(regexp_substr(c2,&＃39;[^,]+&＃39;,1,level),&＃39;,&＃39;,&＃39; &＃39;) c2

from t1

connect BY level<=length(c2)-length(replace(c2,&＃39;,&＃39;,&＃39;&＃39;))+1

order by c1,level

www.2cto.com

返回结果如下：

[sql]

C1 LEVEL C2

1 1 aaa

1 2 bbb

1 2 bbb

1 2 bbb

2 1 ccc

2 2 ddd

2 2 ddd

2 2 ddd

3 1 eee

3 2 fff

3 2 fff

3 2 fff

3 3 ggg

3 3 ggg

3 3 ggg

3 3 ggg

3 3 ggg

3 3 ggg

3 3 ggg

3 3 ggg

3 3 ggg

www.2cto.com

--======================================================

看起来connect by之后产生了大量重复数据，于是加入distinct后取得正确数据。

反思：

我构造的测试数据仅仅只有三行，最长的拆分资料仅3段&＃39;eee,fff,ggg&＃39;，却产生了21笔资料。如果测试数据增多，或者需拆分的段

数量增多，那么connect by产生的数据将是海量的。

用此种方法实际处理生产库数据时，问题马上显现出来，仅17笔资料，最长拆分字段为8段，竟然产生了738万笔资料，尽管我使用

了distinct，依然慢的很。

解决方案：用Join方式取代connect by方式

[sql]

with t1 as

(

select 3 c1,&＃39;eee,fff,ggg&＃39; c2 from dual UNION ALL

select 2 c1,&＃39;ccc,ddd&＃39; c2 from dual UNION ALL

SELECT 1 c1,&＃39;aaa,bbb&＃39; c2 FROM dual

)

SELECT c1,

substr(t.ca,

instr(t.ca, &＃39;,&＃39;, 1, d.lv) + 1,

instr(t.ca, &＃39;,&＃39;, 1, d.lv + 1) -

(instr(t.ca, &＃39;,&＃39;, 1, d.lv) + 1)) AS d

FROM (SELECT c1,

&＃39;,&＃39; || c2 || &＃39;,&＃39; AS ca,

length(c2 || &＃39;,&＃39;) - nvl(length(REPLACE(c2, &＃39;,&＃39;)), 0) AS cnt

FROM t1) t,

(select rownum lv from

(select max(length(c2 || &＃39;,&＃39;) - nvl(length(REPLACE(c2, &＃39;,&＃39;)), 0)) mlc from t1)

connect by level<=mlc

)d

WHERE d.lv <= t.cnt

ORDER BY c1

www.2cto.com

结论：

对于表资料只有一笔的时候，用connect by一般不会有什么问题。但如果表中资料是多笔，则connect by会产生海量的重复资料。

用join方式可解决此类问题。