Khi nào một index nên được xây dựng lại (rebuild) trong Oracle Database?

Khái niệm

Một index của Oracle là một đối tượng trong schema có thể tăng tốc độ truy xuất các row bằng cách sử dụng một con trỏ. 
Bạn có thể tạo các index trên một hoặc nhiều cột của bảng để tăng tốc độ thực thi câu lệnh SQL trên bảng đó. 
Nếu bạn không có index trên cột, thì việc quét toàn bộ bảng sẽ xảy ra. Bạn có thể giảm I/O đĩa bằng cách sử dụng phương pháp truy cập đường dẫn nhanh để xác định vị trí dữ liệu nhanh chóng. Theo mặc định, Oracle tạo các index B-tree. 
Sau khi một bảng trải qua một số lượng lớn các lần chèn, cập nhật và xóa, index có thể trở nên mất cân bằng và bị phân mảnh và có thể cản trở hiệu suất truy vấn, giảm hiệu năng.

>>>Đọc thêm:

Khoá học SQL – Truy vấn cơ sở dữ liệu từ cơ bản đến nâng cao

Lộ trình trở thành Data Engineer (Kỹ sư dữ liệu)

Làm thế nào để xác định một index cần phải được xây dựng lại? 

Trước tiên chúng ta phải có ý tưởng về trạng thái hiện tại của index bằng cách sử dụng lệnh 

ANALYZE INDEX <index_name> VALIDATE STRUCTURE.

Lệnh VALIDATE STRUCTURE có thể được thực thi một cách an toàn mà không ảnh hưởng đến trình tối ưu hóa. 
Lệnh VALIDATE STRUCTURE chỉ điền vào bảng SYS.INDEX_STATS. Bảng SYS.INDEX_STATS có thể được truy cập với public synonym INDEX_STATS. Bảng INDEX_STATS sẽ chỉ giữ thông tin xác thực cho một index tại một thời điểm. Bạn sẽ cần truy vấn bảng này trước khi xác thực cấu trúc của index tiếp theo. 
Dưới đây là một đầu ra mẫu từ bảng INDEX_STATS. 

SQL> ANALYZE INDEX IDX_EMP_ACCT VALIDATE STRUCTURE; 
SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;

NAME                      HEIGHT    LF_ROWS    LF_BLKS    DEL_LF_ROW

———————- ———–   ———-      ———-   —————-

DX_EMP_ACCT           2             1                     3               6

1 hàng được chọn.

Có hai quy tắc để giúp xác định xem chỉ số có cần được xây dựng lại không.

1. Nếu index có chiều cao lớn hơn bốn, hãy xây dựng lại index.
2. Các hàng lá bị xóa phải nhỏ hơn 20%. 

Nếu xác định rằng index cần được xây dựng lại, điều này có thể dễ dàng được thực hiện bằng ALTER INDEX <INDEX_NAME> REBUILD | Lệnh REBULID ONLINE. Không khuyến khích lệnh này thực thi trong giờ hoạt động bình thường. Thay thế là xóa và tạo lại index. Tạo một index sử dụng bảng cơ sở làm nguồn dữ liệu cần đặt khóa trên bảng. Index cũng không có sẵn trong quá trình tạo.

Trong ví dụ này, cột HEIGH hiển thị rõ ràng giá trị 2. Đây không phải là một ứng cử viên tốt để xây dựng lại. Đối với hầu hết các index, chiều cao của index sẽ khá thấp, tức là một hoặc hai. Tôi đã thấy một index trên bảng 2 triệu hàng có chiều cao hai hoặc ba. Một chỉ số có chiều cao lớn hơn bốn có thể cần phải được xây dựng lại vì điều này có thể chỉ ra cấu trúc cây bị lệch. Điều này có thể dẫn đến việc đọc khối cơ sở dữ liệu không cần thiết của index. Hãy lấy một ví dụ khác. 

SQL>  ANALYZE INDEX IDX_EMP_FID VALIDATE STRUCTURE;
SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)

*100 as ratio FROM INDEX_STATS;

NAME                           HEIGHT     LF_ROWS    DEL_LF_ROW RATIO    
—————————— ———- ———- ———- ——-
IDX_EMP_FID                                  1          189         62        32.80

