|
最终结论,构造较大序列时,例如同样是构造出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
文章来自微信公众号：数据和云 （编辑：宣城站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
