黃昏的甘蔗

寫了 Partition Table （ 『SQL』分割資料表 Partition Table），卻沒交代 Partition Function (PF) 與 Partition Scheme (PS) 怎麼來的，一整個覺得怪怪的，總覺得少了什麼，所以再寫了這篇，簡單說明一下 PF 與 PS 的寫法。

要寫總要有些測試資料，就從 AdventureWorks2008 做一下

一開始有504筆測試資料，我將以他的「ReorderPoint」作為切割的依據

第1步：建立切割函數 Partition Function

CREATE PARTITION FUNCTION [pf_product](smallint) AS RANGE RIGHT FOR VALUES (300, 500, 700)

 

先觀察一下你想要切割的欄位，例如我是用「ReorderPoint」 作為切割，這個欄位型態為 「smallint」，所以在定義 PF 時就會宣告為 (smallint)

第2步：建立分割配置 Partition Scheme

CREATE PARTITION SCHEME [ps_product] AS PARTITION [pf_product] TO ([fg1], [fg2], [fg3], [fg4])

 

將 PF 的切割方法 apply 到檔案群組 File Groups

第3步：在真正對 data 做資料分群時，可以先測試看看 PF 與 PS 是否設定妥當，也就是資料是否能如願的平均分攤到每個檔案群組（這時只是測試，還沒分割唷）

SELECT  $partition.pf_product(ReorderPoint) as 分割區塊, count(ProductID) AS 筆數

FROM dbo.product

group by $partition.pf_product(ReorderPoint)

order by 1

 

覺得ok了，便可以真的對資料做分群囉

SSMS Step 1:在 SQL 管理介面

SSMS Step 2:選擇欄位

SSMS Step 3:選擇 PF

SSMS Step 4:選擇 PS

SSMS Step 5:決定 Boundary，預設為 PF 所指定之值

SSMS Step 6:立即執行，當然你也可以產生 Script 出來

SSMS Step 7:最後確認

SSMS Step 8:執行成功完成

SSMS Step 9:來個小段 T-SQL 測試一下，利用 System View 「sys.Partitions」

由於畫面跟前一篇完全一樣，所以這篇都只用部分截圖。

--------------------

最後來看一下執行計畫（不懂請看  執行計畫 Execution Plan ）。底下有兩張圖分別為一個有用 partition 另一個則無，執行 Cost 分別為 0.01590 與 0.00828，哪個快？當然是 Cost 小的囉！

哪個是有用 Partition ？答案是 0.01590 的執行

怎麼越切割執行效率越差呢？其原因是筆數不大時（本例為504筆），用了分割，反而多花時間從每個分割區將資料撈出再統合！

～ End

那......其 apply 到 Table 的 partition script 長什麼樣子呢？

USE [PTdb]

GO

BEGIN TRANSACTION

CREATE CLUSTERED INDEX [ClusteredIndex_on_ps_product_634020582217726096] ON [dbo].[product]

(

    [ReorderPoint]

)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ps_product]([ReorderPoint])

 

DROP INDEX [ClusteredIndex_on_ps_product_634020582217726096] ON [dbo].[product1] WITH ( ONLINE = OFF )

 

COMMIT TRANSACTION