Partitioning trong Oracle Database là một kỹ thuật quản lý cơ sở dữ liệu cho phép bạn chia nhỏ các bảng lớn và chỉ mục thành các phần nhỏ hơn, được gọi là partition. Điều này giúp cải thiện hiệu suất truy vấn, dễ dàng quản lý và duy trì các bảng lớn, cũng như tăng cường khả năng mở rộng của cơ sở dữ liệu.
Các loại Partition trong Oracle Database
Oracle hỗ trợ nhiều loại partitioning khác nhau, bao gồm:
- Range Partitioning (Phân vùng theo phạm vi): Dữ liệu được phân vùng dựa trên một phạm vi giá trị của một cột nhất định.
- List Partitioning (Phân vùng theo danh sách): Dữ liệu được phân vùng dựa trên một danh sách các giá trị cụ thể của một cột nhất định.
- Hash Partitioning (Phân vùng theo băm): Dữ liệu được phân vùng ngẫu nhiên dựa trên một hàm băm.
- Composite Partitioning (Phân vùng tổng hợp): Kết hợp nhiều kỹ thuật phân vùng với nhau, chẳng hạn như range-hash hoặc range-list.
Tạo Partition Table
Ví dụ về Range Partitioning
Giả sử bạn có một bảng orders và bạn muốn phân vùng bảng này dựa trên cột order_date.
CREATE TABLE orders (
order_id NUMBER,
customer_id NUMBER,
order_date DATE,
amount NUMBER
)
PARTITION BY RANGE (order_date) (
PARTITION p1 VALUES LESS THAN (TO_DATE(‘2021-01-01’, ‘YYYY-MM-DD’)),
PARTITION p2 VALUES LESS THAN (TO_DATE(‘2022-01-01’, ‘YYYY-MM-DD’)),
PARTITION p3 VALUES LESS THAN (TO_DATE(‘2023-01-01’, ‘YYYY-MM-DD’)),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
Ví dụ về tạo Partition theo ngày: (Ghi chú: phân vùng phải được tạo theo ngày nhỏ trước rồi tới lớn và tới ngày hiện tại, nếu làm ngược lại sẽ lỗi)
## chỉnh định dạng thời gian:
alter session set NLS_DATE_FORMAT=’SYYYY-MM-DD HH24:MI:SS’;
## tạo bảng partition theo ngày tháng 01/2024 => chúng ta phải tạo năm 2023 trước:
CREATE TABLE orders (
order_id NUMBER,
customer_id NUMBER,
order_date DATE,
amount NUMBER
)
PARTITION BY RANGE (order_date) (
PARTITION orders_p2023 VALUES LESS THAN (TO_DATE ('2024-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS'))
);## ADD thêm partition vào table:
alter table orders add PARTITION orders_data20240101 VALUES LESS THAN (TO_DATE(‘2024-01-02 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240102 VALUES LESS THAN (TO_DATE(‘2024-01-03 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240103 VALUES LESS THAN (TO_DATE(‘2024-01-04 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240104 VALUES LESS THAN (TO_DATE(‘2024-01-05 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240105 VALUES LESS THAN (TO_DATE(‘2024-01-06 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240106 VALUES LESS THAN (TO_DATE(‘2024-01-07 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240107 VALUES LESS THAN (TO_DATE(‘2024-01-08 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240108 VALUES LESS THAN (TO_DATE(‘2024-01-09 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240109 VALUES LESS THAN (TO_DATE(‘2024-01-10 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240110 VALUES LESS THAN (TO_DATE(‘2024-01-11 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240111 VALUES LESS THAN (TO_DATE(‘2024-01-12 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240112 VALUES LESS THAN (TO_DATE(‘2024-01-13 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240113 VALUES LESS THAN (TO_DATE(‘2024-01-14 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240114 VALUES LESS THAN (TO_DATE(‘2024-01-15 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240115 VALUES LESS THAN (TO_DATE(‘2024-01-16 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240116 VALUES LESS THAN (TO_DATE(‘2024-01-17 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240117 VALUES LESS THAN (TO_DATE(‘2024-01-18 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240118 VALUES LESS THAN (TO_DATE(‘2024-01-19 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240119 VALUES LESS THAN (TO_DATE(‘2024-01-20 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240120 VALUES LESS THAN (TO_DATE(‘2024-01-21 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240121 VALUES LESS THAN (TO_DATE(‘2024-01-22 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240122 VALUES LESS THAN (TO_DATE(‘2024-01-23 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240123 VALUES LESS THAN (TO_DATE(‘2024-01-24 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240124 VALUES LESS THAN (TO_DATE(‘2024-01-25 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240125 VALUES LESS THAN (TO_DATE(‘2024-01-26 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240126 VALUES LESS THAN (TO_DATE(‘2024-01-27 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240127 VALUES LESS THAN (TO_DATE(‘2024-01-28 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240128 VALUES LESS THAN (TO_DATE(‘2024-01-29 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240129 VALUES LESS THAN (TO_DATE(‘2024-01-30 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240130 VALUES LESS THAN (TO_DATE(‘2024-01-31 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
alter table orders add PARTITION orders_data20240131 VALUES LESS THAN (TO_DATE(‘2024-02-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’));
Ví dụ về List Partitioning
Giả sử bạn muốn phân vùng bảng orders dựa trên cột region.
CREATE TABLE orders (
order_id NUMBER,
customer_id NUMBER,
order_date DATE,
region VARCHAR2(20),
amount NUMBER
)
PARTITION BY LIST (region) (
PARTITION east VALUES (‘East’),
PARTITION west VALUES (‘West’),
PARTITION central VALUES (‘Central’)
);
Ví dụ về Hash Partitioning
Giả sử bạn muốn phân vùng bảng orders dựa trên cột customer_id bằng phương pháp hash.
CREATE TABLE orders (
order_id NUMBER,
customer_id NUMBER,
order_date DATE,
amount NUMBER
)
PARTITION BY HASH (customer_id) (
PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p4
);
Ví dụ về Composite Partitioning
Giả sử bạn muốn kết hợp range partitioning và hash partitioning.
CREATE TABLE orders (
order_id NUMBER,
customer_id NUMBER,
order_date DATE,
amount NUMBER
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY HASH (customer_id)
SUBPARTITIONS 4 (
PARTITION p1 VALUES LESS THAN (TO_DATE(‘2021-01-01’, ‘YYYY-MM-DD’)),
PARTITION p2 VALUES LESS THAN (TO_DATE(‘2022-01-01’, ‘YYYY-MM-DD’)),
PARTITION p3 VALUES LESS THAN (TO_DATE(‘2023-01-01’, ‘YYYY-MM-DD’)),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
Xem thông tin Partition
select * from dba_tab_partitions where table_name like 'ORDERS';
Quản lý Partition
Xử lý lỗi: nếu bạn insert data vượt ngoài các range mà Partition chúng ta đã định nghĩa bên trên thì khi insert data vào hệ thống sẽ báo lỗi:
— ORA-14400: inserted partition key does not map to any partition
Giải pháp: Sử dụng MAXVALUE
Xử lý: chúng ta phải tạo 1 partition để bao trọn tất cả trường hợp và add vào bên dưới cùng:
Tiếp theo như ở Ví dụ trên: nếu chúng ta chèn thêm bản ghi tháng 10/2024 thì sẽ lỗi ngay lập tức vì dữ liệu không tìm được Partition để lưu:
alter table orders add PARTITION orders_data_default VALUES LESS THAN (MAXVALUE);
=> lúc này nếu có bản ghi ngoài ngày tháng trên thì hệ thống sẽ tự động thêm vào default.
Thêm Partition
Bạn có thể thêm partition mới vào bảng hiện có.
ALTER TABLE orders
ADD PARTITION p5 VALUES LESS THAN (TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’));
Xóa Partition
Bạn có thể xóa partition khi không cần thiết.
ALTER TABLE orders
DROP PARTITION p1;
Di chuyển Partition
Bạn có thể di chuyển partition sang tablespace khác để quản lý hiệu suất hoặc lưu trữ.
ALTER TABLE orders
MOVE PARTITION orders_data20240130 tablespace new_tablespace;
Merge Partition
Bạn có thể gộp hai hoặc nhiều partition lại với nhau.
ALTER TABLE orders
MERGE PARTITIONS p2, p3 INTO PARTITION p23;
Split Partition
Bạn có thể tách một partition thành hai hoặc nhiều partition nhỏ hơn.
ALTER TABLE orders
SPLIT PARTITION p202412 AT (TO_DATE(‘2024-01-02 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
INTO (PARTITION p4a, PARTITION p4b);
Lợi ích của Partitioning
Hiệu suất: Tăng hiệu suất truy vấn bằng cách giảm số lượng dữ liệu cần được quét.
Quản lý dễ dàng hơn: Giúp quản lý và duy trì các bảng lớn dễ dàng hơn.
Tính sẵn sàng cao: Giúp duy trì tính sẵn sàng cao bằng cách chỉ tác động đến partition cần thiết khi thực hiện các thao tác bảo trì.
Khả năng mở rộng: Hỗ trợ xử lý các tập dữ liệu lớn hơn bằng cách chia nhỏ chúng.
Khi nào thì nên thực hiện Partition bảng:
Việc quyết định có nên phân vùng bảng (partitioning) trong Oracle Database hay không phụ thuộc vào nhiều yếu tố, bao gồm dung lượng bảng, cách sử dụng bảng, hiệu suất truy vấn và yêu cầu quản trị. Tuy nhiên, có một số trường hợp điển hình mà việc phân vùng bảng có thể mang lại lợi ích đáng kể:
1. Bảng có dung lượng lớn:
- Khi dung lượng bảng vượt quá 2GB, việc phân vùng có thể giúp cải thiện hiệu suất truy vấn, quản lý và bảo trì. Việc chia nhỏ bảng thành các phân vùng nhỏ hơn giúp Oracle Database dễ dàng quản lý dữ liệu, thực hiện các thao tác I/O hiệu quả hơn và giảm thời gian truy cập dữ liệu.
2. Bảng có dữ liệu lịch sử:
- Nếu bạn thường xuyên truy vấn dữ liệu lịch sử trong bảng, việc phân vùng theo thời gian có thể giúp tối ưu hóa hiệu suất truy vấn. Ví dụ: bạn có thể phân vùng bảng theo năm, quý hoặc tháng để Oracle Database chỉ cần truy cập phân vùng chứa dữ liệu liên quan đến truy vấn, thay vì quét toàn bộ bảng.
3. Bảng có truy vấn thường xuyên truy cập một tập con dữ liệu:
- Nếu bạn có các truy vấn thường xuyên truy cập một tập con dữ liệu cụ thể trong bảng, việc phân vùng theo các thuộc tính dữ liệu đó có thể giúp cải thiện hiệu suất truy vấn. Ví dụ: bạn có thể phân vùng bảng theo khu vực, khách hàng hoặc loại sản phẩm.
4. Bảng cần lưu trữ dữ liệu khác nhau:
- Nếu bảng của bạn cần lưu trữ các loại dữ liệu khác nhau, việc phân vùng có thể giúp bạn quản lý dữ liệu hiệu quả hơn. Ví dụ: bạn có thể phân vùng bảng thành một phân vùng cho dữ liệu cấu trúc và một phân vùng cho dữ liệu không cấu trúc.
5. Bảng cần truy cập đồng thời bởi nhiều người dùng:
- Nếu bảng của bạn cần truy cập đồng thời bởi nhiều người dùng, việc phân vùng có thể giúp giảm tranh chấp khóa và cải thiện khả năng mở rộng. Việc chia nhỏ bảng thành các phân vùng nhỏ hơn giúp phân tán tải truy cập và cho phép Oracle Database xử lý đồng thời nhiều truy vấn hơn.
Partitioning là một tính năng mạnh mẽ của Oracle Database giúp tối ưu hóa hiệu suất và quản lý cơ sở dữ liệu lớn hiệu quả hơn. Bằng cách hiểu và áp dụng các kỹ thuật partitioning phù hợp, bạn có thể cải thiện đáng kể hiệu suất và khả năng quản lý của hệ thống cơ sở dữ liệu của mình.
