Làm thế nào tối ưu tốc độ tìm kiếm?

[xi`tin]

Chào mọi người
Tôi hiện đang gặp một vấn đề về tốc độ truy vấn câu query, mong được mọi người giúp đỡ.
- Tôi đang làm việc trên oracle 9. Câu query làm việc trên 3 bảng với lượng dữ liệu mỗi bảng khoảng 300.000 record.
- Trung bình 1 thời điểm có khoảng 150 người dùng (Tối đa là 300 user)
Hiện tại câu query của tôi thường cho kết quả trong thời gian từ 1-3 phút. Lúc ít người thì có thể là khoảng 20 giây. Tuy nhiên là hệ thống giải đáp thông tin qua điện thoại nên yêu cầu phải cho kết quả trong thời gian thấp mà với thời gian hiện tại hệ thống chưa đáp ứng được yêu cầu.
Vậy bác nào có cách giải quyết tốt hơn xin được giúp đỡ.

[KingsCat]

Bạn có thể post cấu trúc 3 bảng và câu lệnh SQL lên đây đc k?

[minhpn]

Dùng Index đi bạn ơi

[xi`tin]

Tôi mô hình chung 3 bảng như sau:
Bảng A: Member(mid, sid, pid, phone, address1, name1, comment1,...)
Bảng B: Payment(pid, address2, name2, comment2)
Bảng C: Street(sid, name)
(Thực tế thì tôi JOIN với nhiều bảng khác nữa để lấy thông tin)

Theo yêu cầu là hệ thống phải cho phép tìm kiếm theo tên khách hàng (gồm tên người đăng ký (name1) và tên người sử dụng (name2)); địa chỉ, số điện thoại, ghi chú,...
Câu query của tôi như sau, xét trường hợp tổng quát là người dùng tìm chuỗi bất kỳ (không biết thông tin đó là tên hay địa chỉ khách hàng nên sẽ quét hết các trường thông tin cần thiết):

SELECT A.name1, B.name2, A.address1, B.address2, C.name, ...
FROM A
LEFT JOIN B ON B.pid = A.pid
LEFT JOIN C ON C.sid = A.sid
WHERE lower(phone) LIKE '%PHONE%' OR lower(address1) LIKE '%ADDRESS%' OR lower(address2) LIKE '%ADDRESS%' OR lower(name1) LIKE '%NAME%' OR lower(name2) LIKE '%NAME%' OR lower(comment1) LIKE '%COMMENT%' OR lower(comment2) LIKE '%COMMENT%'

- Tôi sử dụng hàm lower() vì oracle phân biệt hoa thường.
- ADDRESS, NAME, COMMENT, PHONE là các tham số truyền vào.
- Tôi cũng đã index các trường trên để tìm kiếm cho nhanh rồi.

Tuy nhiên kết quả không như mong muốn. Rất mong được sự chỉ giáo của các bác.

Xin cảm ơn đã quan tâm!

[kendo]

LIKE = Full scan table.

[xi`tin]

Đúng là LIKE = full scan nhưng trong trường hợp này không thể khác hơn được vì thông tin tìm kiếm là không biết chính xác

[dbp]

Một số quy tắc để tăng tốc độ query:

1/ Các câu query không dùng index thì nhìn chung là luôn chậm hơn so với câu query tương đương có dùng index, ngoại trừ một số trường hợp đặc biệt (ví dụ như các table đều nhỏ, được Oracle cho hết lên cache rồi)

2/ Chỉ khi nào điều kiện "where" thực hiện trên một column được index thì câu query mới (có thể) sử dụng index và mới có tốc độ nhanh.
Ví dụ bạn có index trên column Last_Name và First_Name nhưng câu query chỉ là "... where Date_Of_Birth = 'xx-***-***x' ..." thì index đó sẽ không được sử dụng (tất nhiên)
Vì vậy nhìn chung cần phải tạo index trên tất cả các column nào mà các bạn dựa vào đó để tìm kiếm.

3/ Cho dù điều kiện 2 được thỏa mãn thì chưa chắc Oracle đã dựa vào index để tìm kiếm. Dưới đây là một số trường hợp hay gặp (không phải là tất cả) mà Oracle sẽ không sử dụng index cho dù bạn đã tạo index trên column:
3a. Dùng like '%abc' trên character columns . Ví dụ:
... WHERE First_Name like '%HUONG'
Chỉ khi nào chuỗi tìm kiếm không bắt đầu bằng '%' thì index mới được sử dụng. VD: ..WHERE First_Name like 'TU%'

3b. Bạn dùng function trên column trong điều kiện tìm kiếm, trong khi trước đó bạn không tạo một function based index cho nó.
Ví dụ như câu query của Xì tin có dùng function uppper():
... WHERE upper(First_Name) LIKE 'TU%'
sẽ chỉ dùng index nếu trước đó bạn đã tạo một function-based index:
Create index <INDEXNAME> on <TABLENAME>(upper(First_Name))

3c. Khi bạn kết hợp 2 điều kiện bằng toán tử OR thì cả hai điều kiện đó sẽ không khiến cho Oracle dùng index.
Ví dụ bạn có index trên cả First_Name và Last_NAme nhưng câu lệnh sau sẽ vẫn không dùng index:
Select ...
From ..
Where (First_Name like 'TU%' Or Last_Name = 'NGUYEN')
Tuy nhiên Oracle vẫn có thể dùng index trong các điều kiện khác của mệnh đề WHERE

4/ Kể từ Oracle 8i, Oracle có 2 cơ chế tối ưu hóa câu query: rule-based (cơ chế cũ, giống như các version trước) và cost-based (mới, và là default). Theo cơ chế rule-based, Oracle sẽ tự động dùng index nếu thỏa mãn các điều kiện trên. Theo cơ chế cost-based, Oracle còn căn cứ vào một loạt thứ khác, ví dụ kích thước table, table có đang được cache hay không, v.v... để thử các execution plan khác nhau, với mỗi execution plan Oracle tính xem "chi phí" (cost) của nó là bao nhiêu, qua đó chọn plan nào có cost thấp nhất.
Để có Oracle chọn chính xác (chọn được execution plan nhanh nhất), cần phải thường xuyên tính toán lại các thông số thống kê (recalculate statistics) cho các table và index. Khi kích thước table và index tăng lên nhiều mà các con số thống kê vẫn cũ thì việc tính cost của query sẽ không chính xác, và do đó việc chọn execution plan cũng không chính xác nữa. Việc recalculate statistics là công việc của người DBA.

[haithanhqb]

trong trường hợp này theo tôi nghĩ:
Thứ nhất: Câu lệnh sql của bạn để động: thông tin tìm kiếm nào có giá trị thì mới đưa vào
Thứ 2: Có index cho các fields: phone, name1, cmnd, ...

theo tôi thấy: yêu cầu của người dùng rất nhiều: nhưng đến 60-80% người dùng sử dụng các thông tin tìm kiếm như name, số phone, adress

Thân chào