Để tối ưu hóa index trong Oracle, cần tập trung vào việc thiết kế và quản lý index sao cho cân bằng giữa hiệu suất truy vấn và chi phí duy trì. 

Dưới đây là các bước chi tiết và ví dụ minh họa:
1. Chọn đúng loại index:
+ B-tree Index: Phù hợp với cột có tính chọn lọc cao (ví dụ: ID, EMAIL).
Ví dụ: CREATE INDEX idx_employee_email ON employees(email);
+ Bitmap Index: Dùng cho cột có giá trị phân loại thấp (ví dụ: GENDER, STATUS).
Ví dụ: CREATE BITMAP INDEX idx_employee_gender ON employees(gender);
+ Function-Based Index: Khi truy vấn sử dụng hàm hoặc biểu thức (ví dụ: UPPER(name))
Ví dụ: CREATE INDEX idx_employee_upper_name ON employees(UPPER(last_name));
+ Composite Index: Khi truy vấn liên quan đến nhiều cột (ưu tiên cột có độ chọn lọc cao trước).
Ví dụ: CREATE INDEX idx_emp_dept_job ON employees(department_id, job_id);
2. Chọn cột phù hợp để đánh index
+ Cột tham gia trong điều kiện WHERE, JOIN, hoặc ORDER BY:
Ví dụ: Truy vấn thường xuyên tìm kiếm theo department_id và salary, có thể tạo index theo câu lệnh CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);
+ Tránh index trên cột ít được sử dụng hoặc có giá trị trùng lặp cao (ví dụ: boolean_flag).
3. Theo dõi và loại bỏ index không hiệu quả
+ Kiểm tra index không được sử dụng:
   + Bật chế độ theo dõi usage cho index: ALTER INDEX idx_old_index MONITORING USAGE;
   + Sau một thời gian, bạn kiểm tra bảng V$OBJECT_USAGE: SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME = 'IDX_OLD_INDEX';
+ Xóa index thừa: DROP INDEX idx_old_index;
4. Cân bằng giữa đọc và ghi
+ OLTP hệ thống: Hạn chế số lượng index để tránh chậm INSERT/UPDATE/DELETE.
+ Data Warehouse: Thêm nhiều index để tăng tốc truy vấn phức tạp.
5. Tránh Fragmentation
Kiểm tra và rebuild index bị phân mảnh: ALTER INDEX idx_fragmented REBUILD;
6. Phân vùng (Partitioning) và Local Index
Index phân vùng cho bảng lớn:
Ví dụ:
    CREATE TABLE sales (
      sale_id NUMBER,
      sale_date DATE
    ) PARTITION BY RANGE (sale_date) (...);
    
    CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;
7. Tận dụng Invisible Index
+ Kiểm tra xem một index có thực sự được dùng hay không.
+ Nếu không, thử ẩn nó (invisible) để đánh giá hiệu năng hệ thống khi thiếu index đó.
+ Nếu mọi thứ vẫn ổn → Có thể xóa bỏ.
Dưới đây là một đoạn script đơn giản:
+ Theo dõi MONITORING USAGE
+ Ẩn index không dùng (invisible)
+ Ghi log lịch sử
+ Tự động DROP sau 30 ngày invisible nếu index không dùng
+ Gửi email báo cáo định kỳ với UTL_SMTP
+ Scheduler chạy hàng tuần

Có thể thử nghiệm việc sử dụng các invisible indexes trong Oracle bằng cách sử dụng hint /*+ USE_INVISIBLE_INDEXES */. Mặc định, Oracle không sử dụng invisible index trong các truy vấn thông thường, trừ khi chỉ định rõ ràng bằng hint này.
Giả sử có một bảng EMPLOYEES và đã tạo index IDX_EMP_DEPT trên cột DEPARTMENT_ID và sau đó đã ẩn nó: ALTER INDEX idx_emp_dept INVISIBLE;
Giờ hãy thử truy vấn có sử dụng index này:

SELECT /*+ USE_INVISIBLE_INDEXES */  *
FROM employees
WHERE department_id = 10;

Oracle sẽ xem xét sử dụng index nếu dùng hint USE_INVISIBLE_INDEXES.
8. Tối ưu composite index
Composite index là index bao gồm nhiều cột
Nguyên tắc tối ưu:
+ Đặt cột lọc nhiều nhất lên đầu
+ Phù hợp với truy vấn WHERE: Oracle chỉ sử dụng hiệu quả composite index khi truy vấn sử dụng các cột theo thứ tự từ trái sang phải đúng như khi đã tạo index.
Ví dụ: Giả sử tạo một composite index: CREATE INDEX idx_emp_dept_job ON employees(department_id, job_id);
Oracle sẽ sử dụng index này hiệu quả trong các truy vấn có dạng:
Truy vấn Oracle dùng index?
WHERE department_id = 10 AND job_id = 'IT_PROG' ✅ Có
WHERE department_id = 10 ✅ Có
WHERE job_id = 'IT_PROG' ❌ Không (vì thiếu department_id - cột đầu tiên)
WHERE job_id = 'IT_PROG' AND department_id = 10 ✅ Có (Oracle đủ thông minh sắp lại điều kiện)
WHERE department_id > 10 ✅ Có
WHERE department_id IS NULL ✅ Có
WHERE job_id = 'IT_PROG' AND department_id IS NULL ❌ Không (vì job_id đứng sau)

