Switch In Và Switch Out Với Table Partitioning

Bài viết tham khảo tại: http://www.sqlviet.com/blog/switch-in-va-switch-out-voi-table-partitioning

Một trong những ưu điểm của phân đoạn bảng là bạn có thể dễ dàng loại bỏ một lượng lớn bản ghi ra khỏi bảng, cũng như dễ dàng nhập một lượng lớn bản ghi vào bảng, thay vì phải sử dụng các lệnh INSERT và DELETE.

Ví dụ, bạn có bảng BanHang chứa dữ liệu bán hàng qua nhiều năm. Nay nhận thấy dữ liệu của năm 2010 không còn cần dùng đến thường xuyên, bạn muốn chuyển dữ liệu này sang một bảng khác để giảm nhẹ bảng chính. Nếu không có partition, bạn phải dùng hai lệnh: INSERT vào bảng mới và DELETE trên bảng chính với điều kiện năm = 2010. Hai lệnh này đều rất tốn kém vì mỗi bản ghi được thêm/xóa đều được ghi vào transaction log. Khi bảng được phân đoạn theo năm, bạn có thể dễ dàng chuyển đoạn cho 2010 sang bảng mới. Thao tác này gọi là switch out. Vì đây là thao tác DDL, dữ liệu sẽ được di chuyển tức thì.

Một ví dụ khác, bạn làm việc ở trung tâm dữ liệu của một hội sở ngân hàng, tập hợp dữ liệu của tất cả các chi nhánh. Mỗi cuối tháng, bạn tiếp nhận dữ liệu giao dịch từ các chi nhánh và gộp chung vào một bảng giao dịch chính. Nếu không có partition, bạn cần chạy lệnh INSERT cho từng chi nhánh. Khỏi cần bàn thêm cũng thấy việc này rất tốn thời gian. Tuy nhiên, nếu bạn phân đoạn bảng chính theo chi nhánh (ví dụ phân đoạn theo mã chi nhánh), bạn có thể nạp dữ liệu của từng chi nhánh vào bảng chính thông qua thao tác switch in. Cũng giống như switch out, đây là thao tác DDL nên cần rất ít tài nguyên hệ thống và dữ liệu được nạp ngay tức thì.

Switch out

Tôi dùng lại ví dụ trong bài Table Partitioning – Các Khái Niệm Cơ Bản, và tạo một bảng phân đoạn theo giá trị của cột id, đoạn 1: id nhỏ hơn 1000; đoạn 2: id từ 1001 – 2000; đoạn 3: id từ 2001 – 3000; và đoạn 4: id từ 3001 trở lên.

CREATE DATABASE MyPartDB
GO
 
-- tạo filegroup
ALTER DATABASE MyPartDB ADD FILEGROUP FG1
ALTER DATABASE MyPartDB ADD FILEGROUP FG2
ALTER DATABASE MyPartDB ADD FILEGROUP FG3
ALTER DATABASE MyPartDB ADD FILEGROUP FG4
 
-- thêm data file vào mỗi filegroup
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F1', FILENAME = N'D:\DATA\MyPartDB_F1.ndf') TO FILEGROUP FG1
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F2', FILENAME = N'D:\DATA\MyPartDB_F2.ndf') TO FILEGROUP FG2
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F3', FILENAME = N'D:\DATA\MyPartDB_F3.ndf') TO FILEGROUP FG3
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F4', FILENAME = N'D:\DATA\MyPartDB_F4.ndf') TO FILEGROUP FG4
GO
 
USE MyPartDB
GO
 
-- tạo partition function
CREATE PARTITION FUNCTION MyPartFunc_1(INT) AS RANGE LEFT FOR VALUES (1000, 2000, 3000)
 
-- tạo partition scheme
CREATE PARTITION SCHEME MyPartScheme_1 AS PARTITION MyPartFunc_1 TO (FG1, FG2, FG3, FG4)
 
-- tạo bảng
CREATE TABLE dbo.MyTablePart(
MyID INT IDENTITY PRIMARY KEY,
MyData VARCHAR(100)
) ON MyPartScheme_1(MyID)
 
--thêm dữ liệu vào bảng
INSERT INTO dbo.MyPartTable(MyData)
SELECT DISTINCT a.name
FROM master.SYS.syscolumns a

Nay tôi muốn chuyển toàn bộ dữ liệu của đoạn 1 sang một bảng khác. Tôi tạo một bảng mới và switch out đoạn 1 sang đó:

-- tạo bảng tạm
CREATE TABLE dbo.Tbl_1000 (
MyID INT IDENTITY PRIMARY KEY,
MyData VARCHAR(100)) ON FG1
GO
 
--Switch out. Boom!
ALTER TABLE dbo.MyPartTable SWITCH PARTITION 1 TO dbo.Tbl_1000
 
-- kiểm tra kết quả
SELECT * FROM  dbo.MyPartTable
 
SELECT * FROM  dbo.Tbl_1000

Vậy là dữ liệu đã được chuyển sang bảng Tbl_1000. Ở đây có vài điểm cần lưu ý:

• Bảng tạm phải có cấu trúc giống hệt với bảng chính.
• Bảng tạm phải được tạo trên cùng filegroup với đoạn mà bạn sắp chuyển sang. Ở ví dụ trên tôi cần chuyển đoạn 1 và tôi biết đoạn này nằm trên filegroup FG1 (vì sao tôi lại biết điều này?), nên tôi cũng cần tạo bảng tạm trên filegroup FG1.
• Và tuy không thể hiện trong ví dụ trên, bảng tạm phải trống trước khi switch out. Khi bảng tạm có chứa dữ liệu, SQL Server sẽ báo lỗi chứ không xóa dọn dữ liệu.
• Một điểm nữa là các nonclustered index trên bảng chính (MyPartTable) cũng phải được phân đoạn cùng với bảng. Khi đó index được gọi là aligned. Tôi sẽ viết kỹ hơn về aligned index trong một bài tới, tuy nhiên để bạn hình dung bạn hãy chèn lệnh sau đây vào ngay sau lệnh tạo bảng Table_1000:

  CREATE UNIQUE INDEX ui_Tbl_1000 ON dbo.Tbl_1000(MyData)

  Lệnh trên tạo một non-aligned index, và SQL Server sẽ từ chối thực hiện switch out và trả về một thông báo lỗi.

Switch in

Giả sử giờ tôi muốn chuyển lại dữ liệu trong bảng Tbl_1000 vào đoạn 1 của bảng MyPartTable. Các điều kiện áp dụng đối với switch out cũng vẫn cần được thỏa mãn (đồng thời đoạn cần được chuyển vào phải trống). Ngoài ra switch in còn đòi hỏi một điều kiện nữa, đó là cột trên bảng tạm tương ứng với partition key phải có chứa ràng buộc check theo đó nó chỉ cho phép chứa dải giá trị trùng khớp với đoạn tương ứng. Vì tôi muốn chuyển dữ liệu vào đoạn 1, và tôi biết đoạn 1 chỉ chứa các giá trị <= 1000, nên tôi cần tạo một ràng buộc check tương ứng trên bảng tạm rồi mới có thể switch in:

-- tạo ràng buộc
ALTER TABLE dbo.Tbl_1000 ADD CONSTRAINT ck_id CHECK(MyID <= 1000)
 
-- Switch in. Boom!
ALTER TABLE Tbl_1000 SWITCH TO MyPartTable PARTITION 1

Phiên bản áp dụng: SQL Server 2005 trở lên