如何在現有的資料表(Table)使用Partition

通常在刪除大量資料時，例如一次要看幾百萬甚至千萬筆的資料，使用Delete會砍到天荒地老，而且一不小心LOG還會大到爆炸，但一般建立Partition Table都是在一開始建立Table時就給定Partition Schema，那已經建立的Table但尚未套用Partition Schema是不是就無法使用Partition了呢?

情況：

上個禮拜同事的報表資料庫要進行刪除舊資料的動作，為了避免資料庫的紀錄檔(LDF)過大，同事每50萬筆進行Commit一次，於是悲劇就要開始發生，刪除到一半發山每50萬筆需要花費25分鐘，但是要刪除的資料有1700萬筆，而且後面還有二個Table要刪除資料量也不小，於是使用Partition的念頭就油然而生了。

發現：

公司的某些資料倉儲當初尚未規劃Partition，原因有很多，一是不知道Partition，一是當初的資料庫是SQL SERVER 2000，但不論是哪一種在大量異動資料(新增、更新與刪除)時效能會非常差，有規劃Partition的Table都是一開始建立好的，所以我開始思考如何在現有的Table加入Partition。

解決方案：

於是上網問了一下G大神，終於在這篇文章找到方法，除了新建一個有Partition的Table後再將資料從舊資料表匯入之外，另一種方式就是使用Cluster Index的方式套用Partition。

執行過程：

第一步：建立原始資料表

--STEP 1
--建立原始資料表
IF OBJECT_ID('partTable') IS NOT NULL
     BEGIN
         DROP TABLE partTable
     END
 
CREATE TABLE partTable (col1 VARCHAR(6), col2 VARCHAR(20)) ;
GO

 --建立CLUSTERED INDEX
IF EXISTS (SELECT NAME FROM SYS.INDEXES WHERE NAME = 'ClusteredIndex_partTable')
     BEGIN
         DROP INDEX [ClusteredIndex_partTable] ON [dbo].[partTable]
     END
CREATE CLUSTERED INDEX [ClusteredIndex_partTable] ON [dbo].[partTable]([col1] ASC)

--建立NONCLUSTERED INDEX
IF EXISTS (SELECT NAME FROM SYS.INDEXES WHERE NAME = 'NonClusteredIndex_partTable')
     BEGIN
         DROP INDEX [NonClusteredIndex_partTable] ON [dbo].[partTable]
     END
CREATE INDEX [NonClusteredIndex_partTable] ON [dbo].[partTable] ([col2] ASC)

第二步：建立Partition Function與Schema

--建立 PARTITION FUNCTION
IF EXISTS (SELECT NAME FROM SYS.PARTITION_FUNCTIONS WHERE name = 'partRange1')
     BEGIN
         DROP PARTITION FUNCTION partRange1
     END
GO
CREATE PARTITION FUNCTION partRange1 (VARCHAR(6))
AS RANGE LEFT FOR VALUES ('201604') ;
GO
 
--建立 PARTITION SCHEME
IF EXISTS (SELECT NAME FROM SYS.PARTITION_SCHEMES WHERE name = 'partScheme1')
     BEGIN
         DROP PARTITION SCHEME partScheme1
     END
CREATE PARTITION SCHEME partScheme1
AS PARTITION partRange1
ALL TO ([PRIMARY]) ;
GO

第三步：將Cluster與NonCluster Index刪除並重建，重建時必須套用Partition Schema。

--建立CLUSTERED INDEX，並套用PARTITION SCHEME
IF EXISTS (SELECT NAME FROM SYS.INDEXES WHERE NAME = 'ClusteredIndex_partTable')
     BEGIN
         DROP INDEX [ClusteredIndex_partTable] ON [dbo].[partTable]
     END
CREATE CLUSTERED INDEX [ClusteredIndex_partTable] ON [dbo].[partTable] ([col1] ASC) ON partScheme1([col1])

--建立NONCLUSTERED INDEX，並套用PARTITION SCHEME
IF EXISTS (SELECT NAME FROM SYS.INDEXES WHERE NAME = 'NonClusteredIndex_partTable')
     BEGIN
         DROP INDEX [NonClusteredIndex_partTable] ON [dbo].[partTable]
     END
CREATE INDEX [NonClusteredIndex_partTable] ON [dbo].[partTable] ([col2] ASC) ON partScheme1([col1])

第四步：確認Partition套用成功

結論：

由上得知，在已經建立好的資料表可以透過重新建立Cluster Index的方式套用Partition，這對日後維護大資料的Table有很大的幫助，下一節將示範如何在已經有Index的情況下進行資料的轉換(SWITCH)。

Partition06PartitionTable搭配Switch新增資料

