欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-05-23 23:38:13
...

把字符串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 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 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 order by id,ele;

    Table created.

    Elapsed: 00:00:16.48

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

    SQL> select count(*) from t500;

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

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

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

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

    ,美国空间