Table Partitioning – Split và Merge

Vũ Huy Tâm

Khi định nghĩa partition function bạn phải chỉ rõ giá trị biên của từng đoạn và theo đó, số lượng các đoạn cũng được xác định. Qua thời gian sẽ đến lúc bạn cần định nghĩa thêm các đoạn mới cho dữ liệu được thêm vào sau này. Ví dụ bạn phân đoạn bảng bán hàng theo năm của ngày giao dịch, tại thời điểm ban đầu bạn chia bảng làm 3 đoạn cho các năm 2009 trở về trước, 2010, và 2011 trở đi. Tại một thời điểm nào đó trước năm 2012 bạn cần tạo thêm đoạn mới cho năm 2012, và tương tự sau này cho 2013… Thao tác này gọi là split (chia đoạn).
Ngược lại, bạn cũng có thể muốn hợp các đoạn chứa dữ liệu lại với nhau để giảm bớt số lượng đoạn cần quản lý, hoặc đáp ứng yêu cầu thay đổi về nghiệp vụ (ví dụ Hà Tây nhập vào Hà nội). Với bảng bán hàng ở trên, có thể bạn muốn hợp hai đoạn 2009 và 2010 để gom tất cả dữ liệu cũ từ năm 2010 trở về trước vào một đoạn. Thao tác này gọi là merge (hợp đoạn). Lưu ý là partition merge khác với lệnh MERGE được đưa vào từ bản 2008.
Setup
Tôi dùng lại database trong bài Switch In Và Switch Out Với Table Partitioning và tạo thêm các đối tượng mới:

USE MyPartDB
GO
CREATE PARTITION FUNCTION MyPartFuncD(DATETIME) AS RANGE RIGHT FOR VALUES ('2009-01-01', '2010-01-01', '2011-01-01')
GO
CREATE PARTITION SCHEME MyPartSchemeD AS PARTITION MyPartFuncD TO (FG1, FG2, FG3, FG4)
GO
CREATE TABLE dbo.BanHang(
BanHang_ID INT IDENTITY,
Ngay DATETIME,
MaSP VARCHAR(10),
SoLuong INT,
ThanhTien INT,
CONSTRAINT PK_BanHang PRIMARY KEY (BanHang_ID,Ngay)
) ON MyPartSchemeD(Ngay)
GO
 
--thêm dữ liệu vào bảng
INSERT INTO dbo.BanHang(Ngay,MaSP,SoLuong,ThanhTien)
SELECT '2008-03-25 19:26:12','100',2,500000 
UNION ALL
SELECT '2009-06-12 07:14:27','200',1,300000 
UNION ALL
SELECT '2010-01-22 10:01:44','300',3,600000 
UNION ALL
SELECT '2011-10-26 12:06:42','400',1,1000000 
UNION ALL
SELECT '2012-02-17 15:32:16','500',5,400000 
GO
--kiểm tra dữ liệu lưu vào các đoạn
SELECT OBJECT_ID, index_id, partition_number, ROWS
FROM SYS.partitions WHERE OBJECT_NAME(OBJECT_ID)='BanHang'
------------------------------
object_id   partition_number   rows
229575856	       1       1
229575856	       2       1
229575856	       3       1
229575856	       4       2

Như vậy bảng được lưu trữ đúng như mong đợi: bản ghi 2008 vào đoạn 1, bản ghi 2009 vào đoạn 2, bản ghi 2010 vào đoạn 3, còn hai bản ghi 2011 và 2012 vào đoạn 4. Số bản ghi của mỗi đoạn (cột “rows”) phản ánh điều này.

Split
Nay bạn muốn tạo thêm một đoạn cho 2012, bạn cần làm theo các bước như sau:

