几种构造序列的方法比较

newkid

如果不是10的整数幂, 时间是：

WITH
   E1(N) AS (
             SELECT 1 FROM DUAL CONNECT BY LEVEL<=10
            ),                          -- 1*10^1 or 10 rows
   E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
   E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*10^4 or 10,000 rows
   E8(N) AS (SELECT 1 FROM E4 a, E4 b)  -- 1*10^8 or 100,000,000 rows
SELECT COUNT(*) FROM (SELECT * FROM E8 WHERE ROWNUM<=POWER(2,26))
;

  COUNT(*)
----------
  67108864

Elapsed: 00:00:07.41
和一亿差不多。

lastwinner

〇〇 发表于 2013-6-14 18:39
打印出执行计划的实际成本

sql太多，贴出来太费页面，所以就不贴了

lastwinner

lugionline 发表于 2013-6-14 23:09
tally table?
http://www.sqlservercentral.com/articles/T-SQL/74118/

好久不见，呵呵
sqlserver不了解的说

lastwinner

newkid 发表于 2013-6-14 21:43
贴个PLSQL函数，以前从jonathan lewis博客看来的，只比你的方法慢一点点但是更灵活：

create or replace ...

他这个方法应该还可以更快，比如除以100
核心思路就是增加嵌套层数，减少最直接的循环次数，从而提高效率

newkid

改100后也差不多：

create or replace function generator (n pls_integer) return sys.odcinumberlist pipelined is
  m pls_integer := trunc(n / 100);
  r pls_integer := n - 100 * m;
begin
  for i in 1 .. m loop
  FOR j IN 1..10 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;
  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:07.10

dingjun123

不错！~

solomon_007

搂主，你好像还掉了两种 MODEL 和 RECURSIVE WITH
我自己收集的有5种，现在，加NEWKid的自定义，就是6种了

--1. by japan
select level n from dual connect by level <=10;


--2. by tomkyte
select rownum n from all_objects where rownum<=10;


--3.
select to_number(column_value) n from xmltable('1 to 10');


--4. model
select n
  from (select 1 v from dual)
model
   dimension by (v)
   measures (v n)
   rules
   (
     n[for v from 2 to 10 increment 1] = n[cv(v)-1] + 1               
   );

   
--5. recursive with
with t(n) as
(
  select 1 from dual
  union all
  select n + 1 from t where n < 10
)
select n from t

lastwinner

solomon_007 发表于 2013-6-15 22:10
搂主，你好像还掉了两种 MODEL 和 RECURSIVE WITH
我自己收集的有5种，现在，加NEWKid的自定义，就是6种了 ...

那俩我不会

solomon_007

lastwinner 发表于 2013-6-16 14:37
那俩我不会

你太谦虚了！为了便于记忆收藏，我特意给你打了个包！

create or replace package pkg_serial is
  procedure sp_japan;
  procedure sp_tomkyte;
  procedure sp_xml;
  procedure sp_model;
  procedure sp_with;
  procedure sp_table;
  
  function generator (n pls_integer)
  return sys.odcinumberlist
  pipelined;
end;
/

create or replace package body pkg_serial is

  --1.
  procedure sp_japan is
    cursor cur is
      select level n from dual connect by level <= 10;
  begin
    for rec in cur loop
      dbms_output.put_line(rec.n);
    end loop;
  end;

  --2.
  procedure sp_tomkyte is
    cursor cur is
      select rownum n from all_objects where rownum <= 10;
  begin
    for rec in cur loop
      dbms_output.put_line(rec.n);
    end loop;
  end;

  --3.
  procedure sp_xml is
    cursor cur is
      select to_number(column_value) n from xmltable('1 to 10');
  begin
    for rec in cur loop
      dbms_output.put_line(rec.n);
    end loop;
  end;

  --4.
  procedure sp_model is
    cursor cur is
      select n
        from (select 1 v from dual)
       model
             dimension by (v)
             measures (v n)
             rules
             (
                n[for v from 2 to 10 increment 1] = n[cv(v)-1] + 1               
             );
  begin
    for rec in cur loop
      dbms_output.put_line(rec.n);
    end loop;
  end;

  --5.
  procedure sp_with is
    cursor cur is
      with t(n) as
      (
         select 1 from dual
          union all
         select n + 1 from t where n < 10
       )
       select n from t;  
  begin
    for rec in cur loop
      dbms_output.put_line(rec.n);
    end loop;
  end;


  function generator (n pls_integer)
  return sys.odcinumberlist
  pipelined
  is
  begin
    for i in 1..n loop
      pipe row(i);
    end loop;
    return;
  end;

  --6.
  procedure sp_table is
    --alter function generator compile plsql_code_type = native;
    cursor cur is
      select column_value n from table(generator(10));
     
  begin
    for rec in cur loop
      dbms_output.put_line(rec.n);
    end loop;
  end;

end pkg_serial;
/

〇〇

既然写了存储过程，那么普通for循环也可