Switch In Và Switch Out Với Table Partitioning

Vũ Huy Tâm

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

Tags: , , ,

7 Comments

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

Comments
  • bongbingo (12/09/2011 2:38 am)

    Bài viết rất hữu ích cho những ai đang manage database có dung lượng lớn :) .

    Mình thì đang quản lý website data cỡ chừng 45k records nên cũng chưa cần add favourite cái đã

  • ecompc (03/10/2013 4:36 am)

    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.

    => cho mình hỏi là bảng BAN ĐẦU khi tạo ra nó nằm ở filegroup nào ạ, có phải primary không???

    • tung (20/02/2014 11:43 pm)

      Nếu bạn không chon FG thì mặc định nó là FG primary

  • Partitioning existed table (13/05/2014 11:53 pm)

    Hi bác Tâm,

    Bác cho em hỏi là em muốn partitioning existed table , nhưng table này là table dynamic, nghĩa là nó sẽ được delete và create lại do em dùng 1 tool ETL để làm. Tương tự như table thì store procedure cũng dynamic, nên khi em apply switch in và switch out trên store procedure thì rất tốn công maintain do khi nào add/remove column thì cái store được gernate lại . Vậy em có cách nào để làm switch in và switch out data để chia data history ko?

  • sau (10/08/2014 5:40 am)

    Hi anh Tâm,

    Em có 1 thắc mắc là thay vì partition theo năm, e partition theo tháng. vậy khi sang tháng 1 của năm mới, e mún split partition cuối thành 12 parition thì phải làm sao a? e thấy hàm split chỉ hỗ trợ thêm 1 biên mới (ví dụ thêm biên 1/2015 thay vì 1/2015, 2/2015, 3/2015 … )

    • Denis (03/07/2015 12:10 pm)

      My wife and i were so delighted John maaegnd to deal with his investigation using the precious recommendations he gained when using the web site. It is now and again perplexing just to possibly be giving for free secrets and techniques which other people have been trying to sell. We recognize we have got you to thank for this. The type of explanations you’ve made, the simple site menu, the friendships you will help to create it is mostly excellent, and it’s aiding our son and us reason why the topic is thrilling, and that is highly fundamental. Many thanks for everything!

  • du (11/05/2015 3:20 am)

    ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table ‘AdventureWorks2008.dbo.new_table’ allows values that are not allowed by check constraints or partition function on target table ‘AdventureWorks2008.dbo.table’.

    a cho e hỏi e bị lỗi này là sao a, script của e đây
    https://app.box.com/s/jl3wp9x0afrnno1sm4duaaapqe9c6akh

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>