--bước 1: tạo thêm 1 filegroup mới để chứa đoạn sắp được tạo
ALTER DATABASE MyPartDB ADD FILEGROUP FG5
GO 
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F5', FILENAME = N'D:\DATA\MyPartDB_F5.ndf') TO FILEGROUP FG5
GO
--bước 2: mở rộng thêm partition scheme
ALTER PARTITION SCHEME MyPartSchemeD NEXT USED FG5
GO
--bước 3: định nghĩa thêm giá trị biên trong partition function
ALTER PARTITION FUNCTION MyPartFuncD() SPLIT RANGE ('2012-01-01')
GO
--Thêm một số bản ghi mới và kiểm tra dữ liệu lưu vào các đoạn
INSERT INTO dbo.BanHang(Ngay,MaSP,SoLuong,ThanhTien)
SELECT '2012-03-12 14:25:12','100',3,750000 
UNION ALL
SELECT '2012-06-21 07:24:12','200',1,300000 
GO
SELECT OBJECT_ID, index_id, partition_number, ROWS
FROM SYS.partitions WHERE OBJECT_NAME(OBJECT_ID)='BanHang'
object_id   partition_number   rows
229575856	       1       1
229575856	       2       1
229575856	       3       1
229575856	       4       1
229575856	       5       3

Lưu ý ở bước 2, bạn yêu cầu đoạn tiếp theo sẽ sử dụng filegroup FG5 qua mệnh đề “NEXT USED FG5″. Ở bước 3, bạn tạo thêm một giá trị biên trong partition function, theo đó miền từ 2011-01-01 đến ngày tận thế được cắt tại 2012-01-01. Do đó đoạn 4 giờ được điều chỉnh để chứa dải giá trị từ 2011-01-01 đến trước 2012-01-01, và đoạn mới chứa dải từ 2012-01-01 đến ngày tận thế.
Vậy là đoạn mới đã được tạo (partition number 5), bản ghi 2012 cũ được chuyển vào đây và hai bản ghi 2012 mới cũng được lưu vào đoạn này.

Merge
Việc hợp đoạn đơn giản hơn, để hợp đoạn 2009 vào 2010 bạn chỉ cần thực hiện lệnh sau

ALTER PARTITION FUNCTION MyPartFuncD() merge RANGE ('2009-01-01')

Hiểu một cách đơn giản là xóa đi giá trị biên 2009-01-01 và do đó tất cả các giá trị từ trước đến 2010-01-01 được lưu chung vào một đoạn. Vậy đoạn nào được dùng? Trước khi hợp, đoạn 1 lưu vào filegroup FG1, đoạn 2 lưu vào filegroup FG2. Sau khi hợp, đoạn 1 và đoạn 2 được gộp chung vào thành đoạn 1. Vậy đoạn 1 mới bây giờ được lưu ở FG1 hay FG2? Xin nhường cho bạn tìm câu trả lời.

Lưu ý
Không giống như Switch in và Switch out vốn chỉ thay đổi metadata trong database, Split và Merge kéo theo sự di chuyển của dữ liệu. Các bản ghi sẽ được chuyển sang đoạn mới thích hợp với giá trị partition key của nó. Trong ví dụ split ở trên, bản ghi 2012 ban đầu ở đoạn 4 được chuyển sang đoạn 5; còn ở ví dụ merge hai đoạn 2009 và 2010, các bản ghi ở một đoạn được chuyển qua đoạn kia. Điều này có thể gây kéo dài thời gian thực hiện khi bảng có lượng bản ghi lớn.




Tags: , , ,

14 Comments
Posted on 26/10/2011 | Categories: Table partitioning

Các bài viết tương tự

