SQL Server 資料表切割、合併、再切割與交換

資料表切割是資料庫中常用的應用
當資料量隨著時間愈來愈大
舊的資料價值就比較低,又不能隨備亂刪
如此系統在運作上要花比較多的效能

舉例來說,一本5萬頁的書 跟 5頁的書
那本書找起來比較快?
當然是只有5頁的書嘍!

在這部份,老師用圖解的來說明切割的概念
可參考下圖來了解
簡單而言,使用者並不會知道資料被切了
有點像群組的概念
還是可以查,表面上還是同一張表
但實際上已經透過分割函數分組
並在分割結構分配到不同的位置

在做切割時必須相當謹慎,切了索引記得重新建立
1本書被切成5本小書,那目錄還可以用嗎?
當然是要重新寫過目錄




--1. 建立分割函數----------------------------------------
CREATE PARTITION FUNCTION 價錢分割(SMALLMONEY)
AS RANGE LEFT --也可以RIGHT
FOR VALUES(20,40,80)
GO
--left跟right的差別很簡單,資料要被在區間值上的左邊還是右邊
--以本例來說是放左邊,所以價錢是20會放入在第1組
--以時間而言,例如午夜12點,就會放在右邊
--這邊有3個區間值,所以會被切成4份




--2. 建立分割結構----------------------------------------
CREATE PARTITION SCHEME 價錢結構
AS PARTITION 價錢分割
TO([PRIMARY],會計群,人事群,行銷群)           --以群來分組
--TO([PRIMARY],[PRIMARY],[PRIMARY],會計群) --也可以3個在同群
--TO([PRIMARY],會計群,人事群,行銷群,預備群)   --也可以多1個,若最後一組還想分割時會預設放入
--ALL TO([PRIMARY])                      --當然對可以全都在同一群
GO


--3. 建立分割表----------------------------------------
CREATE TABLE 巨巨分割
(
    產品編號 INT,
 產品名 NCHAR(5),
 售價 SMALLMONEY
)
ON 價錢結構(售價) --以前是on在群上,現在在分割結構上
GO


--測試一下
SELECT * FROM 巨巨分割

--將巨巨表倒入
INSERT INTO 巨巨分割
  SELECT * FROM 練練.dbo.匠匠

--新增一筆
INSERT INTO 巨巨分割 VALUES(26,'卡拉酒母',35)

--查看組別
SELECT *,$PARTITION.價錢分割(售價) AS 組別
FROM 巨巨分割


如此即完成:

  •  依價錢切割
  •  20以下、20~40間、40~80間、80以上
  • 位置在:  [PRIMARY]、會計群、人事群、行銷群   

的分割表,看樣子似乎不錯
假設今天物價上揚,當年的20以下跟現在的40以下差不多
而且資料舊了,那這時就會有「區間滑動」問題
希望能做到:

  • "20以下""20~40間"二個表結合
  • 80以上的再多切成:"80~100間""100以上"二個表
最後是測試SWITCH交換的功能
交換就是換個空白的表到某個區間
並將該區間原資料換出來
交換表也可以是別的表的區間表,也就是說
可以2個表的區間表做交換

--會造成資料的實際搬移
ALTER PARTITION FUNCTION 價錢分割()
MERGE RANGE(20) --結合區間值為20的2個表


--切割組別
--先指定新分割表的群組
ALTER PARTITION SCHEME 價錢結構 NEXT USED 會計群
ALTER PARTITION FUNCTION 價錢分割()
SPLIT RANGE(100) --切割區間值為100的表


--交換(SWITCH)
--1. 欲交換的目的地表必須與原分割表的結構一模一樣
--2. 欲交換的目的地表必須是空的
--3. 欲交換的目的地表與分割表欲交換的組別表需位於相同的檔案群組


DROP TABLE 巨巨交換表
CREATE TABLE 巨巨交換表
(
    產品編號 INT,
 產品名 NCHAR(5),
 售價 SMALLMONEY
)
ON 會計群

SELECT * FROM 巨巨交換表

ALTER TABLE 巨巨分割 SWITCH PARTITION 3 TO 巨巨交換表 --PARTITION 2


留言

熱門文章