Partition Bảng Đã Có Sẵn

Vũ Huy Tâm

Trong loạt bài về phân đoạn bảng và index, tôi đã giới thiệu về kiến trúc phân đoạn của SQL Server. Các ví dụ nêu trong các bài đó đều sử dụng bảng được tạo lần đầu và áp dụng phân đoạn lên nó. Một câu hỏi thường gặp là, làm thế nào để phân đoạn một bảng đã có sẵn dữ liệu? Bảng này có thể chưa được phân đoạn, hoặc đã được phân đoạn theo một cách khác, nay bạn muốn phân đoạn lại theo cách hợp lý hơn.
Có một cách làm thực ra rất đơn giản mà nếu bạn để ý một tẹo là thấy ngay, đó là trong SQL Server clustered index chính là bảng, nói cách khác là bảng được lưu trữ thành một cây index và cây này chính là clustered index. Vì vậy để phân đoạn bảng bạn chỉ việc phân đoạn clustered index của nó, bằng cách tạo lại clustered index trên partition scheme.
Trong trường hợp phổ biến, clustered index thường đi liền với primary key. Ở ví dụ dưới, giả sử bạn đã có bảng BanHang với primary key và clustered index trên trường ID, bảng này không được phân đoạn.

CREATE TABLE dbo.BanHang(ID INT IDENTITY NOT NULL, 
NgayGD DATETIME NOT NULL, 
MaSP INT NOT NULL,
SoLuong INT NOT NULL, 
Gia INT NOT NULL,
CONSTRAINT PK_BanHang PRIMARY KEY CLUSTERED (ID)
)

Nay bạn muốn phân đoạn bảng, có hai khả năng xảy ra:
(1) bạn phân đoạn bảng trên trường ID, nghĩa là vẫn giữ nguyên định nghĩa của primary key và clustered index (giữ nguyên trường cũ) chỉ muốn phân đoạn nó ra, dựa vào partition scheme PScheme_ID. Bạn chỉ việc tạo lại index như sau:

CREATE UNIQUE CLUSTERED INDEX PK_BanHang ON dbo.BanHang(ID) 
WITH (DROP_EXISTING = ON)
ON PScheme_ID(ID)

Điểm chính của đoạn lệnh trên là mệnh đề “WITH (DROP_EXISTING = ON)”, nó sẽ tự động xây dựng lại index thay vì bạn phải xóa đi rồi tạo lại.
(2) Bạn muốn phân đoạn bảng trên một trường khác, ví dụ NgayGD. Vì trường partition key cần phải có mặt trong clustered index, bạn cần tạo lại clustered index (và primary key) trong đó phải chứa thêm trường NgayGD. Vì định nghĩa của primary key và clustered index khác so với trước, bạn không thể dùng WITH (DROP_EXISTING = ON) được nữa. Nếu cố tình chạy đoạn lệnh này:

CREATE UNIQUE CLUSTERED INDEX PK_BanHang ON dbo.BanHang(ID,NgayGD) 
WITH (DROP_EXISTING = ON)
ON PScheme_NGD(NgayGD)

Bạn sẽ gặp lỗi:
Cannot recreate index ‘PK_BanHang’. The new index definition does not match the constraint being enforced by the existing index.
Thay vào đó, bạn cần xóa đi và tạo lại:

ALTER TABLE BanHang DROP CONSTRAINT PK_BanHang
GO
CREATE UNIQUE CLUSTERED INDEX PK_BanHang ON dbo.BanHang(ID,NgayGD) 
ON PScheme_NGD(NgayGD)
--hoặc
ALTER TABLE dbo.BanHang ADD CONSTRAINT PK_BanHang PRIMARY KEY CLUSTERED (ID,NgayGD) ON PScheme_NGD(NgayGD)

