ITPub博客

首页 > Linux操作系统 > Linux操作系统 > “从序号中找到最小的未使用序号”算法的改进

“从序号中找到最小的未使用序号”算法的改进

原创 Linux操作系统 作者:cow977 时间:2012-03-28 11:12:51 0 删除 编辑

看到有网友juogkl从序号中找到最小的未使用序号http://space.itpub.net/24156512/viewspace-719687),其用循环来检索未使用的序号,效率较低,现用ORACLEOVER分析函数加以改进:

先看结果:

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;

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/81227/viewspace-719797/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-02

  • 博文量
    629
  • 访问量
    683984