Tìm hiểu về index partition, phân biệt local index và global index

Mục đích: 

Index tạo trên bảng partition có thể là partition hoặc nonpartition, với partition index giúp dễ dàng quản lý, tăng tính sẵn sàng, hiệu năng cao hơn và khả năng mở rộng tốt hơn. Bạn có thể tạo partititioin độc lập (global index) hoặc tự động ánh xạ 1-1 với partition của table (local index). Nhưng nhìn chung bạn nên tạo global index cho ứng dụng OLTP (do nhiều khi không quét theo trường partition key của bảng được) và local index cho ứng dụng data warehousing hoặc DSS (decision support system).
Qua bài viết này hy vọng sẽ giúp bạn Hiểu về index partition, phân biệt local index và global index. 

>>>Đọ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)

Khi nào cần tạo partition cho index?

Một số gợi ý khi tạo partition cho index:
• Tránh bảo trì lại  cả cây index khi dữ liệu bị xóa.
• Thực hiện bảo trì trên các phần (các partition) của dữ liệu mà không làm mất hiệu lực toàn bộ index.
• Giảm ảnh hưởng của sai lệch index gây ra bởi một index trên một cột có giá trị tăng đơn điệu.

Lựa chọn kiểu partition index nào?

Khi quyết định loại partition index nào sẽ sử dụng, bạn nên xem xét các nguyên tắc sau theo thứ tự này:

1. Nếu cột partition key được sử dụng là 1 trong các điều kiện WHERE của DML/DDL, thì hãy sử dụng local index. Nếu đúng như vậy thì bạn đã xong. Nếu không đúng như vậy, hãy tiếp tục hướng dẫn 2.
2. Nếu là unique index và không bao gồm các cột partition key, thì hãy sử dụng global index. Nếu đúng như vậy thì bạn đã xong. Nếu không, hãy tiếp tục hướng dẫn 3.
3. Nếu ưu tiên của bạn là khả năng quản lý, thì hãy xem xét dùng local index. Nếu đúng như vậy thì bạn đã xong. Nếu không đúng như vậy, hãy tiếp tục hướng dẫn 4.
4. Nếu ứng dụng là loại OLTP và người dùng cần thời gian phản hồi nhanh thì hãy sử dụng global index. Nếu ứng dụng là loại DSS và người dùng quan tâm hơn đến dung lượng sử dụng (quét nhiều dữ liệu), thì hãy sử dụng local index.

LOCAL PARTITION INDEX

Local partition index dễ quản lý hơn global partition index. Chúng cũng cung cấp tính khả dụng cao hơn và phổ biến trong môi trường DSS. Lý do cho điều này là phân vùng trang bị: mỗi partition của local index được liên kết với chính xác một partition của bảng. Chức năng này cho phép Oracle tự động giữ các index partition được đồng bộ hóa với các table partition và làm cho mỗi cặp table-index trở nên độc lập. Bất kỳ hành động nào làm cho dữ liệu của một partitioin không hợp lệ (invalid) hoặc không có sẵn chỉ ảnh hưởng đến một phân vùng duy nhất.

Các local partition index hỗ trợ tính khả dụng (availability) hơn khi có các hoạt động bảo trì partition hoặc subpartition trên bảng. Khi thêm mới, drop, merge, split, hash partition hay subpartition được thêm mới thì index partition sẽ được tự động tạo theo.

Bạn không thể thêm partition vào local index. Thay vào đó, các partition mới chỉ được thêm vào local index khi bạn thêm partition vào bảng bên dưới. Tương tự như vậy, bạn không thể drop một partition khỏi một local index. Thay vào đó, các partition của local index chỉ bị drop khi bạn drop partition từ bảng.

Local index có thể là duy nhất. Tuy nhiên, để 1 local index là duy nhất, partition key của bảng là một phần trong các cột đánh index.

Đây cũng là kiểu index mà tôi thường sử dụng nếu không muốn nói là 100% cho các bảng đánh partition và rất hiệu quả với những ứng dụng Core, online 24/7, phức tạp “bậc nhất” ở VN mà tôi đang quản trị.

Ví dụ:

1 bảng app_owner.tab1 partition theo trường start_datetime

câu lệnh SQL hay sử dụng là select * from app_owner.tab1 where start_datetime >= sysdate-1 and start_datetime <sysdate and sub_id=:a1;

Khi đó ta tạo local index như sau:

create index app_owner.tab1_ind1 on app_owner.tab1(sub_id) tablespace INDX local online;

(Có thể dùng câu lệnh sau nếu dữ liệu lớn:

create index app_owner.tab1_ind1 on app_owner.tab1(sub_id) tablespace INDX nologging local parallel 8 online;

alter index app_owner.tab1_ind1 noparallel;)