Lý do tại sao cần 2 trường ID và NgayGD trong index là vì ID cần thiết để duy trì tính duy nhất, và NgayGD cần có mặt để làm partition key.
Trong trường hợp ít phổ biến hơn, clustered index không đi kèm với primary key, hay nói tổng quát là nó đứng một mình không gắn với một constraint nào. Khi đó bạn có thể tạo lại clustered index với bổ sung thêm trường mới mà vẫn dùng được mệnh đề “WITH (DROP_EXISTING = ON)”:

CREATE TABLE dbo.BanHang(ID INT IDENTITY NOT NULL, 
NgayGD DATETIME NOT NULL, 
MaSP INT NOT NULL,
SoLuong INT NOT NULL, 
Gia INT NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX CI_BanHang ON dbo.BanHang(ID) 
GO
CREATE UNIQUE CLUSTERED INDEX CI_BanHang ON dbo.BanHang(ID,NgayGD) 
WITH (DROP_EXISTING = ON)
ON PScheme_NGD(NgayGD)



Tags: ,

14 Comments
Posted on 31/10/2013 | Categories: Database Administration, Index, Table partitioning

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

Comments
  • ecompc (15/11/2013 12:30 am)

    Thank a, em chờ bài viết này đã lâu, và đã thử áp dụng , nhưng kết quả trả ra em vẫn ko hiểu lắm, mong anh xem hình và giải thích 1 số chỗ giúp ạ.

    https://dl.dropbox.com/s/2flonswevxco44i/Screenshot%202013-11-15%2011.21.40.png

    Em có chia bảng ra theo cột Date, Trước năm 2006 thì vào 1 đoạn, và Quý 1 năm 2006 vào 1 đoạn, và cứ thế Q2 Q3..

    Em đã thử select và count lại số dòng thì thấy đúng tất cả các quý.

    NGOẠI TRỪ đoạn trước năm 2006, em thấy nó có 3 INDEX 1 2 3, và mỗi INDEX là số dòng khác nhau,
    5151 là số dòng đúng trước năm 2006

    còn 121317 là TỔNG SỐ DÒNG BAN ĐẦU CHƯA PHÂN ĐOẠN.

    Vậy là thế nào hả anh??

    Còn nếu trong trường hợp Tạo bảng trước rồi phân đoạn, thì không gặp tình trạng trên

    RẤT mong anh giải đáp giúp

    • ecompc (15/11/2013 12:49 am)

      Còn 1 vấn đề nữa là file DATA ban đầu (mdf) có dung lượng 200Mb

      Sau đó mình Partition 1 vài bảng ra, thì cho e hỏi là dung lượng file mdf có giảm không?
      vì khi partition mình tạo ra file ndf cũng đã chiếm dung lượng rồi.

    • Vũ Huy Tâm (18/11/2013 11:47 am)

      Hai index kia (index id =2 và 3) là nonclustered index và mình nghĩ chúng không được phân đoạn (partition id = 1 một mình nó 1 đoạn) nên số dòng chính bằng số bản ghi của bảng. Bạn kiểm tra xem 2 index này là gì:

      SELECT name FROM SYS.indexes WHERE OBJECT_ID = 'copy_object_id_here' and index_id = 2

      Về dung lượng file, sau khi partition thì lượng dữ liệu ở mdf sẽ giảm xuống vì được san bớt sang các file khác, nhưng kích thước file vẫn giữ nguyên trừ khi bạn shrink file.

      • ecompc (18/11/2013 10:54 pm)

        Thank a đã trả lời,
        Bảng này nó có 2 noncluster index là rowguid, và productid

        Còn lúc mình phân đoạn thì mình chỉ xóa mỗi cái index cluster SalesOrderID, và SalesOrderDetailID

        Vậy nếu muốn phân đoạn đúng thì mình phải xóa 2 cái cluster kia đi hay sao ạ??
        Hay là phải làm thế nào để nó không bị tình trạng như lúc trước

        đây là cấu trúc bảng lúc mình chưa phân đoạn, và em muốn partition theo ModifiedDAte, theo cách cũ là em kéo nó zô SalesOrderID, và SalesOrderdetailID làm noncluster

        https://dl.dropbox.com/s/ims16cc3nujt664/Screenshot%202013-11-19%2009.52.12.png

  • tung (18/02/2014 6:03 am)

    Em muốn phân đoạn trên trường NgayGD nhưng không muốn cho nó làm PRIMARY KEY được không bác ? Tại vì như thế thì lại thành 1 cặp khóa chính rồi (ID và NgayGD ), em muốn khóa chính chỉ là trường ID thôi còn phân đoạn trên NgayGD .

    • ecompc (19/02/2014 12:37 am)

      không đc bạn nhé.

      • tung (26/02/2014 4:48 am)

        Tạo như vậy bảng không có khóa ngoại thì không sao , nếu có khóa ngoại lại phải thêm trường Date ở khóa ngoại . Hơi rắc rối nhỉ ?

  • Moon (16/10/2014 11:18 pm)

    Anh Tâm ơi anh cho em hỏi:
    Hiện tại em đang có 1 bảng tbl_MatHang, có khóa chính là cột MaMatHang.

    Giờ em muốn partition trên cột NgayCapNhat, nhưng em ko muốn đổi lại khóa chính mà vẫn muốn giữ khóa chính chỉ có cột MaMatHang(do khóa chính này là khóa ngoại ở rất nhiều bảng khác).

    Vậy làm sao để em có thể drop clustered index trên cột MaMatHang và chuyển clustered index sang cột NgayCapNhat. Vì hiện tại nếu em drop clustered index thì nó xóa luôn cả khóa chính.

    Mong anh và mọi người giúp đỡ.

  • Vũ Huy Tâm (23/10/2014 8:27 am)

    Theo mình bạn nên tạo khóa chính bao gồm hai trường là MaMatHang + NgayCapNhat, và tạo unique non-clustered index trên MaMatHang làm khóa ngoại cho các bảng khác. Vì không thể sửa được khóa chính nên bạn cần xóa và tạo lại, tương tự cũng không thể đổi một index từ clustered thành non-clustered nên bạn cũng cần khóa để tạo lại.
    Bạn có thể đổi tên bảng tbl_MatHang thành tên khác, ví dụ tbl_MatHang_1, sau đó tạo một bảng trống tbl_MatHang theo định nghĩa mới, rồi đổ dữ liệu từ tbl_MatHang_1 sang.

    * Partition thường chỉ cần cho các bảng lớn, nếu bảng này chỉ là danh sách các mặt hàng thì có thể không đủ lớn để cần phải partition.

    • Moon (24/10/2014 3:15 am)

      Dạ. Em cảm ơn anh.
      Em đã làm được rồi. Em lấy ví dụ là bảng mặt hàng thôi chứ thật sự làm em cần partition bảng phiếu nhập, phiếu xuất, phiếu giao nhận. Do là database của xưởng chế tác vàng trang sức nên dữ liệu giao nhận, nhập xuất hàng ngày sẽ rất nhiều.

      Cảm ơn anh đã giúp đỡ. ^^

  • TMQUAN (28/11/2014 4:23 am)

    Chào Tâm,

    Mình có vài câu hỏi như sau :

    1.Bảng có dữ liệu lớn như thế nào mới thì mới nên dùng partition.
    2. Một bảng có khoảng 100 triệu record, trung bình 1 tháng có thêm 10 triệu thì nên partition theo tháng hay theo năm? Bảng này hiện đang dùng để report theo tháng.
    3. Vd khi đã tạo partition function , partition scheme và filegroup cho bảng này theo tháng. Vậy thì mỗi tháng mình lại tạo lại partition function , partition scheme và filegroup để tiếp tục phân đoạn hay sao, có cách nào auto không?

    Mong Tâm có phản hồi sơm
    Xin cảm ơn

    TMQUAN

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>