Index Tips Review Oracle Database

Khi chúng ta nâng cấp các phiên bản Oracle, hãy kiểm tra các truy vấn của ứng dụng để xác định xem các đường dẫn thực thi (execution paths) của truy vấn có còn sử dụng các index như trước khi nâng cấp hay không. Kiểm tra xem chiến lược thực thi có thay đổi không và liệu nó có tốt hơn hoặc tệ hơn.

Chúng ta có thể truy vấn DBA_INDEXES và DBA_IND_COLUMNS view để lấy danh sách các index trên một bảng cụ thể. Sử dụng USER_INDEXES và USER_IND_COLUMNS view để lấy thông tin chỉ cho schema.

Bằng cách sử dụng các index ẩn (invisible indexes), chúng ta có thể tạm thời "ẩn" các index để kiểm tra xem các truy vấn hoạt động như thế nào khi không có chúng. Vì các index ẩn vẫn được duy trì khi ẩn, việc bật lại chúng nếu cần là nhanh chóng và đơn giản.

Các index ẩn (invisible indexes) cũng là phương tiện để tạo nhiều index trên cùng một cột. Điều này cho phép sử dụng một loại index cho xử lý giao dịch ban ngày, một loại khác cho xử lý cho các job ban đêm, và có thể là một loại khác cho xử lý hàng tháng, hàng quý. Chỉ một index có thể hiển thị tại một thời điểm (ngoại trừ các function-based index).

Chúng ta có thể tạo index và analyze chúng trong một bước duy nhất bằng cách sử dụng mệnh đề COMPUTE STATISTICS trong lệnh CREATE INDEX, nhưng từ phiên bản 10g, Oracle luôn tính toán số liệu thống kê cho tất cả các thao tác CREATE/REBUILD index khi index không rỗng (not empty).

Việc sử dụng mệnh đề giá trị mặc định (default values) cho một cột trong bảng sẽ không cho phép các giá trị NULL và loại bỏ các vấn đề hiệu suất liên quan đến việc sử dụng giá trị NULL.

Bằng cách sử dụng các hàm (như TO_DATE hoặc TO_CHAR) để thay đổi giá trị so sánh với một cột thay vì thay đổi chính cột đó, các chỉ mục (index) sẽ được sử dụng. Các chỉ mục có thể bị vô hiệu hóa nếu chúng sử dụng hàm trực tiếp trên cột.

Việc so sánh các kiểu dữ liệu không tương đồng có thể khiến Oracle vô hiệu hóa chỉ mục bên trong. Ngay cả khi sử dụng lệnh EXPLAIN PLAN cho truy vấn, chúng ta cũng không thể hiểu được tại sao lại xảy ra quét toàn bộ bảng.

Tính chọn lọc của một chỉ mục giúp cost-based optimizer xác định đường dẫn thực thi. Chỉ mục càng có tính chọn lọc cao, càng ít số lượng hàng được trả về. Nâng cao tính chọn lọc bằng cách tạo các chỉ mục kết hợp (nhiều cột).

Việc analyze chỉ mục hoặc bảng sẽ cung cấp chiều cao nhị phân của chỉ mục. Sử dụng cột BLEVEL trong USER_INDEXES view để kiểm tra chiều cao nhị phân của chỉ mục.

Nếu số lượng hàng bị xóa trong một chỉ mục đạt từ 20-25%, hãy rebuild chỉ mục để giúp giảm chiều cao nhị phân và lượng không gian trống đang được đọc trong quá trình I/O.

Nếu dữ liệu trong bảng không đồng đều, histograms cung cấp cho cost-based optimizer một cái nhìn tổng quan về phân bố dữ liệu. Sử dụng histograms trên các cột khá đồng đều sẽ không tăng hiệu suất mà có thể làm giảm nó.

Đối với các bảng lớn với chỉ mục kết hợp, tính năng skip-scan của chỉ mục cung cấp khả năng truy cập nhanh ngay cả khi cột đầu tiên của chỉ mục không được sử dụng trong điều kiện giới hạn.

Giá trị của các cột được đánh chỉ mục được lưu trữ trong chỉ mục. Vì lý do này, chúng có thể tạo các chỉ mục kết hợp có thể được sử dụng để thỏa mãn một truy vấn mà không cần truy cập vào bảng. Điều này giúp loại bỏ việc truy cập bảng để lấy dữ liệu, giảm bớt I/O.

Sử dụng chỉ mục bitmap cho các cột có số lượng giá trị thấp (cardinality thấp). Ví dụ, một cột gọi là GENDER với hai giá trị có thể là "nam" hoặc "nữ" (cardinality chỉ là 2).

Để truy vấn danh sách các chỉ mục bitmap của chúng ta, hãy truy vấn trong bảng USER_INDEXES.

Không nên sử dụng chỉ mục bitmap để tránh các vấn đề khóa (locking), đặc biệt khi có nhiều thao tác INSERT xảy ra trên các bảng, chẳng hạn như trong các môi trường OLTP lớn. Hãy tìm hiểu những hạn chế liên quan đến chỉ mục bitmap.

Cân nhắc sử dụng các bảng tổ chức theo chỉ mục (index-organized tables) cho những bảng luôn được truy cập bằng cách so khớp chính xác hoặc range scan trên khóa chính.

Nếu chúng ta có số lượng đĩa hạn chế và phải thực hiện nhiều tải tuần tự đồng thời lớn, chỉ mục khóa đảo (reverse key indexes) có thể là giải pháp khả thi.

Để các chỉ mục dựa trên hàm (function-based indexes) được tối ưu hóa sử dụng, chúng ta phải thiết lập tham số khởi tạo QUERY_REWRITE_ENABLED thành TRUE.

Chỉ mục local prefixed cho phép Oracle loại bỏ nhanh các phân vùng (partition) không cần thiết. Những phân vùng không chứa giá trị nào xuất hiện trong mệnh đề WHERE sẽ không cần được truy cập, từ đó cải thiện hiệu suất của câu lệnh.

Chỉ định câu lệnh UPDATE GLOBAL INDEXES trong lệnh ALTER TABLE khi sửa đổi bảng phân vùng (table partition). Theo mặc định, chúng cần phải rebuild các chỉ mục toàn cục (global index) khi thay đổi bảng phân vùng.

Sử dụng chỉ mục kết hợp bitmap để cải thiện hiệu suất của các phép nối (joins) trong các data warehouse.

Sử dụng tùy chọn REBUILD của lệnh ALTER INDEX để nhanh chóng xây dựng lại chỉ mục bằng cách sử dụng chỉ mục hiện có thay vì bảng.

Chúng ta có thể sử dụng tùy chọn REBUILD ONLINE để cho phép các thao tác DML trên bảng hoặc phân vùng trong khi chỉ mục đang được xây dựng lại. Chúng không thể chỉ định REBUILD ONLINE cho các chỉ mục bitmap hoặc chỉ mục áp dụng các ràng buộc toàn vẹn tham chiếu.

Chúng ta có thể thực hiện chuyển đổi online từ bảng không phân vùng (non-partition) sang bảng phân vùng (partition), bao gồm cả các chỉ mục. Chúng ta cũng có thể đặt các partition và subpartitions ở trạng thái chỉ đọc.