Ở bài viết Oracle so sánh thời gian ngày tháng – Oracle compare date time mình có chia sẻ về cách ta có thể so sánh thời gian trong Oracle. Nhưng trong quá trình triển khai thực thế, việc turning hệ thống thì thấy việc sử dụng các câu lệnh này sẽ có vấn đề về index trên cột này. Ta cùng phân tích và tối ưu câu lệnh SQL cho vấn đề này nhé.
Đối với câu lệnh PL/SQL, cụ thể là Oracle thì hàm to_char(…) gây mất index đối với cột dữ liệu kiểu DATETIME. Tương tự với việc sử dụng toán tử LIKE cho kiểu dữ liệu VARCHAR hoặc NVARCHAR. Nhưng đổi lại việc sử dụng hàm TRUNC lại không làm mất index kiểu dữ liệu DATETIME.
Để tối ưu câu lệnh phải so sánh thời gian ta tránh việc sử dụng hàm to_char(…) để tránh mất index column này. Mặc dù ta nhìn câu lệnh SQL khi turning seckhông được tường minh và dễ hiểu lắm. Đúng thôi, ta buộc phải đánh đổi, cuộc sống mà.
Cách tối ưu khi so sánh ngày tháng
– So sánh ngày:
Kiểu cũ: Ta tiến hành convert dữ liệu dạng ngày sang kiểu string và tiến hành so sánh:
ví dụ:
Cần lấy ngày tạo lớn hơn hoặc bằng ngày 06/04/2018
AND TO_CHAR(datecreate, ‘YYYYMMDD’) >= ‘20180406’;
Turning: Ta có thể viết lại hàm này một chút để nó sẽ chạy nhanh hơn, tránh gây ảnh hưởng hiệu năng hệ thống.
Như câu lệnh trên ta có thể thấy ta đã đều ép kiểu dữ liệu về String để so sánh rồi, chính việc luôn phải convert dữ liệu từ kiểu DATETIME về kiểu String nên việc này sẽ ảnh hưởng đến chỉ số CPU. Ta sẽ để việc so sánh này về so sánh bằng kiểu DATETIME, nghĩa là ta sẽ chuyển đối tượng so sánh về kiểu thời gian, cụ thể câu lệnh trên được viết lại thành:
AND TRUNC(datecreate) >= TO_DATE(‘20180406’,'YYYYMMDD');
Như vậy, hàm TO_DATE(‘20180406’,'YYYYMMDD') chỉ phải sử dụng 1 lần duy nhất để chuyển kiểu String về kiểu DATETIME để so sánh. Hàm TRUNC sẽ không gây mất index mà đơn giản để chính xác ta để nó về thời gian đầu tiên của ngày, nghĩa là 0h 00p.
Hoặc trong khoảng ngày cũng tương tự:
Cách 1:AND TRUNC(datecreate) >= TO_DATE(‘20180406’,'YYYYMMDD')AND TRUNC(datecreate) < TO_DATE(‘20180411’,'YYYYMMDD')
– So sánh ngày chi tiết tới thời gian:
Kiểu cũ: Ta tiến hành convert dữ liệu dạng ngày sang kiểu string và tiến hành so sánh:
ví dụ:
Cần lấy ngày tạo lớn hơn hoặc bằng 7h tối ngày 06/04/2018:
AND TO_CHAR(laa.datcre, ‘YYYYMMDD HH24:MI:SS’) >= ‘20180406 19:00:00’
Turning: Việc này thì ta cũng làm tương tự như trên nhưng cần lưu ý xử lý về vấn đề thời gian giờ – phút – giây. Việc chuyển định dạng thời gian này hơi phức tạp, mình sẽ nghiên cứu và bổ sung thêm.
Tối ưu câu lệnh SQL không chỉ tập trung vào các cú pháp và hướng xử lý như việc tối ưu trên mà ta còn cần phải chú ý tới dữ liệu ta sử dụng. Đối với những dữ liệu khi cần tìm kiếm nhiều ta cũng cần quan tâm đến việc đánh Index và bố trí phân vùng cho chúng được hợp lý và tốt hơn.