1. Mở Đầu

Chào mừng bạn đến với series "Giới thiệu về Chỉ Mục", nơi chúng ta sẽ khám phá và tìm hiểu về chỉ mục trong cơ sở dữ liệu Oracle. Mỗi bài viết sẽ đưa ra những thách thức từ những kiến thức cơ bản đến nâng cao, giúp bạn trở thành một chuyên gia trong lĩnh vực này.

2. Khái Niệm Cơ Bản Về Chỉ Mục

Khi truy vấn dữ liệu từ bảng trong cơ sở dữ liệu Oracle, có hai cách tiếp cận chính: đọc toàn bộ bảng (full table scan) hoặc truy cập từng hàng qua ROWID. Việc sử dụng chỉ mục (index) trở nên cần thiết khi bạn chỉ cần truy xuất một tỷ lệ nhỏ hàng từ một bảng lớn. Chẳng hạn, trong trường hợp bạn chỉ cần lấy 5% số hàng từ một bảng rất lớn, việc sử dụng chỉ mục sẽ giúp giảm thiểu thao tác I/O đáng kể. Nếu không sử dụng chỉ mục, bạn sẽ phải quét toàn bộ bảng, điều này là không hiệu quả.

Mức độ cải thiện hiệu suất mà chỉ mục mang lại phụ thuộc vào tính chọn lọc của dữ liệu và cách dữ liệu được phân phối trong bảng. Nếu dữ liệu có tính chọn lọc cao, chỉ một số ít hàng sẽ khớp với giá trị trong chỉ mục (ví dụ như số hộ chiếu, số căn cước công dân), Oracle có thể nhanh chóng truy vấn chỉ mục để tìm ROWID khớp và lấy dữ liệu tương ứng. Ngược lại, nếu độ chọn lọc thấp (ví dụ như tên quốc gia), chỉ mục có thể trả về nhiều ROWID hơn, buộc hệ thống phải truy vấn nhiều block khác nhau, dẫn đến hiệu suất kém.

Ngoài ra, nếu các hàng trong dữ liệu không được lưu trữ gần nhau trong bảng, việc sử dụng chỉ mục sẽ có ít lợi ích hơn. Nếu dữ liệu nằm rải rác ở nhiều block khác nhau, Oracle có thể phải đọc nhiều block riêng lẻ, làm giảm hiệu suất. Trong những trường hợp như vậy, việc sử dụng quét toàn bộ bảng có thể mang lại hiệu quả tốt hơn. Oracle tối ưu hóa cơ chế quét bằng cách sử dụng chế độ đọc đa block (multiblock mode), giúp tăng tốc quá trình quét bảng.

Khi sử dụng Oracle, có nhiều tùy chọn khả dụng như phân vùng (partitioning), thực hiện DML song song, và các thao tác truy vấn song song, tất cả đều góp phần thay đổi điểm cân bằng giữa việc quét toàn bộ bảng và tìm kiếm chỉ mục. Phần cứng ngày càng mạnh mẽ hơn và chi phí cũng ngày càng giảm, điều này đồng nghĩa với việc Oracle cũng đã cải thiện khả năng của các chỉ mục thông qua các kỹ thuật như skip-scan.

Mẹo
Khi nâng cấp phiên bản Oracle, đừng quên kiểm tra các truy vấn của ứng dụng để xem execution paths có còn sử dụng các chỉ mục như trước đây không. Đảm bảo xem xét execution plan để xác định xem có sự thay đổi nào không và ảnh hưởng của chúng ra sao.

Các chỉ mục thường làm tăng hiệu suất cho các truy vấn như câu lệnh SELECT, các mệnh đề WHERE của câu lệnh UPDATE, và DELETE (khi chỉ truy cập đến một số ít hàng). Tuy nhiên, việc thêm chỉ mục có thể làm giảm hiệu suất của các câu lệnh INSERT do cần thêm thao tác vào cả bảng và chỉ mục. Các thao tác UPDATE trên các cột có chỉ mục cũng sẽ chậm hơn so với các cột không có chỉ mục vì cơ sở dữ liệu cần quản lý sự thay đổi cho cả bảng và chỉ mục. Thêm nữa, việc xóa (DELETE) một số lượng lớn hàng cũng sẽ bị chậm lại khi bảng có chỉ mục.

Để liệt kê tất cả các chỉ mục trên một bảng, bạn có thể truy vấn từ view DBA_INDEXES. Nếu bạn chỉ muốn lấy chỉ mục cho schema của mình, hãy sử dụng USER_INDEXES. Để xem các chỉ mục trên tất cả các bảng mà bạn có quyền truy cập, hãy truy vấn ALL_INDEXES.

Dưới đây là ví dụ về việc tạo chỉ mục trên bảng EMP thuộc sở hữu của user SCOTT:

Khi thực hiện các lệnh này, cơ sở dữ liệu sẽ tạo ra hai chỉ mục khác nhau trên bảng EMP, mỗi chỉ mục sẽ bao gồm các giá trị từ bảng cùng với ROWID cho các hàng khớp. Nếu bạn muốn tìm một bản ghi trong bảng EMP mà có giá trị SAL là 1000, trình tối ưu hóa có thể sử dụng chỉ mục EMP_ID2 để tìm kiếm, sau đó tra cứu ROWID tương ứng để lấy hàng trong bảng.

Câu truy vấn dưới đây từ view USER_INDEXES (và bạn cũng có thể kiểm tra trong DBA_INDEXES) cho thấy các chỉ mục mới tạo trên bảng EMP:

Câu lệnh này hiển thị hai chỉ mục, nhưng không cho biết chi tiết về các cột trong từng chỉ mục. Để lấy thông tin cụ thể về các cột được chỉ mục trên một bảng của người dùng hiện tại, xem VIEW USER_IND_COLUMNS. Các DBA có thể truy cập DBA_IND_COLUMNS để kiểm tra tất cả các cột được lập chỉ mục cho tất cả các schema. Với ALL_IND_COLUMNS, chúng ta có thể xem các cột được lập chỉ mục cho tất cả các bảng mà chúng ta có quyền truy cập.

Bảng EMP có hai chỉ mục, trong đó chỉ mục đầu tiên, EMP_ID1, là chỉ mục kết hợp, đã lập chỉ mục cho các cột EMPNO, ENAME và DEPTNO. Chỉ mục thứ hai, EMP_ID2, chỉ lập chỉ mục cho cột SAL. Thứ tự của các cột (COLUMN_POSITION) cho thấy thứ tự của các cột trong chỉ mục kết hợp — ở đây là EMPNO trước tiên, sau đó là ENAME và cuối cùng là DEPTNO.

Mẹo
Sử dụng truy vấn đối với DBA_INDEXES và DBA_IND_COLUMNS để lấy danh sách các chỉ mục trên một bảng cụ thể. Nếu bạn chỉ cần thông tin cho schema hiện tại, hãy truy vấn USER_INDEXES và USER_IND_COLUMNS.

3. Kết Nối Với Tác Giả

Nếu bạn có bất kỳ thắc mắc nào hoặc muốn trao đổi thêm về bài viết, hãy kết nối với tôi qua LinkedIn và Facebook:

• LinkedIn: https://www.linkedin.com/in/nguyentrungnam/
• Facebook: https://www.facebook.com/trungnam.nguyen.395/

Mong sẽ được kết nối và thảo luận cùng bạn!
source: viblo