RiCo技術農場

網友No.18詢問一個問題，而這問題以前其實也有很多客戶問過我(很多客戶喜歡拿O牌來打MS SQL...)，

如果你有玩過Oracle 11gR2 Partition Table，我個人感覺 MS SQL Partition Table真是扶不起的阿斗....，

真實世界中管理Partition Table的 Partition 是一項繁瑣又沉重的工作，

很多DBA常當我的面抱怨為什麼O牌可以依據資料自動管理Partition Table減輕DBA負擔(但會犧牲效能)，

MS SQL為什麼就不行(我心中OS:花大錢自然有人幫你做得好好...XD)，

當初 SQL2012 推出時我也抱怨過Partition Table的更新有跟沒有一樣(我個人比較希望有更多 Partition Table 類型供DBA選擇)，

anyway....如果你夠熟悉MS SQL Partition Table的話，

要解決這樣的需求也不是一件難事，

下面我提供自己當初的作法，並提供給No.18參考參考。

 

問題:

 

回答: Partition Table確實一開始就要指定相關 Range Values，但不只修改 Partition Function(split or merge)，

還有Partition Scheme也須修改。

那是否要用維護計畫定時調整Partition呢?

請依你的資料庫環境自行決定，而我自己是不採用定時調整的作法(因為客戶會打槍我，導致無法結案，老闆可能又要找我泡茶了....XD)，

我採用 Trigger攔截Insert來管理Partition的需求(效能問題須注意)。

 

 

--1.create partition function by year
CREATE PARTITION FUNCTION pf_myTest (int)
AS RANGE LEFT FOR VALUES (2009 , 2010 , 2011,2012)

--2.create partition scheme
CREATE PARTITION SCHEME ps_myTest AS
PARTITION pf_myTest ALL TO ([PRIMARY])

--3.create table 
CREATE TABLE MyTest (
c1 int identity(1,1) not null,   
c2 Varchar(10),
c3 datetime,
myyear as year(c3) PERSISTED
) ON ps_myTest(myyear)

 

 

 

--insert test data
insert into MyTest values ('rico1','2009-03-01'),
('rico2','2010-01-01'),('rico3','2011-01-01'),('rico4','2012-01-01') 

 

 

 

--確認partition 
select PARTITION=$partition.pf_myTest(myyear),
       rows=COUNT(1),
       minval=MIN(c3),
       maxval=MAX(c3)
from MyTest
group by $partition.pf_myTest(myyear)
order by PARTITION    

可以看到目前Partition Table依照當初所設定Range Values分區，

但如果後續有2013、2014、2015....N年資料新增時該如何自動處理分區呢?

下面我打算建立 Trigger 攔截新增資料來解決這問題。

 

 

 

 

--create trigger INSTEAD OF insert
CREATE TRIGGER tr_AutoPartitionFormyTest ON myTest INSTEAD OF INSERT
AS
 BEGIN
  DECLARE @max_part_dt int;--目前最大分區  
  DECLARE @max_inserted_dt int;--新增資料最大分區
  DECLARE @min_inserted_dt int;--新增資料最小分區

  
  
  SELECT @max_part_dt=cast(max(value) as int)
  from sys.partition_functions f
  inner join sys.partition_range_values rv
  on f.function_id = rv.function_id
  where name = 'pf_myTest';
  SELECT @max_inserted_dt=max(myyear) FROM inserted
  select @min_inserted_dt=min(myyear) FROM inserted where myyear > @max_part_dt 

  IF (@max_inserted_dt > @max_part_dt )
  BEGIN
    
    WHILE (@min_inserted_dt <= @max_inserted_dt )
    BEGIN    
      ALTER PARTITION SCHEME ps_myTest NEXT USED [Primary];--自行修改檔案群組
      ALTER PARTITION FUNCTION pf_myTest() SPLIT RANGE (@min_inserted_dt);      
      SET @min_inserted_dt = @min_inserted_dt +1    
    END   
    
  END;

  -- 最後新增資料到分區
  INSERT INTO MyTest (c2, c3)
  SELECT c2, c3
  FROM inserted;  
END   

--insert test data again
insert into MyTest values ('rico5','2013-03-01'),
('rico6','2013-02-01'),('rico6','2014-01-01'),('rico7','2015-01-01') 

 

 

 

 

 

確認Partition Table

可以看到這裡依據資料自動分割出相對應的Partition。

 

 

 

 

 

 

參考

[SQL SERVER][TSQL] 查詢 Partition Table 相關資訊

[Oracle][Performance]善用Partition Table#簡介

ALTER PARTITION SCHEME (Transact-SQL)

ALTER PARTITION FUNCTION (Transact-SQL)

使用 inserted 或 deleted 資料表