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博客 ” ，链接：https://blog.itpub.net/81227/viewspace-720022/ ，如需转载，请注明出处，否则将追究法律责任。
