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

cow977發表於2012-03-30
在上一篇(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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章