Đã đăng vào Thứ Hai, 3:55 SA 15 phút đọc

Index Binary Height Oracle Database

1. Tổng quan

Binary Height của một chỉ mục đóng vai trò quan trọng trong việc xác định lượng I/O cần thiết để trả về ROWID cho tiến trình của người dùng. Mỗi cấp độ trong binary height sẽ thêm một khối (block) cần được đọc, và do các block này không được đọc theo thứ tự, nên mỗi block sẽ yêu cầu một thao tác I/O riêng biệt. Trong Hình bên dưới, một chỉ mục có binary height là ba khi trả về một hàng (row) cho người dùng sẽ cần đọc tổng cộng bốn block: ba block từ chỉ mục và một block từ bảng. Khi binary height của chỉ mục tăng lên, lượng I/O cần thiết để truy xuất dữ liệu cũng tăng theo.

Sau khi Analyze một chỉ mục, chúng có thể truy vấn cột BLEVEL của DBA_INDEXES view để xem binary height của nó:

TIP

Analyze chỉ mục hoặc bảng sẽ cung cấp binary height của chỉ mục. Sử dụng cột BLEVEL trong USER_INDEXES view để kiểm tra binary height của các chỉ mục.

Binary height tăng chủ yếu do số lượng các giá trị không NULL trong cột được index trong bảng và độ hẹp của phạm vi giá trị trong các cột được chỉ mục. Sự hiện diện của nhiều hàng đã xóa trong chỉ mục cũng có thể làm tăng chiều cao. Rebuild chỉ mục có thể giúp giảm chiều cao. Mặc dù những bước này làm giảm số lượng I/O thực hiện trên chỉ mục, nhưng lợi ích về hiệu suất có thể không đem lại nhiều hiệu quả đáng kể. Nếu số lượng hàng đã xóa trong một chỉ mục đạt khoảng 20–25%, thì nên rebuild các chỉ mục để giảm binary height và không gian trống được đọc trong quá trình I/O.

TIP

Nói chung, database block size lớn hơn sẽ làm giảm binary height của chỉ mục. Mỗi cấp độ bổ sung trong binary height (BLEVEL) sẽ tăng thêm chi phí hiệu suất trong các thao tác DML.

2. Thông tin bổ sung về BLEVEL và Index Height

Cấp độ b-tree (BLEVEL) là độ sâu của chỉ mục từ root block đến các leaf blocks. Một độ sâu bằng 0 cho thấy root block và leaf blocks là cùng một block. Tất cả các chỉ mục bắt đầu với một leaf node duy nhất, đại diện cho cấp độ b-tree là 0. Khi các hàng dữ liệu được thêm vào chỉ mục, Oracle sẽ đưa dữ liệu vào block/leaf. Khi block ban đầu đầy khi các hàng được chèn vào, hai block mới sẽ được tạo ra. Oracle xử lý việc này theo hai cách, thường được gọi là phân tách chỉ mục 90-10 hoặc 50-50. Mỗi loại phân tách (chỉ thực hiện một trong hai loại) phụ thuộc vào giá trị đang được chèn vào.

Nếu giá trị mới lớn hơn bất kỳ giá trị nào đã có trong chỉ mục, thì Oracle sẽ thực hiện phân tách 90-10 bằng cách sao chép các giá trị của block vào một trong các khối mới và đặt giá trị mới vào block còn lại.
Nếu giá trị mới được chèn vào không phải là giá trị lớn nhất trong chỉ mục, thì Oracle sẽ phân tách block theo tỷ lệ 50-50. Nó sẽ đặt nửa dưới của các giá trị đã chỉ mục vào một trong các block mới và nửa trên của các giá trị đã chỉ mục vào block mới còn lại.

