generate sequence numbers - 用SQL语句生成序号

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/8/6
Subject: sql语句生成一组序号
To: xiewenxiewen at gmail.com


方法1. 建表

比如：
drop table t;
create table t(n number primary key) organization index;
exec for i in 1..10000 loop insert into t(n) values (i); end loop
commit;
select n from t where n between 15 and 22;

生成了15到22一组数字

这种方法效率低下，需要建表建数据，占用空间，浪费时间


方法2. pl/sql函数
create or replace
type numtab_type is table of number;
/
show err
create or replace function rowgen(p_a in number, p_b in number)
return numtab_type deterministic pipelined
as
begin
for i in p_a..p_b
loop
pipe row(i);
end loop;
return;
end rowgen;
/
show err
select column_value n from table(rowgen(15,22));

生成了15到22一组数据

8i不支持pipeline，函数得改成:
create or replace
function rowgen(p_a in number, p_b in number)
return numtab_type deterministic
as
v_numtab numtab_type;
begin
v_numtab := new numtab_type();
for i in p_a..p_b
loop
v_numtab.extend();
v_numtab(i-p_a+1) := i;
end loop;
return v_numtab;
end rowgen;
/
show err
select column_value from table(rowgen(15,22));




方法3. 用数据库中数据字典和rownum伪列
select rownum+15-1 n from all_objects where rownum <= 22-15+1;


如果想生成1000万条数据，一个all_objects不够用，则要交叉连接多个all_objects
如：

连接2个就够了
select rownum n from all_objects,all_objects where rownum <= 10000000;


方法4. 用connect by
select level+15-1 n from dual connect by level <= 22-15+1;


此方法速度很快：
set autot trace
select level+15-1 n from dual connect by level <= 22-15+1;

10g对dual表进行了优化，没有逻辑读和物理读
但是，8i下这么用有问题，不能用


方法5. group by cube
select rownum+15-1 n
from (select 1
from dual
group by cube(1,2,3))
where rownum<=22-15+1;

cube括号里有几个数字，子查询就会产生2^n条记录
select 1 from dual group by cube(0);
select 1 from dual group by cube(10,-1);



方法6. model
select integer_value n
from dual
where 0=1
model
dimension by ( 0 as key )
measures ( 0 as integer_value )
rules upsert ( integer_value[ for key from 15 to 22 increment 1 ] = cv(key) );


只有10g才能用


方法7. xmltable
select to_number(column_value) n from xmltable('15 to 23')


也是10g才能用


结论:
第四种connect by，又快又好，推荐使用


参考资料:
Oracle Row Generator Techniques
Generating an arbitrary amount of rows with Oracle SQL
Integer Series Generators
Can there be an infinite DUAL?



Xie wen（谢文）
Network & Operations,
Mobile Software Solutions (MDB) MOTOROLA Inc.
No. 108 Jian Guo Road, Chao Yang District, Beijing
100022 P. R. China
e-mail wenxie at motorola.com



-fin-