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.
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
---------------------- ----------- ---------- ---------- ----------------
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
*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
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
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:
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
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
Xem thêm từ Oracle Doc ID 1373415.1
@
Trần Văn Bình - Founder of Oracle DBA AZ
#học
oracle #oracle database #khóa học oracle online #khóa học oca #học oca ở đâu
#oca là gì #oca oracle #BossData #OraAz #OracleDBAAz #OracleTutorial
#Quản_trị_cơ_sở_dữ_liệu_Oracle #OracleDBA #OracleDatabaseAdministration
