如何在現有的資料表(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)。