Thứ Ba, 24 tháng 6, 2025

Thử nghiệm quét full chậm hơn index

Dưới đây là phiên bản viết lại bài viết từ link bạn cung cấp, theo hướng phân tích sâu sắc hơn về kiến trúc index trong Oracle, lý giải vì sao truy vấn dùng Index có thể chạy chậm hơn Full Table Scan, kèm mô hình truy xuất dữ liệu, các khái niệm như selectivity, random I/O, và cost-based optimizer.

🔍 Khi Index chạy chậm hơn Full Table Scan – Một phân tích kiến trúc sâu

Trong thế giới quản trị cơ sở dữ liệu, chúng ta thường mặc định rằng “có index thì truy vấn sẽ nhanh”. Tuy nhiên, trong một số tình huống thực tế, bạn sẽ thấy truy vấn dùng index lại chậm hơn Full Table Scan (FTS). Đây không phải là bug, mà là hành vi được tối ưu có chủ đích của Oracle, dựa trên nguyên lý vận hành của Index, hệ thống lưu trữ, và Cost-Based Optimizer (CBO).

🧪 Tình huống thực nghiệm

Giả sử bạn có bảng USERS với hơn 2.4 triệu dòng, và một index đã được tạo trên cột REPUTATION:


CREATE INDEX IDX_REPUTATION ON USERS(REPUTATION);

Câu truy vấn đơn giản:


SELECT * FROM USERS WHERE REPUTATION = 1;

👉 Oracle chọn TABLE ACCESS FULL


Id  | Operation         | Name   | Rows   | Cost (%CPU)
--------------------------------------------------------
0   | SELECT STATEMENT  |        | 1.09M  | 13615 (1%)
1   | TABLE ACCESS FULL | USERS  | 1.09M  | 13615 (1%)

Nhưng khi ép dùng index:

SELECT /*+ INDEX(u IDX_REPUTATION) */ * FROM USERS u WHERE REPUTATION = 1;

Kế hoạch thực thi chuyển thành:

Id  | Operation                | Name             | Rows   | Cost (%CPU)
----------------------------------------------------------------------
0   | SELECT STATEMENT         |                  | 800K   | 518000 (10%)
1   | TABLE ACCESS BY ROWID    | USERS            | 800K   | ...
2   | INDEX RANGE SCAN         | IDX_REPUTATION   | 800K   | ...

Chi phí tăng gần 38 lần.

🔍 Giải thích sâu về nguyên lý kiến trúc Index

1. 🔗 Index là cấu trúc phụ, không chứa đủ dữ liệu

  • Index giống như mục lục sách: giúp bạn biết dòng nào có từ khóa.

  • Tuy nhiên, để đọc toàn bộ nội dung dòng đó, Oracle vẫn phải quay lại bảng chính bằng ROWID.

→ Nếu bạn SELECT *, Oracle phải:

  • Truy xuất index để lấy ROWID

  • Truy cập từng dòng thật trong bảng qua TABLE ACCESS BY ROWID

  • Lặp lại quá trình này hàng trăm nghìn lần

2. 🧮 Random I/O vs Sequential I/O

Truy vấn kiểuI/O PatternThời gian
FULL TABLE SCANSequential – đọc liên tục block theo thứ tự vật lýRất nhanh (disk/memory tối ưu hóa)
INDEX + ROWIDRandom – nhảy lung tung theo mỗi dòngRất chậm (gấp nhiều lần)

→ Dù index giúp lọc, nhưng chi phí "nhảy về bảng chính" theo từng ROWID là rất cao.

3. 📉 Selectivity thấp = Index không hiệu quả

Selectivity = Tỷ lệ dòng thỏa điều kiện / Tổng số dòng.

  • Nếu REPUTATION = 1 xuất hiện ở 800,000 dòng trên 2.4 triệu:
    → Selectivity = 33% ⇒ quá thấp ⇒ Oracle bỏ qua index

Chỉ khi selectivity < 5%, index mới thường có lợi.

4. ⚖️ Cost-Based Optimizer (CBO) cực kỳ thông minh

Oracle không “mù quáng” dùng index:

  • Nó dự đoán chi phí dựa vào:

    • Số dòng ước lượng (CARDINALITY)

    • IO ước tính (DISK_READS, BUFFER_GETS)

    • CPU ước lượng

  • Nếu thấy index tốn nhiều tài nguyên hơn, nó sẽ chọn Full Table Scan

🧠 Kết luận: Khi nào Index chậm hơn Full Table Scan?

Tình huốngGiải thích
WHERE lọc quá nhiều dòngIndex không đủ chọn lọc ⇒ random I/O quá nhiều
SELECT * trên bảng lớnOracle phải truy cập bảng chính theo từng dòng
Index không "covering"Không chứa đủ thông tin ⇒ cần thêm 1 lần truy cập bảng
Dữ liệu phân bố không đềuOptimizer thấy index không giúp ích nhiều

✅ Giải pháp thực tế

Giải phápChi tiết
Chỉ SELECT các cột nằm trong index (covering index)Tránh phải truy cập bảng chính
Tạo index dạng INCLUDE (>= Oracle 12.2)Dùng CREATE INDEX ... INCLUDE (...)
Tránh dùng SELECT *Chỉ lấy cột cần thiết
Phân tích lại statisticsĐảm bảo CBO ra quyết định đúng
Tạo Bitmap Index (nếu dữ liệu lặp lại nhiều)Áp dụng với DWH, không dùng với hệ thống OLTP

🎯 Thông điệp cuối cùng

Index không phải thần dược hay thuốc bổ gì. Muốn dùng đúng, cần:

  • Hiểu rõ cách thức hoạt động

  • Hiểu dữ liệu (phân bố, số lượng)

  • Hiểu hệ thống (loại truy vấn, OLTP hay DWH)

Oracle hoàn toàn khôn ngoain khi không dùng index nếu nó không hiệu quả.

=============================
Website không chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- 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
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin_1200
- Linux: https://bit.ly/linux_1200
=============================
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: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Đị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

=============================
AI, trí tuệ nhân tạo, artificial intelligence, machine learning, deep learning, LLM, ChatGPT, DeepSeek, Grok, 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, ms 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
By lúc
Labels: , ,

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master