“從序號中找到最小的未使用序號”演算法的改進

看到有網友juogkl的“從序號中找到最小的未使用序號”（http://space.itpub.net/24156512/viewspace-719687），其用迴圈來檢索未使用的序號，效率較低，現用ORACLE的OVER分析函式加以改進：

先看結果：

SQL> create table t_num as select rownum rn from dba_objects t;

Table created

SQL> select max(rn) from t_num;

   MAX(RN)

----------

     72278

SQL> delete t_num where rn=round(dbms_random.value()*72278);

2 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

2 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

2 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

2 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

2 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

2 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

3 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

2 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

2 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

2 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

2 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

3 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

2 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

2 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

3 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

2 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

2 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

1 row deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

2 rows deleted

SQL> delete t_num where rn=round(dbms_random.value()*72278);

0 rows deleted

SQL> delete t_num where rn=65084;

1 row deleted

SQL> delete t_num where rn between 65994 and 65994+10;

11 rows deleted

SQL> commit;

Commit complete

 

SQL> set timing on

SQL> select pkg_test.f_get_bh1('t_num','rn') from dual;

PKG_TEST.F_GET_BH1('T_NUM','RN')

--------------------------------

                             361

Elapsed: 00:00:03.24

SQL> /

PKG_TEST.F_GET_BH1('T_NUM','RN')

--------------------------------

                             361

Elapsed: 00:00:00.58

SQL> /

PKG_TEST.F_GET_BH1('T_NUM','RN')

--------------------------------

                             361

Elapsed: 00:00:00.58

SQL> select pkg_test.f_get_bh2('t_num','rn') from dual;

PKG_TEST.F_GET_BH2('T_NUM','RN')

--------------------------------

                             361

Elapsed: 00:00:00.03

SQL> /

PKG_TEST.F_GET_BH2('T_NUM','RN')

--------------------------------

                             361

Elapsed: 00:00:00.01

SQL> /

PKG_TEST.F_GET_BH2('T_NUM','RN')

--------------------------------

                             361

Elapsed: 00:00:00.01

SQL> /

PKG_TEST.F_GET_BH2('T_NUM','RN')

--------------------------------

                             361

Elapsed: 00:00:00.01

最後看一下兩個演算法：

create or replace package body pkg_test is

  function f_get_bh1(v_tablename varchar2, v_col varchar2) return int as

    nb_flag1  number(10);

    nb_flag2  number(10);

    nb_flag3  number(10);

    l_bh      number(10);

    bh_return number(10);

  begin

    execute immediate 'select min(' || v_col || ') from ' || v_tablename

      into nb_flag1;

    nb_flag2 := 100000000;

    l_bh     := nb_flag1;

    for l_bh in nb_flag1 .. nb_flag2 loop

      execute immediate 'select count(1) from ' || v_tablename || ' where ' ||

                        v_col || '=' || l_bh

        into nb_flag3;

      exit when nb_flag3 = 0;

      bh_return := l_bh + 1;

    end loop;

    return bh_return;

  end f_get_bh1;

 

  function f_get_bh2(v_tablename varchar2, v_col varchar2) return int as

    nb_flag1 number(10);

    nb_flag2 number(10);

  begin

    execute immediate 'select max(' || v_col || ') from ' || v_tablename

      into nb_flag1;

    execute immediate 'select ' || v_col || ' from (select ' || v_col ||

                      ',last_value(' || v_col || ') over(order by ' ||

                      v_col ||

                      ' ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) lv from ' ||

                      v_tablename || ') where lv-' || v_col ||

                      '>1 and rownum=1'

      into nb_flag2;

    return nb_flag2 + 1;

  end f_get_bh2;

 

begin

  null;

end pkg_test;