Oracle构造序列的方法分析对比(2)

最终结论,构造较大序列时,例如同样是构造出64M的序列,oracle在处理时,用表连接的方式明显占优.但考虑到书写的便利性,因此在构造较小序列的时候,比如不超过1K的序列,那么直接用connect by或xmltable的方式就好了.

附：newkid 回复方法,表示更灵活,有兴趣的同学可以尝试：

create or replace function generator (n pls_integer) return sys.odcinumberlist pipelined is
m pls_integer := trunc(n / 10);
r pls_integer := n – 10 * m;
begin
for i in 1 .. m loop
pipe row (null);
pipe row (null);
pipe row (null);
pipe row (null);
pipe row (null);
pipe row (null);
pipe row (null);
pipe row (null);
pipe row (null);
pipe row (null);
end loop;
for i in 1 .. r loop
pipe row (null);
end loop;
end;
/
alter function generator compile plsql_code_type = native;

SQL> select count(*) from table(generator(67108864));

COUNT(*)
———-
67108864

Elapsed: 00:00:06.68

SQL> with b as (select 1 r from dual union all select 2 from dual),
2??c as (select rownum r from b,
3??d as (select rownum r from c,
4??e as (select rownum r from d,b)
5??select count(*) from e;

COUNT(*)
———-
67108864

Elapsed: 00:00:06.32

文章来自微信公众号：数据和云

（编辑：ASP站长网）