Oracle数据库9i、10g里列转行各种方法比较

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle数据库9i、10g里列转行各种方法比较，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2126字，纯文字阅读大概需要4分钟。

内容图文

把字符串1,2,3,4,5，以逗号分隔，输出为行，也就是12345随手写了一个,Oracle10G以上有表如下SQLselect*fromt;IDNAM..

把字符串 '1,2,3,4,5'，以逗号分隔，输出为行，也就是

1
2
3
4
5

随手写了一个, Oracle 10G 以上
有表如下

ID NAME
---------- --------
1 0
1 1
1 5
1 2
1 8
1 10
2 9
2 7
2 8
3 你好
3 他好
3 大家好

12 rows selected.

然后客户那反馈说数据库说9i的，报错说connect by不能使用子查询。
于是再稍做修改，在确定逗号不会超过99个的前提下，直接写个常量100，可以支持9i

后来想想这样也不好，于是建议建立一张特定的IOT表，保存10000个数字，一般够用了。
建立一张IOT,共10000行，同样支持9i

create table tnumber(ele,constraint pk_tnumber primary key(ele)) organization index as

select rownum id from dual connect by rownum<=10000;

然后不再需要常量100，即保证准确，又保证较好的性能

ID NAME
---------- --------
1 0
1 1
1 5
1 2
1 8
1 10
2 9
2 7
2 8
3 你好
3 他好
3 大家好

12 rows selected.

下面测试一下性能：转换50万行试试，美国服务器，测试环境：DELL D630 用了4年半的旧笔记本，磁盘都是碎片
SQL> insert into t select rownum+4,'1,2,3,4' from dual connect by rownum<=500000;

500000 rows created.

SQL> commit;

Commit complete.

set timi on
SQL> set timi on
SQL> create table t500 nologging as
with vmaxnum as (
select ele
from tnumber
where ele<=(select max(length(name)-length(replace(name,',',null)))+1 from t))
select id,
decode(pos,0,substr(name,lagpos+1),substr(name,lagpos+1,pos-lagpos-1)) name
from (select id,name,ele,pos,nvl(lag(pos) over (partition by id order by ele),0) lagpos
from (select id,name,ele,
instr(name,',',1,ele) pos
from (select /*+ all_rows no_merge(v2) use_merge(t,v2) */
t.id,t.name,v2.ele
from t,vmaxnum v2
where ele<=length(name)-length(replace(name,',',null))+1)))
order by id,ele;

Table created.

Elapsed: 00:00:16.48

耗时16秒，香港服务器，含建表的写盘时间

SQL> select count(*) from t500;

COUNT(*)
----------
2000012

当然还有别的写法，比如简单的可以这样

或者10G以上用正则表达式也可以实现
这个方法可能不是最高效的，但也还可以。
这个SQL的性能和字符串含有的逗号的个数有关，逗号越多，也就是分隔项越多，性能越差。

本文出自 “专注J2EE系列规范下的..” 博客，请务必保留此出处

，美国空间

内容总结

以上是互联网集市为您收集整理的Oracle数据库9i、10g里列转行各种方法比较全部内容，希望文章能够帮你解决Oracle数据库9i、10g里列转行各种方法比较所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---