Tăng tốc database phần 15.2 Indexing NULL trong Oracle - NOT NULL Constraints

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é

Facebook