Global Partitioned Indexes

Oracle cung cấp các global range partitioned indexes và global hash partitioned indexes, được thảo luận trong các chủ đề sau:

• Global Range Partitioned Indexes
• Global Hash Partitioned Indexes
• Bảo trì Global Partitioned Indexes

Global Range Partitioned Indexes
Global Range Partitioned Indexes linh hoạt ở chỗ mức độ phân vùng và partition key độc lập với phương pháp partition của bảng.
Partition cao nhất của global index là điểm giới hạn của partition, tất cả đều có giá trị MAXVALUE. Điều này đảm bảo rằng tất cả các row trong bảng có thể được đặt trong index. 
Bạn không thể thêm partition vào global index vì partition cao nhất luôn có giới hạn partition của MAXVALUE. Để thêm một partition cao nhất mới, hãy sử dụng câu lệnh ALTER INDEX SPLIT PARTITION. Nếu một global index partition trống, bạn có thể loại bỏ nó một cách rõ ràng bằng cách đưa ra câu lệnh ALTER INDEX DROP PARTITION. Nếu một global index partition có chứa dữ liệu, việc drop partitioin sẽ khiến partition cao nhất tiếp theo được đánh dấu là unusable. Bạn không thể drop partition cao nhất trong global index.
Global Hash Partitioned Indexes
Các global hash parttitioned index cải thiện hiệu suất bằng cách loại bỏ được nghẽn khi index đang tăng trưởng một cách đơn điệu. Nói cách khác, hầu hết các lần thêm mới index chỉ xảy ra ở cạnh bên phải của một index, được trải đều trên N partition đã hash cho một global hash partition index.
Bảo trì Global Partitioned Indexes
Những hoạt động bảo trì/thay đổi partition như : ADD (HASH), COALESCE (HASH), DROP , EXCHANGE, MERGE, MOVE, SPLIT, TRUNCATE sẽ làm global index là unusable.
Các index này có thể được duy trì bằng cách thêm mệnh đề UPDATE INDEXESvào các câu lệnh SQL cho hoạt động trên (ví dụ alter index add partition DATA20220228 UPDATE INDEXES). Tuy nhiên, lưu ý rằng việc bổ sung UPDATE INDEXES khi thực hiện bảo trì global index như một phần của hoạt động bảo trì partition, có thể kéo dài thời gian chạy của hoạtd động trên và tăng yêu cầu tài nguyên (ví dụ khi thêm mới partition DATA20220228 nó phải thực hiện rebuild lại partition index bị unusable trước khi thông báo hoàn thành việc thêm mới partition)
Hai lợi ích để duy trì các global index là:

• Index vẫn avaiable và online trong suốt quá trình hoạt động. Do đó không có ứng dụng nào khác bị ảnh hưởng bởi thao tác này.
• Index không phải rebuild sau các hoạt động trên 
• Việc duy trì global index cho DROP và TRUNCATE được thực hiện dưới dạng metadata.

Index này tôi gần như KHÔNG sử dụng cho các bảng partition do tính phức tạp, khó bảo trì của nó (bài toán OLTP tôi đã có hướng xử lý riêng nên không cần global partition index), còn các bạn sử dụng hay không thì tùy nhé.

Global Nonpartitioned Indexes

Global nonpartitioned index hoạt động giống như local nonpartitioned index, đây là index phổ biến cho những ai mới làm quen với partition, nhược điểm là khi có các hoạt động trên partition (thêm mới, move, modify, split, exchange, drop, truncate,..) sẽ làm Global nonpartitioned index hỏng (trạng thái unusable, khi đó DML bị lỗi) nên mọi người KHÔNG nên dùng index này (tuy nhiên nó cũng có ưu điểm là khá nhanh cho nghiệp vụ OLTP mà không quét trường partition key, nhưng cũng không nhanh hơn mấy đâu).

Ví dụ  1 bảng app_owner.tab1 partition theo trường start_datetime câu lệnh SQL hay sử dụng là

select * from app_owner.tab1 where start_datetime >= sysdate-1 and start_datetime <sysdate and sub_id=:a1;

Khi đó ta tạo Global nonpartitioned index như sau:

create index app_owner.tab1_ind1 on app_owner.tab1(sub_id) tablespace INDX online;

Có thể dùng câu lệnh sau nếu dữ liệu lớn:

create index app_owner.tab1_ind1 on app_owner.tab1(sub_id) tablespace INDX nologging parallel 8 online;
alter index app_owner.tab1_ind1 noparallel;

Tham khảo thêm tài liệu chuẩn của Oracle:https://docs.oracle.com/database/121/VLDBG/toc.htm

>>>Đọ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)