Trong ví dụ này, tỷ lệ các hàng lá bị xóa trên tổng số hàng lárõ ràng là trên 20%. Đây là một ứng cử viên tốt để xây dựng lại.
Hãy xây dựng lại index và kiểm tra kết quả

SQL> ANALYZE INDEX IDX_EMP_FID REBUILD;
SQL> ANALYZE INDEX IDX_EMP_FID VALIDATE STRUCTURE;
SQL> SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*

100 as ratio FROM INDEX_STATS;

NAME                           HEIGHT     LF_ROWS    DEL_LF_ROW RATIO    
—————————— ———- ———- ———- ——-
IDX_EMP_FID                                  1          127         0        0

Kiểm tra bảng INDEX_STATS cho thấy 62 hàng lá bị xóa đã bị loại khỏi index. Lưu ý rằng tổng số hàng lá đi từ 189 đến 127, chênh lệch của 62 hàng lá (189-127). Chỉ số này sẽ cung cấp hiệu suất tốt hơn cho ứng dụng.

Kịch bản để xây dựng lại các index:

Rất khó để viết một tập lệnh sẽ xác định các index sẽ được hưởng lợi từ việc xây dựng lại bởi vì nó phụ thuộc vào cách các index được sử dụng. Ví dụ: các index luôn được truy cập khi quét một lần duy nhất index “sẽ không bao giờ cần xây dựng lại, vì” không gian chết “không can thiệp vào truy cập index. 

Chỉ các index có số lượng khối lá bị xóa cao và được truy cập theo những cách này mới được hưởng lợi từ việc xây dựng lại:

• index fast full scan
• index full scan
• index range scan

Có được giá trị thống kê: bằng chứng từ một hệ thống phục vụ sản xuất không ổn định sẽ là một thách thức phi thường. Trong một hệ thống  phục vụ sản xuất lớn, sẽ là một nỗ lực lớn để theo dõi LIO từ các truy vấn cụ thể đến các index cụ thể trước và sau khi xây dựng lại.

Tuy nhiên, bạn vẫn có thể sử dụng tập lệnh bên dưới để xây dựng lại index sau khi xác minh tất cả:

Select 'alter index ' || owner || '.' || index_name || ' rebuild online;'
  from all_indexes
 where owner='XXX'
 and index_type not in ('DOMAIN', 'BITMAP','FUNCTION-BASED NORMAL','IOT - TOP')
 order by owner, index_name;

Lưu ý: Chỉ xây dựng lại các index B-tree như một khái niệm toàn cầu.

Là khối lá bị xóa được tái sử dụng?

Đúng. nhưng tùy thuộc vào thời gian dữ liệu sẽ được xác nhận lại và trong khi B-Tree sẽ cân bằng, cây sẽ sử dụng lại nó.

Mẫu thử:

SQL> create table test_empty_block (id number, value varchar2(10));
Table created.
SQL> begin
2 for i in 1..10000 loop
3 insert into test_empty_block values (i, 'Bowie');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> create index test_empty_block_idx on test_empty_block (id);
Index created.


SQL> delete test_empty_block where id between 1 and 9990;
9990 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze index test_empty_block_idx validate structure;
Index analyzed.
SQL> select lf_blks, del_lf_rows from index_stats;
LF_BLKS DEL_LF_ROWS
---------- -----------
21             9990

Bây giờ nhập lại một khối lượng tương tự nhưng sau các giá trị hiện tại cuối cùng:

SQL> begin
2 for i in 20000..30000 loop
3 insert into test_empty_block values (i, 'Bowie');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> analyze index test_empty_block_idx validate structure;
Index analyzed.
SQL> select lf_blks, del_lf_rows from index_stats;
LF_BLKS DEL_LF_ROWS
---------- -----------
21      

Lưu ý tất cả các block trống đã được sử dụng lại và xóa hàng.
Câu lệnh chọn sau được thực thi sau khi xóa 9990 trong ví dụ trước

SQL> select /*+ index test_empty_blocks */ * from test_empty_blocks
where id between 1 and 100000;
10 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_EMPTY_BLOCKS'
2 1 INDEX (RANGE SCAN) OF 'TEST_EMPTY_BLOCKS_IDX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
549 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

>>>Đọc thêm:

Khoá học SQL – Truy vấn cơ sở dữ liệu từ cơ bản đến nâng cao

Lộ trình trở thành Data Engineer (Kỹ sư dữ liệu)