[SQL Server]如何將現有table調整為partition table

有關資料庫的討論, 都可以在這發表哦~~
回覆文章
頭像
tim
文章: 1379
註冊時間: 2008年 11月 26日, 00:49

[SQL Server]如何將現有table調整為partition table

文章 tim »

網路上找到大部份的資料都是有關於如何建立一個 table 是 partition table 而非將已存在的現有 table 調整為 partition table, 本文將進行較仔細的說明來介紹這件事. 當然是在 SQL Server 2005 以上版本才適用.

首先要說明的是, 當然可以建立一個新的 partition table 後, 利用 insert into xxxx select xxx from xxx 的方式將舊table (無partition的)轉移到 partition table 上後, 再利用 sp_rename 的方式, 將兩個 table 改名也可以完成. 但如何"直接"做到在原 table 上達成這件事, 將是本文的探討範圍. (新建的方式可以參考這篇資料: http://www.blueshop.com.tw/board/show.a ... 6152735ZFS)

我們先來建立 lab 吧, 首先當然就是建個資料表, 並且塞入一些資料, 如下:

代碼: 選擇全部

--create table
create table tblUsers
(sid int primary key, username nvarchar(50), birthday datetime)

--insert data
INSERT tblUsers (sid, username, birthday) VALUES (1, N'jack', '1980-02-03 00:00:00.000')
INSERT tblUsers (sid, username, birthday) VALUES (2, N'tim', '1992-01-17 00:00:00.000')
INSERT tblUsers (sid, username, birthday) VALUES (3, N'tom', '1988-03-07 00:00:00.000')
INSERT tblUsers (sid, username, birthday) VALUES (4, N'max', '1966-02-01 00:00:00.000')
INSERT tblUsers (sid, username, birthday) VALUES (5, N'mary', '1992-03-15 00:00:00.000')
INSERT tblUsers (sid, username, birthday) VALUES (6, N'kenny', '1987-06-05 00:00:00.000')
INSERT tblUsers (sid, username, birthday) VALUES (7, N'mag', '1994-05-12 00:00:00.000')
INSERT tblUsers (sid, username, birthday) VALUES (8, N'john', '1995-03-07 00:00:00.000')
INSERT tblUsers (sid, username, birthday) VALUES (9, N'david', '1976-03-05 00:00:00.000')

--select to check if data is ready
select * from tblUsers
好的, 應該有9筆資料沒問題了, 接下來就是一般要做 partition table 時, 要建立的 partition function 建立, 假設我們想做的是每10年一個區間, 所以就用 1980, 1990 做為切分點, 由於是 <1980/1/1, >=1980/1/1 and <1990/1/1, >=1990/1/1 三個區間, 而且應該是"右"切點, 所以使用建立 partition function 指令如下: (若要知道 RIGHT 及 LEFT 的不同, 請參閱: http://technet.microsoft.com/zh-tw/libr ... 87802.aspx)

代碼: 選擇全部

create partition function pf_DateRange (datetime)
as range right for values ('1980/1/1', '1990/1/1')
建立好 partition function 後, 就要建立 partition scheme, 由於 partition scheme 是存放各區間的資料儲存區, 所以需要先建立好 filegroup 及對應的 file, 像上面有三個區間, 代表著, 至少要有三個 filegroup 來放, filegroup 及 file 可以透過 ui 操作比較方便直覺, 不過用指令也 ok 的, 如下:

代碼: 選擇全部

ALTER DATABASE bb ADD FILEGROUP fg1 
ALTER DATABASE bb ADD FILE (NAME = 'fg1', FILENAME = 'D:SQLDatabb_fg1.NDF') TO FILEGROUP fg1 
ALTER DATABASE bb ADD FILEGROUP fg2
ALTER DATABASE bb ADD FILE (NAME = 'fg2', FILENAME = 'D:SQLDatabb_fg2.NDF') TO FILEGROUP fg2 
ALTER DATABASE bb ADD FILEGROUP fg3
ALTER DATABASE bb ADD FILE (NAME = 'fg3', FILENAME = 'D:SQLDatabb_fg3.NDF') TO FILEGROUP fg3 
其中 [bb] 這個是 database name 啦, 而後面的 FILENAME 要給定到目錄的層級, 而 ndf 副檔名為 secondary data file (次要資料檔案), 看實體存放位置為何, 若要和原來的 mdf 位置相同, 可以使用指令

代碼: 選擇全部

select * from sysfiles
來看 mdf 的所在位置, 就可以指定到對應的路徑的 ndf 檔, 檔名當然可以隨自己喜好來命名, 不過要容易辨識的話, 還是要命名好才行.

再來的 partition schema 就可以建立了, 如下:

代碼: 選擇全部

CREATE PARTITION SCHEME ps_DateRange
AS PARTITION pf_DateRange
to (fg1, fg2, fg3)
這樣已經完成的準備動作了, 我們先進行原始資料確認, 再執行一次

代碼: 選擇全部

select * from tblUsers
應該會得到如下畫面:
j1.jpg
j1.jpg (33.85 KiB) 已瀏覽 6524 次
接下來就是重頭戲了, 利用 alter table drop constraint 的語法來進行 partition table 原 table 升級的做法, 為什麼是 drop constraint 呢, 因為原來的資料是利用 clustered index (通常是 primary key, 本例會是預設名稱 PK__tblUsers__7F60ED59, 可以利用 sp_help tblUsers 指令來查, 在最下方的 primary key (clustered) 會秀出, 如圖:
j2.jpg
j2.jpg (43.8 KiB) 已瀏覽 6524 次
)來存放位置, 但要利用 partition table 的方式來存放時, 就得打掉原來的 clustered index constraint 才行, 而配合 move to 指令來將 table 放置資料的方式改掉(注意: 不是拿掉或改掉 primary key, 而是拿掉 clustered index 這件事), 指令如下:

代碼: 選擇全部

alter table tblusers DROP CONSTRAINT PK__tblUsers__7F60ED59 WITH (MOVE TO ps_DateRange (birthday))
其中, 拿掉的 contraint 是指 clustered index, 而 move to 是移到 ps_DateRange 這個 partition schema 上, 而且是 birthday 欄位這個條件, 接下來有趣的事就發生了, 資料會重放回各 file group, 而且順序會變, 如何先知道資應該在哪個對應的 filegroup 上呢(只是利用 partition function 將欄位放入去執行得到的結果, 而非真實放置狀況), 可以利用指令:

代碼: 選擇全部

select *, $PARTITION.pf_DateRange(birthday) from tblUsers
得到如下結果:
j4.jpg
j4.jpg (43.07 KiB) 已瀏覽 6523 次
其中的第4個欄位, 就是 filegroup 順序的代碼, 可以明確的了解, birthday 在 1966 及 1976 的兩筆資料是在 filegroup 1, 其餘也都放置完成在對應的位置上了, 不過此時的 primary key 的確會消失, 也只能再重新建立, 以避免發生問題!

建立要注意 primary key 為 non-clustered index 才可以哦, 因為已經是 partition table 了, 所以不能再有 clustered index 來影響 partition table 的存放, 所以 primary key 建立方式如下:

代碼: 選擇全部

ALTER TABLE dbo.tblUsers ADD CONSTRAINT	PK_tblUsers PRIMARY KEY NONCLUSTERED (sid) ON [PRIMARY]
另外若是要確認真實的資料存放筆數於各 Filegroup 的狀況, 可以利用以下指令(確認資料真實存放於各partition的筆數狀況):

代碼: 選擇全部

SELECT ps.partition_number
,ps.row_count 
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('tblUsers')
執行結果如下:
j6.jpg
j6.jpg (17.93 KiB) 已瀏覽 6502 次
這樣對於實際的放置內容應該有比較明確的了解方式.

是不是很容易呢? 但若是要還原回來或是加入新的 filegroup 利用 split 指令或是合併兩個區間的 merge 指令, 都可以再試看看囉, 之後有時間再來整理囉!
多多留言, 整理文章, 把經驗累積下來.....
arvin
文章: 21
註冊時間: 2009年 2月 16日, 15:00

Re: [SQL Server]如何將現有table調整為partition table

文章 arvin »

文章補充:
若要再新增加Partition 的做法
1. 增加新的filegroup
ALTER DATABASE bb ADD FILEGROUP fg4
ALTER DATABASE bb ADD FILE (NAME = 'fg4', FILENAME = 'D:SQLDatabb_fg4.NDF') TO FILEGROUP fg4

2. 增加新的partition scheme
ALTER PARTITION SCHEME ps_DateRange
NEXT USED fg4

3. 增加新的切割區間
ALTER PARTITION FUNCTION pf_DateRange()
SPLIT RANGE ('1995/1/1');

在這邊有一件事要注意的,雖然所有文章都寫到可以隨增修Pratition,但實際操作上建議在新區間資料未寫入前就開啟新的partition 給table 使用,因為若資料已有寫入後,在執行ALTER PARTITION FUNCTION pf_DateRange()時會花費很久搬移的時間。
回覆文章