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

在上一篇（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

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