Block hiện có, vốn đã đầy, được cập nhật để chỉ chứa các con trỏ đến các leaf node mới và trở thành branch, cụ thể là root branch của chỉ mục. Kết quả là chỉ mục bây giờ có branch level là 1. Khi các hàng (row) tiếp tục được chèn vào chỉ mục, khi một leaf node đầy, Oracle sẽ tạo một leaf block mới. Nếu giá trị được chèn vào lớn hơn bất kỳ giá trị nào hiện có trong leaf block, thì Oracle sẽ đặt giá trị mới vào block mới. Nếu giá trị không phải là giá trị lớn nhất, thì Oracle sẽ chia các giá trị của khối thành hai nửa (50-50) theo giá trị, giữ lại các giá trị thấp hơn trong block hiện tại và đặt các giá trị cao hơn vào block mới. Sau đó, branch block cho những leaf node này sẽ được cập nhật với con trỏ cho block mới và block hiện có. Quá trình này tiếp tục cho đến khi branch node/block đầy. Khi branch node đầy, thao tác phân tách block tương tự sẽ được thực hiện. Một branch block mới được thêm vào và một nửa block được sao chép vào block mới, trong khi nửa còn lại vẫn ở branch block hiện có. Điều này không làm tăng chiều cao hoặc BLEVEL của chỉ mục; nó chỉ cung cấp một branch mới để truy vấn có thể theo khi duyệt chỉ mục. Chỉ khi root branch block đầy và bị phân tách thì chiều cao của chỉ mục mới tăng lên.

Chú ý:

Thời điểm duy nhất mà hai block mới được tạo ra cho một chỉ mục là khi root branch block bị phân tách. Nội dung trong root block hiện tại được chia sẻ giữa hai branch block mới, tạo thành mức cao nhất của một cây chỉ mục cao hơn. Root block không thay đổi địa chỉ block của nó, và bằng cách thêm hai block khi xảy ra phân tách tại root, cây chỉ mục luôn được cân bằng.

3. Ảnh hướng các thao tác UPDATE đến chỉ mục

Chỉ mục chỉ bị ảnh hưởng bởi một thao tác UPDATE nếu các cột trong bảng đánh index được cập nhật. Do đó, trong nhiều trường hợp, thao tác UPDATE không ảnh hưởng đến chỉ mục. Khi các cột của bảng đánh index được cập nhật, thao tác bên trong chỉ mục thực tế sẽ là một thao tác DELETE và INSERT. Giá trị cũ sẽ được đánh dấu là đã xóa và một giá trị mới cho entry sẽ được chèn vào. Vì vậy, không có “cập nhật” thực sự nào được thực hiện trong chỉ mục, theo cách mà chúng ta thường nghĩ đến một thao tác cập nhật. Các entry trong chỉ mục cũng được dọn dẹp bởi tính năng dọn dẹp Oracle’s delayed block. Chỉ sau khi entry chỉ mục bị xóa và block được dọn dẹp, không gian trong index block mới có thể được tái sử dụng cho các entry mới.

4. Ảnh hướng các thao tác DELETE đến chỉ mục

Các thao tác DELETE trên một chỉ mục thực tế không xóa entry khỏi chỉ mục để tạo không gian trống. Khi một thao tác DELETE xảy ra trên một hàng trong bảng, entry tương ứng trong chỉ mục sẽ được đánh dấu là đã xóa nhưng vẫn giữ nguyên dữ liệu trong chỉ mục cho đến khi được dọn dẹp. Cách phổ biến nhất để dọn dẹp một entry chỉ mục là trong một thao tác INSERT trên block đó. Các index entry cũng được dọn dẹp bởi tính năng dọn dẹp Oracle’s delayed block. (điều này xảy ra trong một truy vấn tiếp theo, có thể là câu lệnh SELECT). Sau khi index entry bị xóa và block được dọn dẹp, không gian trong index block có thể được tái sử dụng cho các entry mới.

5. Ảnh Hưởng Đến Chỉ Mục Từ Các Thao Tác UPDATE và DELETE

Đã có nhiều cuộc tranh luận và nhiều huyền thoại về chỉ mục liên quan đến tác động của các thao tác DELETE và UPDATE lên chúng. Chúng ta đã cùng nhau giải thích cách thức hoạt động của các thao tác DELETE và UPDATE cơ bản trong các chỉ mục Oracle, vì vậy hãy cùng xem xét chi tiết tác động thực sự. Trong các thao tác DELETE, việc xóa được thực hiện bằng cách đánh dấu index entry là đã xóa, có nghĩa là các thao tác DELETE vẫn giữ lại dữ liệu trong các leaf block cần được dọn dẹp. Một thao tác INSERT trong một leaf block, với các hàng đã được đánh dấu là xóa, sẽ buộc phải dọn dẹp các hàng này, cho phép không gian được "tái sử dụng" cho các thao tác insert tiếp theo trong leaf block đó. Liệu thực tế có những trường hợp nào mà thao tác insert đó sẽ không xảy ra không? Có, nhưng index block cuối cùng sẽ được dọn dẹp bởi Oracle’s delayed block.

