如何高效能拆分一个字段为多行
我本意是将表中的一个字段拆分成多行，例如'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 from dual
)
select c1,level,replace(regexp_substr(c2,'[^,]+',1,level),',',' ') c2
from t1
connect by level
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段'eee,fff,ggg'，却产生了21笔资料。如果测试数据增多，或者需拆分的段
数量增多，那么connect by产生的数据将是海量的。
用此种方法实际处理生产库数据时，问题马上显现出来，仅17笔资料，最长拆分字段为8段，竟然产生了738万笔资料，尽管我使用
了distinct，依然慢的很。
解决方案：用join方式取代connect by方式
[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 from dual
)
select c1,
substr(t.ca,
instr(t.ca, ',', 1, d.lv) + 1,
instr(t.ca, ',', 1, d.lv + 1) -
(instr(t.ca, ',', 1, d.lv) + 1)) as d
from (select c1,
',' || c2 || ',' as ca,
length(c2 || ',') - nvl(length(replace(c2, ',')), 0) as cnt
from t1) t,
(select rownum lv from
(select max(length(c2 || ',') - nvl(length(replace(c2, ',')), 0)) mlc from t1)
connect by level
)d
where d.lv
order by c1
www.2cto.com
结论：
对于表资料只有一笔的时候，用connect by一般不会有什么问题。但如果表中资料是多笔，则connect by会产生海量的重复资料。
用join方式可解决此类问题。