在上一篇Partition05PartitionTable新增資料效能測試有提到使用PartitionTable會降低新增資料的效能(刪除與更新也會)，之前在Partition04PartitionTable搭配Switch刪除資料有示範如何搭配Switch刪除資料，而本篇則要示範在搭配Switch可以新增資料，處理PartitionTable新增資料效能低落的問題。

--1.建立測試的Table，一個為PartitionTable，一個是非PartitionTable，要放置轉入資料的Table

IF OBJECT_ID('InsertTable') IS NOT NULL

BEGIN DROP TABLE InsertTable END

CREATE TABLE InsertTable

(

ChineseYM INT ,

tName varchar(50) ,

tOther [varchar](30)

)

ON [PRIMARY];

GO

ALTER TABLE InsertTable

ADD CONSTRAINT [CK_ChineseYM] CHECK (ChineseYM >= 10008 AND [ChineseYM]  IS NOT NULL);

GO

ALTER TABLE [dbo].[InsertTable] CHECK CONSTRAINT [CK_ChineseYM]

GO

--2.新增測試資料

INSERT INTO InsertTable

SELECT  * FROM  [TestTable]

WHERE ChineseYM=10008

GO

--3.檢查TABLE內容

SELECT COUNT(*) FROM ArchivedTable

SELECT COUNT(*) FROM InsertTable

執行結果：

--4.使用Switch轉入資料到PartitionTable

ALTER TABLE InsertTable  SWITCH TO  ArchivedTable PARTITION 3;

GO

--5.檢查TABLE內容

SELECT COUNT(*) FROM ArchivedTable

SELECT COUNT(*) FROM InsertTable

執行結果：

結論：在PartitionTable使用一個新的Table並搭配Switch可以處理PartitionTable新增資料時造成效能低落的問題。

Partition05PartitionTable新增資料效能測試

在上一篇Partition04PartitionTable搭配Switch刪除資料有提到在PartitionTable刪除資料的方法，而這篇測試主要是測試PartitionTable對於資料異動(Insert)時所帶來的影響。

--1.建立測試的Table，一個為PartitionTable，一個是非PartitionTable

IF OBJECT_ID('ArchivedTable') IS NOT NULL

BEGIN

   DROP TABLE ArchivedTable

END

CREATE TABLE [dbo].[ArchivedTable](

        [ChineseYM] [int]  ,

        [tName] [varchar](50) ,

        [tOther] [varchar](30)

) ON ChineseYearScheme (ChineseYM);

IF OBJECT_ID('NonPartitionTable') IS NOT NULL

BEGIN

   DROP TABLE NonPartitionTable

END

CREATE TABLE [dbo].[NonPartitionTable](

        [ChineseYM] [int]  ,

        [tName] [varchar](50) ,

        [tOther] [varchar](30)

) ON [PRIMARY];

--清除CACHE

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

--2.測試新增資料時的效能

INSERT INTO NonPartitionTable

SELECT  * FROM  [TestTable]

GO

執行結果：花費1分09秒

INSERT INTO ArchivedTable

SELECT  * FROM  [TestTable]

GO

執行結果：花費2分59秒

結果比較：

PartitionTable	花費2分59秒
非PartitionTable	花費1分09秒

結論：PartitionTable降低了Insert的效能，因為在新增資料時多了判斷資料要歸類到哪一個Partition，所以造成效能上的低落，這是很多人共有的問題。使用Switch可以解決Insert的效能問題，下一篇我將為大家示範如何解決。

Partition04PartitionTable搭配Switch刪除資料

在Partition03PartitionTable搭配Switch轉移資料討論到使用Partition Table搭配Switch可將資料從原本的Table轉移到另一個Table，本篇則是上一篇的應用本，在Partition Table使用使用Switch搭配Truncate Table提升刪除大量資料的效能。

--1.觀看資料筆數

SELECT COUNT(*) FROM ArchivedTable

GO

執行結果：

--2.建立要放置轉出資料的Table

IF OBJECT_ID('TruncateTable') IS NOT NULL

BEGIN

   DROP TABLE TruncateTable

END

CREATE TABLE TruncateTable

(

ChineseYM INT ,

tName varchar(50))

ON [PRIMARY];

GO

ALTER TABLE [dbo].[TruncateTable]  WITH CHECK ADD  CONSTRAINT [CK_TruncateTable] CHECK  (([ChineseYM]>=(10001) AND [ChineseYM]  IS NOT NULL ))

GO

ALTER TABLE [dbo].[TruncateTable] CHECK CONSTRAINT [CK_TruncateTable]

GO

--3.檢查TABLE內容

SELECT COUNT(*) FROM ArchivedTable

SELECT COUNT(*) FROM TruncateTable