Comments
  • Vehicles (09/12/2011 12:34 am)

    Cho mình hỏi:

    + Trường hợp bảng dữ liệu dbo.BanHang đã tồn tại và có dữ liệu.

    Muốn dùng cái Partition Tables như bạn Post ở trên. Có nghĩa là mình sẽ không có đoạn

    CREATE TABLE

    nhé….!

    Liệu khi Insert dữ liệu, dữ liệu có chuyển vào từng FG theo điều kiện Ngay không?

    + Đoạn Split và Merge mình thấy rất hay, rất thực tế. Sẽ linh động khi khách hàng có yêu cầu thay đổi sau 1 thời gian sử dụng Partition Table.

    Tks bạn.

    • Vũ Huy Tâm (14/12/2011 9:13 am)

      Việc đầu tiên bạn cần làm là phân đoạn bảng đó. Để phân đoạn 1 bảng có sẵn, một cách thường làm là bạn tạo lại clustered index trên partition scheme (hic, hiện tại tôi không có code sẵn)

  • Shino Riz (11/01/2012 11:41 pm)

    Anh Tâm cho hỏi là: Nếu mình dùng Merge 1 vùng partition rồi, nay muốn dùng split đễ dùng lại file group cũ và file cũ được không?(tức là làm cho nó quay lại như lúc đầu và cũng sửa dụng file group cũ)
    Thanks Anh Tâm! Mong sớm nhận tin từ anh

  • PhaoThu (05/11/2012 5:59 am)

    Hi a,

    Chỗ tạo table và constraint Primary key, bắt buộc phải bao gồm 2 cột (BanHang_ID,Ngay) tại sao lại như vậy ?, có cách nào vẫn giữ primary key là một cột BanHang_ID hay không ?

    • PhaoThu (05/11/2012 12:02 pm)

      Hi a ,

      Em xin đổi lại câu hỏi là, giả sử khi tạo bảng dbo.BanHang ở trển em không tạo primary key, nhưng sau đó em tạo ra một cluster index cho cột BanhHang_ID chẳng hạn thì không làm cách nào mà switch dữ liệu ra được nữa cứ báo lỗi liên quan tới index gì đó. Anh có thể nói giải thích thêm về vấn đề này không.

      Thanks

      • Vũ Huy Tâm (05/11/2012 12:23 pm)

        Tôi đoán chắc là có non aligned index, khi muốn switch in/out thì tất cả các index đều phải aligned với bảng. Nếu bạn post lên cấu trúc bảng và index thì tôi có thể hình dung rõ hơn

  • phiphily (22/03/2013 3:33 am)

    Anh cho em hỏi là khi mình phân chia filegroup để chứa các file .ndf dùng để chứa datafile theo từng đoạn như vậy thì primary lúc này sẽ chứa gì? Và khi backup Primary tức file .mdf dùng làm gì?

  • newmem (09/12/2013 12:16 am)

    Cho mình hỏi cách lấy dữ liệu trên các phân đoạn thế nào vậy bạn?

  • hant (08/04/2014 12:52 am)

    Thank bài viết của a!
    khi nào a có code partition table có sẵn thì up lên a nha!

  • Khánh (31/08/2014 11:52 pm)

    Vậy đoạn 1 mới bây giờ được lưu ở FG1 hay FG2? Xin nhường cho bạn tìm câu trả lời.
    Chưa ai trả lời, mình trả lời thử nhờ admin góp ý nha:
    -Dữ liệu sẽ được chuyến sang FG1
    - Trước khi thực hiện, mọi người có thể vào Sql server management studio, nhấp phải chọn database –> report –> standard report –> disk use by partition để xem dữ liệu trước và sau khi merge để kiểm tra kết quả.

    • Vũ Huy Tâm (04/09/2014 2:55 pm)

      Đúng rồi, nhưng ý mình muốn hỏi là tại sao lại như vậy. Câu này hơi chuối. Với tham số RANGE (’2009-01-01′) trong lệnh, giá trị biên ’2009-01-01′ thuộc về đoạn 2 vì PARTITION FUNCTION này là RIGHT. Lệnh merge trên sẽ xóa sổ đoạn 2 và hợp nó vào đoạn 1, vì thế FG1 nay được sử dụng.

  • Vượng (16/12/2014 3:00 am)

    Mình đang tìm hiể cái này. Cũng đã chia bảng ra làm 3 đoạn rồi.
    Giờ mình muốn sử dụng đoạn đầu tiên thì làm thế nào.
    Ví dụ bảng SinhVien mình chia ra làm 3 đoạn và mình chỉ muốn sử dụng đoạn đầ tiên với 200 sinh viên đầu tiên thôi thì làm sao?
    Mình viết câu lệnh select thế nào vậy?

  • Beginer DBadmin (15/01/2015 1:01 pm)

    Cho mình hỏi nếu trong trường hợp thêm một lúc nhiều partition thì Bước 2 và bước 3 thực hiện như thế nào? mong các bác chỉ giúp.

  • Giang (28/08/2017 1:10 pm)

    Bạn cho hỏi làm sao để phục hồi lại bảng như ban đầu sau khi đã phân vùng vậy ?

Leave a Reply

Hướng dẫn: Để nhập mã T-SQL bạn dùng thẻ <pre lang="tsql"> và </pre>.
Ví dụ: <pre lang="tsql">SELECT * FROM MyTable</pre>