Như bài trước đã viết về index NULL trong Oracle và cách khắc phục. Tuy nhiên cần chú ý thêm một điều nữa là để index được IS NULL trong Oracle thì trong đó phải có ít nhất một Column không bao giờ NULL.
Điều đó nghĩa là chỉ bản ghi đó not NULL là không đủ. Mà Database cần chắc chắn 100% là nó không bao giờ NULL. Nếu không thì Oracle sẽ giả định rằng còn một vài bản ghi không có trong Index.
Chúng ta sẽ đi sâu hơn với ví dụ dưới đây. Câu lệnh sau sẽ chạy được với index nếu trường last_name có NOT NULL Constraint
DROP INDEX emp_dob
CREATE INDEX emp_dob_name ON employees (date_of_birth, last_name)
SELECT * FROM employees WHERE date_of_birth IS NULL
Giả sử chúng ta bỏ ràng buộc NOT NULL đi, lúc này index sẽ không được sử dụng khi truy vấn
ALTER TABLE employees MODIFY last_name NULL
SELECT * FROM employees WHERE date_of_birth IS NULL
Ngoài ràng buộc NOT NULL ra, Oracle cũng biết được nếu dùng const expression như bài trước cũng không thể nào bị NULL, nên có thể dùng index được.
Nếu chúng ta index trong một user-defined function. Thì nó không chắc chắc được điều kiện NOT NULL ví dụ
CREATE OR REPLACE FUNCTION blackbox(id IN NUMBER) RETURN NUMBER
DETERMINISTIC
IS BEGIN RETURN id;
END DROP INDEX emp_dob_name CREATE INDEX emp_dob_bb ON employees (date_of_birth, blackbox(employee_id)) SELECT * FROM employees WHERE date_of_birth IS NULL
Lúc này execution plan như sau
----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 477 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 477 |
----------------------------------------------------
Nhìn vào sẽ thấy database sử dung TABLE ACCESS FULL để tìm kiếm. Như ở bài về index trên function lúc trước. Database không biết gì về các một function hoạt động, lý do để tên là blackbox ở đây. Dù employee_id là NOT NULL, nhưng Database chỉ biết đó là một function trả về một số. Nó sẽ hiểu là có thể NULL ở đây. Vì vậy để cho chắc chắn nó phải quét toàn bộ bảng để không bỏ xót bản ghi nào. Index không được sử dụng ở đây.
Nếu bạn biết rằng function không thể nào NULL bạn có thể sửa câu lệnh sang như sau:
SELECT * FROM employees WHERE date_of_birth IS NULL AND blackbox(employee_id) IS NOT NULL -------------------------------------------------------------
|Id |Operation | Name | Rows | Cost |
-------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 3 |
|*2 | INDEX RANGE SCAN | EMP_DOB_BB | 1 | 2 |
Điều kiện thêm vào luôn luôn TRUE và nó không ảnh hưởng gì tới kết quả câu truy vấn, Tuy nhiên Oracle Database hiểu rằng bạn chỉ tìm những bản ghi NOT NULL trong hàm mới định nghĩa, vì vậy nó chỉ cần lấy dữ liệu trên index là đủ. Kết quả thì giống nhau, nhưng cách thực hiện thì khác nhau và hiệu năng thay đổi nhanh hơn hàng trăm lần.
Hiện tại chưa có cách nào để biết được một hàm luôn chả về giá trị NOT NULL, tuy nhiên có một giải pháp khác ở đây. Đó là dùng virtual column (since 11g) và thêm điều kiện ràng buộc NOT NULL vào column mới này.
ALTER TABLE employees ADD bb_expression GENERATED ALWAYS AS (blackbox(employee_id)) NOT NULL DROP INDEX emp_dob_bb CREATE INDEX emp_dob_bb ON employees (date_of_birth, bb_expression) SELECT * FROM employees WHERE date_of_birth IS NULL AND blackbox(employee_id) IS NOT NULL
Lúc này execution plan chạy như sau
-------------------------------------------------------------
|Id |Operation | Name | Rows | Cost |
-------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 3 |
|*2 | INDEX RANGE SCAN | EMP_DOB_BB | 1 | 2 |
-------------------------------------------------------------
Ngoài ra với một số internal function. Oracle biết được hàm return NULL nếu đầu vào là NULL ví dụ
DROP INDEX emp_dob_bb CREATE INDEX emp_dob_upname ON employees (date_of_birth, upper(last_name)) SELECT * FROM employees WHERE date_of_birth IS NULL
Execution plan như sau
----------------------------------------------------------
|Id |Operation | Name | Cost |
----------------------------------------------------------
| 0 |SELECT STATEMENT | | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 |
|*2 | INDEX RANGE SCAN | EMP_DOB_UPNAME | 2 |
Rõ ràng là dù index trên function, tuy nhiên đây là một internal function và Oracle chắc chắn được là kết quả của hàm NULL hay không dựa vào đầu vào NULL hay không, vì trường last_name đang có ràng buộc NOT NULL nên hàm được hiểu là NOT NULL nên index được sử dụng.
Giả sử nếu ta bỏ ràng buộc NOT NULL trên trường last_name.
ALTER TABLE employees MODIFY last_name NULL
SELECT * FROM employees WHERE date_of_birth IS NULL
Lúc này index không được sử dụng như sau đây
----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 477 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 477 |
----------------------------------------------------
Ngoài lề chút mình có một số Job list bạn nào muốn tìm một công việc mới có thể tham khảo link đây nhé
Các bạn có thể Join hai group này để cùng xây dựng cộng đồng lập trình viên level quốc tế nhé