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 :-)
