Tăng tốc database phần 15.1 Indexing NULL trong Oracle

Nếu các bạn chưa xem bài trước vui lòng xem tại đây. Trong Oracle nếu tất cả các cột trong được đánh index là NULL thì bản ghi đó sẽ không có trong index. Điều đó nghĩa là tất cả các index là partial index giống như điều kiện sau.

CREATE INDEX idx
          ON tbl (A, B, C, ...)
       WHERE A IS NOT NULL
          OR B IS NOT NULL
          OR C IS NOT NULL
             ...

Giả sử chúng ta có một index EMP_DOB trên cột date_of_birth trong bảng employee. Khi chúng ta insert dữ liệu như sau:

INSERT INTO employees ( subsidiary_id, employee_id
                      , first_name   , last_name
                      , phone_number)
               VALUES ( ?, ?, ?, ?, ? )

vì không có giá trị của cột date_of_birth nên giá trị cột sẽ là NULL, nên bản ghi này sẽ không được thêm vào index EMP_DOB. Kết quả là câu truy vấn sau sẽ không được hỗ trợ bởi index

SELECT first_name, last_name
  FROM employees
 WHERE date_of_birth IS NULL

Tuy nhiên nếu chúng ta tạo một index có hai trường như sau:

CREATE INDEX demo_null
          ON employees (subsidiary_id, date_of_birth)

Trong trường hợp như trên bản ghi được thêm vào index vì SUBSIDIARY_ID là khác NULL. Trường hợp này truy vấn sau có thể sử dụng index để tăng hiệu năng khi tìm kiếm một bản ghi trong một subsidiary mà có date_of_birth là NULL

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = ?
   AND date_of_birth IS NULL

Hãy nhớ rằng index hoạt động trong toàn bộ điều kiện where với kiểu INDEX RANGE SCAN. Tuy nhiên trong câu trên thì cần chỉ rõ bản ghi thuộc subsidiary nào? Nếu ta muốn tìm toàn bộ các bản ghi có giá trị date_of_birth là NULL thì làm thế nào?

Lúc đó chúng ta để cột date_of_birth là trường bên trái nhất của index, và add thêm một trường not null ở bên phải, ví dụ trường subsidiary_id. Mặc dù trường này không có trong câu truy vấn, nhưng nó đảm bảo bản ghi có date_of_birth là NULL vẫn được thêm vào index. Giúp câu truy vấn dưới đây được hỗ trợ bởi index.

SELECT first_name, last_name
  FROM employees
 WHERE date_of_birth IS NULL

Có một cách khác để index không thể NULL là sử dụng một constant expression không thể bị NULL. Nó đảm bảo rằng tất cả các bản ghi được thêm vào index dù cho date_of_birth bị NULL đi chăng nữa.

DROP   INDEX emp_dob
CREATE INDEX emp_dob ON employees (date_of_birth, '1')

Về mặt kỹ thuật, đó là function-based index. Điều này cho phép bạn index trên các giá trị NULL trong Oracle.

Các bạn có thể Join group này để cùng xây dựng cộng đồng lập trình viên level quốc tế nhé

Facebook

Database index

Mục lục