Các thao tác UPDATE, cùng với các thao tác DELETE/INSERT trong cùng một transaction, có xu hướng làm tăng kích thước của một chỉ mục một cách đáng kể, nhưng chỉ khi chúng ta thực hiện những thao tác này với số lượng lớn trong cùng một transaction (điều này không được khuyến khích). Các thao tác DELETE tự nó không gây ra việc độ cao chỉ mục hay BLEVEL cao hơn, mà chỉ là triệu chứng của vấn đề lớn hơn liên quan đến việc tái sử dụng các khu vực hàng đã bị xóa. Điều này có nghĩa là một số lượng lớn các thao tác DELETE hoặc UPDATE, hoặc một số lượng lớn các entry đã bị xóa/cập nhật trong một transaction đơn, có thể gây ra việc kích thước, độ cao và BLEVEL tăng lên (nhưng Oracle không khuyến khích chúng ta thực hiện quá nhiều thao tác DML trong cùng một transaction). Giải pháp cho vấn đề này là chia nhỏ các transaction của chúng ta bằng cách thực hiện commit, tạo ra nhiều transaction cho một số lượng lớn thao tác DELETE+INSERT. Điều này giúp tái sử dụng không gian và không làm cho chỉ mục lớn hơn một cách không cần thiết. Điều này cũng giải thích tại sao các thao tác DELETE lớn có chứa các thao tác INSERT lớn trong cùng một giao dịch thường gây ra sự gia tăng kích thước của chỉ mục.

Thực tế, chỉ mục cũng giống như con người, có một "cân nặng" mà chúng ta thích duy trì. Có người thì mũm mĩm, có người thì gầy, có người cao, và có người thấp. Chúng ta có thể ăn kiêng, nhưng thường có xu hướng quay lại cân nặng ổn định lúc ban đầu. Tương tự, chỉ mục cũng vậy. Nếu một chỉ mục muốn trở nên "mập" hơn, thì hàng tháng, chúng ta thường rebuild nó (như cho nó ăn kiêng). Trong nửa tháng đầu, nó sẽ lại trở nên "mập" và tạo ra rất nhiều redo do các lần tách block mà nó trải qua.

Bài học rút ra là nên sử dụng các tablespace được quản lý cục bộ để tránh phân mảnh và chỉ rebuild các chỉ mục khi cần thiết (xây dựng chúng đúng từ đầu và chỉ rebuild khi hiệu suất giảm, thay vì làm điều đó một cách định kỳ).

6. Ảnh Hưởng Đến KÍCH THƯỚC KHỐI (BLOCKSIZE)

Như đã đề cập, chiều cao và branch level của một chỉ mục chỉ tăng lên khi xảy ra tách block và làm cho tất cả các branch blocks cho đến root block cũng bị tách. Số lần tách block, hay cụ thể hơn là tách branch blocks, có thể được giảm thiểu bằng cách sử dụng kích thước block lớn hơn cho các chỉ mục. Đây là lý do tại sao một số chuyên gia tin rằng các chỉ mục nên được tạo trong các tablespace có kích thước block lớn hơn. Nếu mỗi block có thể chứa nhiều dữ liệu hơn, thì nhu cầu tách block sẽ xảy ra ít thường xuyên hơn. Do đó, tổng số branch và leaf có thể được giảm thiểu. Điều này phụ thuộc lớn vào kích thước dữ liệu của các giá trị được chỉ mục—kích thước block cũ so với kích thước block mới.

Hãy cẩn thận khi đo lường tác động của tablespace có kích thước block lớn hơn lên một chỉ mục. Việc chuyển một chỉ mục sang tablespace có kích thước block lớn hơn đòi hỏi phải rebuild lại chỉ mục, điều này sẽ xóa bỏ tất cả các entry đã được đánh dấu là xóa và nén không gian trong chỉ mục, thu hồi không gian không chỉ cho các entry đã được đánh dấu là xóa mà còn cho các entry đã bị xóa mà không được thu hồi hoặc tái sử dụng. Do đó, tác động đầy đủ của kích thước block lớn hơn có thể không như mong đợi hoặc ấn tượng khi chúng xem xét những gì thực sự sẽ xảy ra.