執行結果：

-- 4.將資料從ArchivedTable轉移到TruncateTable

ALTER TABLE ArchivedTable  SWITCH PARTITION 3 TO TruncateTable ;

GO

--5.檢查TABLE內容

SELECT COUNT(*) FROM ArchivedTable

SELECT COUNT(*) FROM TruncateTable

執行結果：

--6.刪除資料

TRUNCATE TABLE TruncateTable

GO

執行結果：

--7.再次檢查TABLE內容

SELECT COUNT(*) FROM ArchivedTable

SELECT COUNT(*) FROM TruncateTable

GO

執行結果：

-- 8.將PARTITION 3轉回給ArchivedTable

ALTER TABLE TruncateTable   SWITCH TO ArchivedTable PARTITION 3  ;

GO

--9.測試使用DELETE刪除資料

DELETE ArchivedTable

WHERE ChineseYM=10008

執行結果：

比較結果：

PartitionTable	Switch搭配Truncate Table	4ms
NonPartitionTable	Delete	2079ms

結論：發現在PartitionTable使用Switch搭配Truncate Table比不使用PartitionTable直接Delete效能快501倍左右，所以PartitionTable對於刪除大量資料在效能上有顯著的提升。

下一篇我會測試與說明PartitionTable對於新增資料效能的影響。

Partition03PartitionTable搭配Switch轉移資料

在上一篇Partition02建立PartitionTable介紹如何建立Partition Table後，這篇要介紹Partition Table搭配Switch轉移資料。

Partition Table搭配Switch可將資料從原本的Table轉移到另一個Table，這是一個很實用而且常用到的範例，熟悉如何使用Partition Table轉移資料後，之後對於使用Partition Table新增資料或刪除資料可以更得心應手，如果要做好DBA的工作熟悉Partition Table的轉換資料機制是不可或缺的技巧。

--1.觀看原本的資料

Use Performance

GO

SELECT * FROM ArchivedTable

GO

執行結果：

--2.建立要放置轉出資料的Table

IF OBJECT_ID('TruncateTable') IS NOT NULL

BEGIN

   DROP TABLE TruncateTable

END

CREATE TABLE TruncateTable

(ChineseYM INT PRIMARY KEY,

tName varchar(50))

ON [PRIMARY];

GO

--3.將資料從ArchivedTable轉移到TruncateTable

ALTER TABLE ArchivedTable  SWITCH PARTITION 3 TO TruncateTable ;

GO

--4.1檢查Partition的內容

SELECT $PARTITION.ChineseYearPartitions(ChineseYM) AS [Partition Number], COUNT(*) AS total

FROM ArchivedTable

GROUP BY $PARTITION.ChineseYearPartitions(ChineseYM)

ORDER BY $PARTITION.ChineseYearPartitions(ChineseYM)

COMPUTE SUM (COUNT(*))

執行結果：

--4.2檢查TABLE內容

SELECT * FROM ArchivedTable

SELECT * FROM TruncateTable

執行結果：

--5.將資料從TruncateTable轉入ArchivedTable，因為沒有CONSTRAINT，再轉換資料時ArchivedTable不知道正確的資料範圍

ALTER TABLE TruncateTable   SWITCH TO ArchivedTable PARTITION 3  ;

GO

/*

訊息4982，層級16，狀態1，行1

ALTER TABLE SWITCH 陳述式失敗。來源資料表'Performance.dbo.TruncateTable' 的檢查條件約束所允許的值，

於目標資料表'Performance.dbo.ArchivedTable' 資料分割'3' 上定義的範圍並不允許。

*/

--6.增加CONSTRAINT，在轉換時ArchivedTable才會知道正確的資料範圍

ALTER TABLE [dbo].[TruncateTable]  WITH CHECK ADD  CONSTRAINT [CK_TruncateTable] CHECK  (([ChineseYM]>=(10001)))

GO

ALTER TABLE [dbo].[TruncateTable] CHECK CONSTRAINT [CK_TruncateTable]

GO

-- 在一次將資料從TruncateTable轉入ArchivedTable

ALTER TABLE TruncateTable   SWITCH TO ArchivedTable PARTITION 3  ;

GO

--7.檢查Partition的內容

SELECT $PARTITION.ChineseYearPartitions(ChineseYM) AS [Partition Number], COUNT(*) AS total

FROM ArchivedTable

GROUP BY $PARTITION.ChineseYearPartitions(ChineseYM)

ORDER BY $PARTITION.ChineseYearPartitions(ChineseYM)

COMPUTE SUM (COUNT(*))

執行結果：

--8.檢查TABLE內容

SELECT * FROM ArchivedTable

SELECT * FROM TruncateTable

GO

執行結果：