Đã đăng vào Chủ Nhật, 3:50 CH 5 phút đọc

Mục đích sử dụng nhiều loại index trên cùng một cột

1. Mở đầu

Việc áp dụng nhiều loại index trên cùng một cột có thể giúp tối ưu hóa hiệu suất cho các loại tác vụ khác nhau. Dù nghe có vẻ không quen, nhưng việc kết hợp các loại index như B-tree, bitmap, hay reverse key trên cùng một cột có thể mang lại lợi ích lớn cho việc xử lý dữ liệu, từ các truy vấn tìm kiếm nhanh chóng đến các phân tích dữ liệu phức tạp.

2. Phân tích

Có thể có nhiều loại index khác nhau trên cùng một (nhiều) cột, nhưng chỉ một index có thể được đặt ở trạng thái visible tại cùng một thời điểm, trừ khi index kia là function-based index (vì một function-based index không thực sự nằm trên cùng cột; nó dựa trên hàm của cột đó).

Tính năng này rất hữu ích cho các khối lượng công việc thay đổi trên cùng một bảng. Việc sử dụng các loại index khác nhau cho các tác vụ batch, truy vấn, hoặc data warehouse vào các thời điểm khác nhau trong ngày hoặc đêm rất hiệu quả. Tuy nhiên, có một số hạn chế: Đối với một (nhiều) cột nhất định, chúng ta không thể tạo cả B-tree và B-tree cluster index. Chúng ta cũng không thể tạo cả B-tree index và index-organized table (IOT). Tất cả các index đều được duy trì trong quá trình thực hiện các thao tác DML (ngay cả khi được đặt ở trạng thái invisible). Hãy lưu ý rằng, các thao tác DML có thể chạy chậm nếu có quá nhiều index được tạo.

Dưới đây là một câu lệnh SELECT cơ bản từ bảng DEPT mà chúng ta sẽ sử dụng cho các ví dụ trong phần này:

Đầu tiên, tạo một unique index, không cho phép insert các giá trị trùng lặp:

Ngay cả khi các index được đặt ở trạng thái invisible, các giá trị trùng lặp vẫn không thể được insert vào:

Ví dụ sau kiểm tra xem các index có được đặt ở trạng thái invisible hay không, để thực hiện visible lại index.

Chúng ta không thể tạo một index khác trên cùng một cột:

Tuy nhiên, chúng ta có thể nếu làm cho index đầu tiên invisible đi, sau đó tạo index thứ hai:

Bây giờ, nếu kiểm tra thông tin VISIBILITY của index, chúng ta sẽ thấy hai index trên cùng một cột:

Nếu chúng ta cố gắng làm cho cả hai index visible, sẽ dẫn đến lỗi như hình bên dưới. Chỉ một index có thể visible tại một thời điểm (trừ các function-based index).

Lưu ý rằng, mặc dù có một unique index (đang ẩn), chỉ có visible index mới được sử dụng:

Để thêm một index thứ ba, chúng ta phải làm cho hai index đầu tiên invisible đi:

Có thể tạo hai index visible trên một cột cùng lúc, nếu một trong số đó là function-based index. Hãy làm cho index thứ ba invisible đi và thêm index thứ tư (và thứ năm):

Bây giờ khi kiểm tra thông tin VISIBILITY của các index, chúng ta sẽ thấy rằng có năm index trên cùng một cột, với hai index (một trong số đó là function-based index) đang visible:

TIP

Bằng cách sử dụng các invisible index, chúng ta có thể tạo nhiều index trên cùng một cột hoặc nhiều cột. Điều này có thể hữu ích với các khối lượng công việc thay đổi. Ví dụ, chúng có thể sử dụng một B-tree index cho khối lượng công việc hàng ngày và sử dụng reverse key index vào các job ban đêm. Tuy nhiên, hãy lưu ý rằng có sự bảo trì nội bộ của index và các chi phí cho các thao tác INSERT, DELETE và khi cập nhật cột đã được index trên tất cả các index này.