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 |
----------------------------------------------------
Hy vọng hữu ích cho bạn
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
Tăng tốc database phần 15.2 Indexing NULL trong Oracle - NOT NULL Constraints, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
