Tìm kiếm số điện thoại

Hiện nay đa số các trang Web cho phép tìm kiếm và đăng ký số điện thoại mới chỉ hỗ trợ phương pháp tìm kiếm theo đầu hoặc đuôi của số máy cần tìm (ví dụ tìm số máy có đầu là 888 hoặc đuôi là 999). Lý do khá đơn giản, thông thường các kỹ sư xây dựng hệ thống sẽ tạo 01 chỉ mục (index) cho số máy điện thoại và 01 chỉ mục cho chuỗi đảo ngược (reverse) của số máy. Khi đó việc tìm kiếm sẽ được thực hiện trên những chỉ mục này tùy thuộc việc tìm theo đầu hay đuôi. Nếu tìm kiếm theo các điều kiện khác (ví dụ số máy có chứa số 666) thì hệ thống sẽ không sử dụng chỉ mục mà tiến hành quét toàn bộ (scan full) số máy điện thoại trên DB để tìm ra số máy phù hợp với điều kiện đưa ra. Điều đó khiến kết quả trả về chậm vì số lượng số máy điện thoại nhiều, trong trường có nhiều yêu cầu tìm kiếm sẽ dẫn đến “treo” hoặc “sập” hệ thống.

Sau đây, tôi giới thiệu một phương pháp khác cho phép chúng ta có thể sử dụng được rất nhiều các điều kiện tìm kiếm mà vẫn đảm bảo tốt thời gian truy vấn và hiệu năng hệ thống. Chúng ta sẽ sử dụng tính năng Oracle Text.

Đầu tiên, chúng ta kích hoạt tính năng Oracle Text trên Oracle DB. Ta có thể sử dụng công cụ Database Configuration Assistant để add tính năng này.

Sau đó chúng ta phải gán quyền sử dụng tính năng này cho user của chúng ta. Login vào DB với user sys hoặc system để gán quyền (user thử nghiệm của tôi là jars):

grant CTXAPP to jars; grant execute on CTXSYS.CTX_CLS to jars; grant execute on CTXSYS.CTX_DDL to jars; grant execute on CTXSYS.CTX_DOC to jars; grant execute on CTXSYS.CTX_OUTPUT to jars; grant execute on CTXSYS.CTX_QUERY to jars; grant execute on CTXSYS.CTX_REPORT to jars; grant execute on CTXSYS.CTX_THES to jars; grant execute on CTXSYS.CTX_ULEXER to jars;

Login lại với user vừa được gán quyền và bắt đầu tạo dữ liệu mẫu. Chúng ta sẽ tạo một bảng chứa các số điện thoại. Tôi tổ chức số máy thành 2 trường là NC (mã mạng) và ISDN (số máy). Ở đây tôi chỉ tạo mã mạng duy nhất là ‘91’.

drop table number_store; create table number_store (   nc varchar2(10),   isdn varchar2(16),   isdn_shadow varchar2(4000) ); alter table number_store add (       constraint number_store_pk primary key (nc, isdn));

Lưu ý trường ISDN_SHADOW. Trường này được tạo ra từ trường ISDN. Cụ thể với mỗi ISDN chúng ta sẽ phân rã thành tất cả các chuỗi số nhỏ hơn mà có thể xuất hiện trong số ISDN này, các số phân rã sẽ được phân cách bằng dấu trống (‘ ‘). Ví dụ với số ‘5385702’ sẽ được phân rã thành ‘5 53 538 5385 53857 538570 5385702 3 38 385 3857 38570 385702 8 85 857 8570 85702 57 570 5702 7 70 702 0 02 2’.

Lý do của việc này là do Oracle Text thực hiện tìm kiếm text dựa trên cách thức tìm kiếm theo từng từ, nếu ta không phân rã thì nó sẽ hiểu là 1 từ thôi và chỉ tìm được với điều kiện là toàn bộ số máy.

Ta viết một hàm phân rã và gán vào trigger của bảng NUMBER_STORE.

create or replace function string_to_words(p_string in varchar2) return varchar2 is   l_n      number;   l_piece  varchar2(255);   l_return varchar2(4000):= ' '; begin   l_n:= length(p_string);   for i in 1..l_n loop     for j in 1..l_n-i+1 loop       l_piece:= substr(p_string, i, j)||' ';       if (nvl(instr(l_return,  ' ' || l_piece), 0) = 0) then         l_return:= l_return || l_piece;       end if;     end loop;   end loop;   return l_return; end; / create or replace trigger number_store_trigger before insert or update on number_store for each row begin   :new.isdn_shadow:= string_to_words(:new.isdn); end; /

Đến đây ta bắt đầu insert dữ liệu mẫu. Ta tạo errorr log cho bảng NUMBER_STORE và sử dụng chỉ thị “log errors reject limit unlimited” trong câu lệnh insert để reject những bản ghi bị trùng khóa chính sang bảng error log. Với vòng lặp 10 lần, mỗi lần 100K bản ghi, như vậy ta có khoảng 1 triệu số (phải trừ đi những số máy bị reject).

exec dbms_errlog.create_error_log('NUMBER_STORE'); / begin   for i in 1..10 loop     insert into number_store (nc, isdn)       select '91', trunc(dbms_random.value(2000000, 9999999))        from dual         connect by level <= 100000         log errors reject limit unlimited;     commit;   end loop; end; /

Chúng ta tạo text index cho trường ISDN_SHADOW:

create index number_store_search_ix on number_store(isdn_shadow)   indextype is ctxsys.context   parameters ('DATASTORE CTXSYS.DEFAULT_DATASTORE MEMORY 50M');; /

Text index sẽ không tự cập nhập dữ liệu mới nên ta phải tiến hành chạy đồng bộ sau khi cập nhập dữ liệu mới. Quá trình đồng bộ rất nhanh do Oracle chỉ thực đồng bộ những dữ liệu bị thay đổi. Ta có thể tạo job đồng bộ định kỳ (1h/lần).

declare   x number; begin   sys.dbms_job.submit(     job       => x,     what      => 'ctx_ddl.sync_index(''number_store_search_ix'', ''50m'');',     next_date => sysdate+1/86400,     interval  => 'sysdate+1/24',     no_parse  => false   );   sys.dbms_output.put_line('job number is: ' || to_char(x)); commit; end; /

Phù!!! Đến đây mọi việc đã xong. Chúng ta bắt đầu thử tìm kiếm với các điều kiện khác nhau nhé, từ đơn giản đến phức tạp.

Tìm số có đầu là ‘4567’:

select * from number_store where contains(isdn_shadow, '{4567}') > 0 and (isdn like '4567%');

Tìm số có đuôi là ‘4567’:

select * from number_store where contains(isdn_shadow, '{4567}') > 0 and (isdn like '%4567');

Tìm số có chứa ‘4567’:

select * from number_store where contains(isdn_shadow, '{4567}') > 0 and (isdn like '%4567%');

Tìm số có chứa ‘88’ và có đuôi là ‘666:

select * from number_store where contains(isdn_shadow, '{88} and {666}') > 0 and (isdn like '%88%666');

Tìm số có chứa ‘47’, ‘6’ và ‘2’:

select * from number_store where contains(isdn_shadow, '{47} and {6} and {2}') > 0 and (isdn like '47%6%2%');

Các bạn có thể thử với các điều kiện khác nữa. Thời gian truy vấn thông thường đều dưới 0.5 giây :-)