Oracle数据库9i、10g里列转行各种方法比较
Jun 07, 2016 pm 05:41 PM把字符串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
然后不再需要常量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系列规范下的..” 博客,请务必保留此出处
,美国空间
Artikel Panas

Alat panas Tag

Artikel Panas

Tag artikel panas

Notepad++7.3.1
Editor kod yang mudah digunakan dan percuma

SublimeText3 versi Cina
Versi Cina, sangat mudah digunakan

Hantar Studio 13.0.1
Persekitaran pembangunan bersepadu PHP yang berkuasa

Dreamweaver CS6
Alat pembangunan web visual

SublimeText3 versi Mac
Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

Topik panas

Berfungsi untuk mengira bilangan hari antara dua tarikh dalam oracle

Berapa lama log pangkalan data Oracle akan disimpan?

Urutan langkah permulaan pangkalan data oracle ialah

Keperluan konfigurasi perkakasan pelayan pangkalan data Oracle

Bagaimana untuk menentukan sama ada dua rentetan terkandung dalam oracle

Bagaimana untuk melihat bilangan kemunculan watak tertentu dalam Oracle

Berapa banyak memori yang diperlukan oleh oracle?
