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

cow977發表於2012-03-28

看到有網友juogkl的“從序號中找到最小的未使用序號”（http://space.itpub.net/24156512/viewspace-719687），其用迴圈來檢索未使用的序號，效率較低，現用ORACLE的OVER分析函式加以改進：

先看結果：

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章

vue 序號，翻頁時序號遞增
2024-10-08
Vue
vxe-table 樹形表格序號的使用
2024-12-02
PbootCMS 文章列表序號
2024-11-10
boot
leedcode Excel 表列序號
2024-03-05
Excel
序號格式化
2024-05-17
LeetCode 171[Excel表列序號]
2024-11-09
LeetCodeExcel
序號產生器合集
2024-03-17
Navicat序號產生器
2018-08-20
【效能技巧】使用DataReader[列序號]的方法讀取DataReader物件
2019-01-08
物件
leetcode：171. Excel表列序號
2020-10-27
LeetCodeExcel
Myeclipse 6.5 序號產生器
2020-04-06
Eclipse
每日一練（42）：Excel表序號
2022-04-20
Excel
[20210218]bash echo 建立順序號.txt
2021-02-18
Excel Sheet Column Number Excel表列序號
2018-11-09
Excel
Navicat Premiumx64 使用序號產生器啟用
2018-11-12
REM
SAP CRM One Order的事件序號產生器制
2020-02-11
事件
分享一個navicat序號產生器
2024-04-02
python:LEGB識別符號解析順序
2018-09-03
Python符號
win10如何執行序號產生器_win10怎麼執行序號產生器
2020-08-28
Win10
2024-05-04 如何為antd的table設定序號
2024-05-04
SAP CRM呼叫中心裡的事件序號產生器制
2020-03-10
事件
ul中有li，點選li，獲得對應的序號
2018-09-12
dataTable.js使用總結(包含序號生成，懸浮框使用等)
2018-08-01
JS
el-table翻頁序號不從1開始（已解決）
2020-04-05
【antdesign select】下拉選擇-帶選擇序號
2020-04-17
CSS ::marker 讓文字序號更有意思
2021-06-15
CSS
先序、中序、後序序列的二叉樹構造演算法
2018-04-19
二叉樹演算法
資料包表多種序號生成方式
2019-12-18
iOS開發 GCD訊號量實現AFNetworking的順序請求
2018-12-20
iOSGC
如何快速找到備份過最近、最大序號的歸檔日誌
2018-11-29
找到無序陣列中最小的k個數
2020-12-21
陣列
web 頁面如何實現不同分組資料的連續序號？
2020-05-22
Web
gson改變輸出欄位的順序
2021-09-09
利用訊號量實現執行緒順序執行
2024-06-14
執行緒
vos3000 2.1.1.5 安裝包及序號產生器
2019-04-04
S3
如何從整數陣列中找到最大和最小數
2019-01-24
陣列
後序+中序（前序+中序）重構樹，嚴格O(N)演算法
2021-01-03
演算法
終於搞懂Word中如何插入特殊符號了！帶圈序號、勾叉不在話下！
2019-04-19
符號
演算法 -- 實現二叉樹先序，中序和後序遍歷
2020-12-06
演算法二叉樹