Tìm các dải số liên tiếp

Lãnh đạo chỗ tôi làm trước đây có yêu cầu chúng tôi tìm kiếm các dải số điện thoại liền nhau để cung cấp cho các khách hàng doanh nghiệp. Chúng tôi có một bảng dữ liệu chứa tất cả các số điện thoại chưa sử dụng. Dữ liệu khá lớn, cỡ chục triệu số. Nếu đơn thuần chỉ thống kê ra thôi thì không có gì, vấn đề là các sếp lại muốn tạo thành một chức năng truy vấn trên hệ thống quản lý số điện thoại và cung cấp cho các đại lý của công ty, cỡ khoảng hơn 100 đại lý được phép làm việc này. Và đương nhiên như vậy thì số lượng truy vấn sẽ rất nhiều, tốc độ truy vấn nếu chậm sẽ khiến cho cả hệ thống quá tải, thậm chí “sập” luôn.

Một số bạn "hăm hở" viết function PL/SQL, quét toàn bộ bảng với số điện thoại được sắp xếp và kiểm tra có liền nhau không, sau đó trả về kết quả. Thời gian xử lý lên tới gần 10 phút, mà đấy là lúc tải thấp. Không đạt yêu cầu.

Tôi nhận thấy rằng chúng ta hoàn toàn có thể thực hiện hiệu quả việc này bằng câu lệnh SQL. Sau một hồi thử nghiệm nhiều mẫu lệnh khác nhau, rồi tôi cũng tìm ra được một câu lệnh khá hiệu quả và ngắn gọn.

Giả sử chúng ta có bảng quản lý số điện thoại chưa sử dụng như sau:

create table number_store (   nc varchar2(5),   isdn varchar2(16),   constraint number_store_pk primary key (nc, isdn) ); /

Ở đây, NC là Network Code (mã mạng), ISDN là số máy. Ví dụ số máy của bạn là 913235623, thì NC là 91, ISDN là 3235623. Cặp NC, ISDN sẽ tạo thành khóa chính.

Sử dụng đoạn code sau để insert dữ liệu mẫu vào bảng NUMBER_STORE.

exec dbms_errlog.create_error_log('NUMBER_STORE'); / begin   for i in 1..50 loop     insert into number_store     select       case ceil(dbms_random.value * 2)         when 1 then '91'         when 2 then '94'       end nc,       round(dbms_random.value(2000000, 9999999)) isdn     from dual     connect by level <= 100000     log errors reject limit unlimited;     commit;   end loop; end; / exec dbms_stats.gather_table_stats(user, 'NUMBER_STORE', cascade=>true ); /

Ta tạo ngẫu nhiên số điện thoại rồi insert vào bảng NUMBER_STORE. Tuy nhiên bảng này lại có khóa chính trên cặp NC và ISDN, quá trình sinh ra ngẫu nhiên nhiều khả năng lại tạo ra số đã được insert rồi, khi đó lệnh insert bị lỗi. Để bỏ qua các trường hợp này, tôi tạo bảng error log cho bảng NUMBER_STORE, sau đó dùng chỉ thị “log errors reject limit unlimited” để đưa các bản ghi lỗi vào bảng errror log.

Vòng lặp 50 lần, mỗi lần 100K số ngẫu nhiên sinh ra, như vậy ta sẽ có gần 5 triệu số (phải trừ đi những số điện thoại bị reject nhưng không nhiều). Thực hiện thêm lệnh gather_table_stats để cập nhập thông tin statictics cho bảng NUMBER_STORE nhằm tăng hiệu năng truy vấn.

Đến đây, quá trình tạo bảng dữ liệu mẫu đã xong, ta bắt đầu thực hiện truy vấn tìm các dải số liền nhau. Chạy câu lệnh SQL sau:

with x as (   select nc, isdn, isdn - row_number() over (partition by nc order by isdn) isdn2   from number_store) select   nc, min(isdn) first_isdn, max(isdn) last_isdn, count(isdn) isdn_count from x group by nc, isdn2 having count(isdn) > 1 order by isdn_count desc;

Điểm quan trọng nhất của câu lệnh trên là “isdn - row_number() over (partition by nc order by isdn) isdn2”. Các bạn sẽ nhận thấy là với một NC cụ thể, các ISDN liền nhau sẽ có ISDN2 giống nhau. Ta chỉ cần thực hiện group by nc, isdn2 và tính các giá trị min, max, count. Còn điều kiện “having count(isdn) > 1” để yêu cầu dải số phải có từ 2 số trở lên.

Hãy chạy thử và cảm nhận! Sẽ rất nhanh đấy.