Muốn lọc theo job_id mà không có department_id → cần index riêng cho job_id.
Oracle chỉ dùng prefix của composite index, không "nhảy cóc":
Nếu cột đầu tiên không có trong WHERE, index không được dùng hoặc chỉ dùng phần sau nếu có Fast Full Scan.
+ Tạo covering index giúp truy vấn tránh phải truy cập bảng chính (table access) nếu tất cả dữ liệu cần thiết đã có sẵn trong index. 
   Khi nào cần thêm cột vào index: 
   + Được dùng trong SELECT: Để tránh phải đọc bảng để lấy dữ liệu.
   + Được dùng trong ORDER BY: Giúp Oracle sắp xếp luôn từ index, tránh sort.
   + Được dùng trong GROUP BY: Giúp Oracle group nhanh hơn.
+ Tránh tạo index quá rộng (có quá nhiều cột) là một nguyên tắc quan trọng trong tối ưu chỉ mục (index tuning) của Oracle, không thì sẽ ảnh hưởng database:
    + Tốn nhiều không gian: Index có nhiều cột chiếm dung lượng lớn trên disk.
    + Giảm hiệu năng ghi: Mỗi lần INSERT, UPDATE, DELETE phải cập nhật toàn bộ index → chậm.
    + Ít được dùng hơn: Nếu truy vấn không dùng đúng hết phần đầu của index → index bị bỏ qua (do quy tắc prefix).
    + Không cache tốt: Index lớn → ít khả năng nằm trong bộ nhớ (buffer cache) → I/O nhiều hơn.
+ Theo dõi bằng V$SQL_PLAN để:
   + Biết truy vấn nào đã hoặc đang sử dụng một index cụ thể.
   + Kiểm tra hiệu quả của invisible index hoặc index mới tạo.
   + Đánh giá xem có nên giữ hay xóa index.
   + Phân tích hiệu quả index mà không cần EXPLAIN PLAN thủ công.
9. Sử dụng Index Compression
Giảm kích thước index, tăng tốc đọc: CREATE INDEX idx_emp_dept_comp ON employees(department_id) COMPRESS;
10. Cập nhật thống kê (Statistics)
Gather statistics là một quá trình quan trọng trong Oracle Database, nhằm mục đích cập nhật thông tin thống kê về các đối tượng trong cơ sở dữ liệu, như bảng, chỉ mục, cột, và phân vùng. Thông tin này được Oracle Optimizer sử dụng để quyết định kế hoạch thực thi (execution plan) cho các truy vấn SQL, giúp tối ưu hóa hiệu suất truy vấn.
Tại sao cần phải gather statistics:
+ Optimizer dựa vào thống kê (stats) để quyết định cách thực thi truy vấn (ví dụ: Dùng full table scan hay index scan).
+ Nếu thống kê không được cập nhật, Oracle có thể chọn kế hoạch thực thi kém, gây hiệu suất truy vấn chậm.
+ Thống kê cung cấp thông tin về số lượng bản ghi (rows), tần suất các giá trị trong cột, hoặc tỷ lệ phân phối dữ liệu trong bảng. Điều này giúp Oracle biết được khi nào cần sử dụng index hay khi nào cần scan toàn bộ bảng.
+ Nếu Oracle không có thống kê cập nhật, nó có thể chọn index không tối ưu cho truy vấn, làm giảm hiệu suất.
+ Các truy vấn có JOIN, GROUP BY, ORDER BY, hoặc các điều kiện lọc phức tạp sẽ có thể lựa chọn phương thức tối ưu dựa trên thống kê đã thu thập.
+ Cập nhật định kỳ statistics đảm bảo rằng Oracle luôn có thông tin chính xác về dữ liệu, giúp giảm thiểu tình trạng cần phải can thiệp thủ công để tối ưu truy vấn.

Ví dụ cho bảng EMPLOYEES trong schema HR, có thể sử dụng câu lệnh sau để gather statistics: EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
Nếu muốn gather statistics cho toàn bộ schema, thay vì chỉ một bảng, có thể dùng: EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');
Để gather statistics cho toàn bộ database: EXEC DBMS_STATS.GATHER_DATABASE_STATS;
Nếu muốn tự động gather statistics cho tất cả các bảng trong cơ sở dữ liệu, có thể tạo một job để gather statistics cho toàn bộ database:
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'GATHER_STATS_ALL',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.GATHER_DATABASE_STATS; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=3; BYMINUTE=0; BYSECOND=0',  -- Chạy lúc 3:00 sáng mỗi ngày
    enabled         => TRUE
  );
END;