ITPub博客

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

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

原创 Linux操作系统 作者:cow977 时间:2012-03-30 10:59:55 0 删除 编辑
在上一篇(http://space.itpub.net/81227/viewspace-719797)中,提出了改进算法,并给出了在“序号”字段没有索引的情况下的测试结果。在本篇中,将测试在“序号”字段有索引的情况下的测试结果:
 

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

Table created

SQL> create index idx_t_num on t_num(rn);

Index created

SQL> set timing on

SQL> select max(rn) from t_num;

   MAX(RN)

----------

     71300

Elapsed: 00:00:00.01

SQL> delete t_num where rn=70000;

1 row deleted.

Elapsed: 00:00:00.01

SQL> commit;

Commit complete.

Elapsed: 00:00:00.01

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

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

       *

ERROR at line 1:

ORA-01013: user requested cancel of current operation

ORA-06512: at "DEMO.PKG_TEST", line 27

 

Elapsed: 00:05:33.02

执行了5分多钟还没结果,人为中断了执行。

 

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

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

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

                           70000

Elapsed: 00:00:00.09

缺号靠近结尾处,换个靠前的试一下:

SQL> delete t_num where rn=700;

1 row deleted.

Elapsed: 00:00:00.01

SQL> commit;

Commit complete.

Elapsed: 00:00:00.01

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

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

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

                             700

Elapsed: 00:00:05.45

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

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

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

                             700

Elapsed: 00:00:00.02

二个方法差别很大,与没有索引的情况类似。

对表和索引进行分析:

SQL> exec dbms_stats.gather_table_stats('DEMO','T_NUM');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.72

SQL> exec dbms_stats.gather_index_stats('DEMO','IDX_T_NUM');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18

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

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

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

                             700

Elapsed: 00:00:00.10

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

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

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

                             700

Elapsed: 00:00:00.01

SQL> insert into t_num values (700);

1 row created.

Elapsed: 00:00:00.00

SQL> commit;

Commit complete.

Elapsed: 00:00:00.01

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

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

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

                           70000

Elapsed: 00:00:12.69

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

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

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

                           70000

Elapsed: 00:00:00.07

有了索引,性能有所提高,但差距依然很大。

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

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

注册时间:2011-03-02

  • 博文量
    689
  • 访问量
    740060