Jak partycjonować tabele w SQL

9-Kwi-2011

Partycjonowanie tabel polega na tym, że fizycznie dane rozkładane są do wielu grup plików (i plików), a logicznie, dla użytkownika, pozostają widoczne jakby wchodziły w skład jednej tylko tabeli. Takie rozwiązanie ma wiele zalet:

Jeżeli grupy plików znajdują się na różnych dyskach (z dedykowanymi kontrolerami!), to odczyt z takiej tabeli odbywa się jednocześnie równolegle z każdego z tych dysków.
Jeżeli użytkownik uruchomi transakcję blokującą wiele rekordów w ramach jednej tylko partycji, to serwer może zdecydować o zablokowaniu tylko tej jednej partycji zamiast całej tabeli (do czego by mogło dojść, gdyby tabela nie była partycjonowana)
Można „przełożyć” partycję z jednej tabeli do innej niepartycjonowanej lub partycjonowanej tabeli bez kopiowania rekordów tej tabeli. Kopiowanie polega po prostu na podpięciu tej partycji z jednej tabeli do innej.

Ale po kolei! Zaraz pokażę, jak pracować z tabelami partycjonowanymi.

Koniecznie potrzebujemy bazy danych, która składa się z wielu grup plików:

CREATE DATABASE [TestPartition] ON PRIMARY
( NAME = N'TestPartition',FILENAME = N'C:\DATA\TestPartition.mdf' ,
SIZE = 2046KB , FILEGROWTH = 1024KB ),
FILEGROUP [fg1]
(NAME = N'f1', FILENAME = N'C:\DATA\f1.ndf' ,
SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [fg2]
( NAME = N'f2',
FILENAME = N'C:\DATA\f2.ndf' ,
SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [fg3]
(NAME = N'f3',
FILENAME = N'C:\DATA\f3.ndf' ,
SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestPartition log',
FILENAME = N'C:\DATA\TestPartition_log.ldf',
SIZE = 1024KB, FILEGROWTH = 10%)
GO

CREATE DATABASE [TestPartition] ON PRIMARY

( NAME = N'TestPartition',FILENAME = N'C:\DATA\TestPartition.mdf' ,

SIZE = 2046KB , FILEGROWTH = 1024KB ),

FILEGROUP [fg1]

(NAME = N'f1', FILENAME = N'C:\DATA\f1.ndf' ,

SIZE = 2048KB , FILEGROWTH = 1024KB ),

FILEGROUP [fg2]

( NAME = N'f2',

FILENAME = N'C:\DATA\f2.ndf' ,

SIZE = 2048KB , FILEGROWTH = 1024KB ),

FILEGROUP [fg3]

(NAME = N'f3',

FILENAME = N'C:\DATA\f3.ndf' ,

SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'TestPartition log',

FILENAME = N'C:\DATA\TestPartition_log.ldf',

SIZE = 1024KB, FILEGROWTH = 10%)

Baza danych już jest. Pora na implementację narzędzi potrzebnych do partycjonowania. Partycjonowanie opiera się na dwóch obiektach:

Funkcji partycjonującej, która patrzy na dane wpisywane do tabeli i na podstawie tych danych rozstrzyga, czy rekord należy zapisać do jednej czy do innej partycji. Funkcja przyjmuje określony typem parametr (tu datę) i zwraca numer partycji. Funkcje partycjonujące są bardzo miłe do napisania, bo składają się po prostu z listy granicznych wartości, które rozdzielają partycje. U nas ta lista składa się z pierwszego dnia miesiąca stycznia i lutego 2008 roku. Pytanie tylko, czy data 2008-01-01 (czyli graniczna data) ma należeć do pierwszej czy do drugiej partycji!? Zależy to od słowa LEFT lub RIGHT w definicji funkcji. Jeżeli napisałeś RIGHT, to data graniczna należy do przedziału na prawo, czyli 2008-01-01 należy do partycji drugiej, zaś każda data wcześniejsza do partycji pierwszej.
Schematu partycjonowania, który jest równie prosty jak funkcja partycjonująca, bo składa się z listy grup plików na których mają być składowane partycje. Schemat partycjonowania wiąże jednocześnie się z funkcją, która będzie ustalać numer partycji dla danego rekordu.

CREATE PARTITION FUNCTION MyPF(DATETIME)
AS
RANGE RIGHT FOR VALUES('2008-01-01','2008-02-01')
GO

CREATE PARTITION FUNCTION MyPF(DATETIME)

RANGE RIGHT FOR VALUES('2008-01-01','2008-02-01')

CREATE PARTITION SCHEME MyPS(DATE)
AS
PARTITION MyPF
TO
(FGl, FG2, FG3)

CREATE PARTITION SCHEME MyPS(DATE)

PARTITION MyPF

(FGl, FG2, FG3)

Teraz pora na utworzenie tabeli. Tabela umieszczona będzie na (!) schemacie partycjonowania. Wywołując tę funkcję należy przekazać do tabeli jakąś kolumnę z tej tabeli:

CREATE TABLE Orders
(OrderID INT IDENTITY,
OrderDate DATE)
ON MyPS (OrderDate)

CREATE TABLE Orders

(OrderID INT IDENTITY,

OrderDate DATE)

ON MyPS (OrderDate)

Jak więc będzie się odbywał zapis?

Użytkownik przesyła rekord (np. przez insert)
Data z tego rekordu jest przekazywana do schematu partycjonowania
Schemat partycjonowania nie wie, gdzie zapisać ten rekord (na której partycji), więc uruchamia funkcję partycjonującą przekazując do niej tę datę.
Funkcja partycjonująca określa numer partycji
Rekord jest zapisywany na określonej przez funkcję partycji zgodnie ze spisem partycji w schemacie partycjonowania.

Można w każdej chwili zobaczyć, jakie mamy schematy partycjonowania:

SELECT * FROM sys.partition_schemes
<a href="http://www.mobilo24.eu/wp-content/uploads/2011/04/part01.png"><img class="alignnone size-full wp-image-467" title="part01" src="http://www.mobilo24.eu/wp-content/uploads/2011/04/part01.png" alt="" width="453" height="68" /></a>

1 2	SELECT * FROM sys.partition_schemes <a href="http://www.mobilo24.eu/wp-content/uploads/2011/04/part01.png"><img class="alignnone size-full wp-image-467" title="part01" src="http://www.mobilo24.eu/wp-content/uploads/2011/04/part01.png" alt="" width="453" height="68" /></a>

Pora wypełnić tabelę danymi. Wpiszemy 100 rekordów do partycji 1, 200 do partycji 2 I 300 do partycji 3:

SET NOCOUNT ON
DECLARE @i INT=1;

1 2	SET NOCOUNT ON DECLARE @i INT=1;

WHILE (@i&lt;=100)
BEGIN
  INSERT INTO Orders VALUES ('2007-12-01')
  SET @i = @i+1
END
GO

WHILE (@i<=100)

BEGIN

INSERT INTO Orders VALUES ('2007-12-01')

SET @i = @i+1

END

SET NOCOUNT ON
DECLARE @i INT=1;

1 2	SET NOCOUNT ON DECLARE @i INT=1;

WHILE (@i&lt;=200)
BEGIN
  INSERT INTO Orders VALUES ('2008-01-01')
  SET @i = @i+1
END
GO

WHILE (@i<=200)

BEGIN

INSERT INTO Orders VALUES ('2008-01-01')

SET @i = @i+1

END

SET NOCOUNT ON
DECLARE @i INT=1;

1 2	SET NOCOUNT ON DECLARE @i INT=1;

WHILE (@i&lt;=300)
BEGIN
  INSERT INTO Orders VALUES ('2008-02-01')
  SET @i = @i+1
END

WHILE (@i<=300)

BEGIN

INSERT INTO Orders VALUES ('2008-02-01')

SET @i = @i+1

END

Dane wpisane, prosty SELECT z tabeli wyświetla 600 rekordów. Jak tu się przekonać, że dane trafiły rzeczywiście do określonych tabel? Wykonując:

SELECT * FROM sys.partitions WHERE OBJECT_ID('Orders')=object_id

1	SELECT * FROM sys.partitions WHERE OBJECT_ID('Orders')=object_id

Możesz zobaczyć podział tabeli na partycje łacznie z ilością rekordów w każdej partycji:

Co należy jednak zrobić, jeżeli dane się rozrastają i przydałaby się kolejna partycja np. na nowsze rekordy?
Zacznijmy od dodania do bazy danych nowej grupy plików:

ALTER DATABASE [TestPartition] ADD FILEGROUP [fg4]

1	ALTER DATABASE [TestPartition] ADD FILEGROUP [fg4]

ALTER DATABASE [TestPartition] ADD FILE
( NAME = N'f4',
FILENAME = N'C:\DATA\f4.ndf',
SIZE = 2048KB, FILEGROWTH = 1024KB ) TO FILEGROUP [fg4]
GO

ALTER DATABASE [TestPartition] ADD FILE

( NAME = N'f4',

FILENAME = N'C:\DATA\f4.ndf',

SIZE = 2048KB, FILEGROWTH = 1024KB ) TO FILEGROUP [fg4]

Teraz informujemy schemat partycjonowania o tym, że ma sobie używać nowej partycji.

ALTER PARTITION SCHEME MyPS
NEXT USED [FG4]

1 2	ALTER PARTITION SCHEME MyPS NEXT USED [FG4]

Taka informacja musi też być dostarczona funkcji partycjonującej. W tym przypadku ważniejsze jest, że wszystkie daty większe lub równe 2008-04-01 mają trafiać do nowej partycji:

ALTER PARTITION FUNCTION MyPF()
SPLIT RANGE ('2008-04-01')

1 2	ALTER PARTITION FUNCTION MyPF() SPLIT RANGE ('2008-04-01')

Wpiszmy rekordy do tej partycji:

SET NOCOUNT ON
DECLARE @i INT=1;

1 2	SET NOCOUNT ON DECLARE @i INT=1;

WHILE (@i&lt;=400)
BEGIN
  INSERT INTO Orders VALUES ('2008-04-01')
  SET @i = @i+1
END
GO

WHILE (@i<=400)

BEGIN

INSERT INTO Orders VALUES ('2008-04-01')

SET @i = @i+1

END

I zobaczmy, jak teraz rozłożyły się dane w partycjach:

SELECT * FROM sys.partitions WHERE OBJECT_ID('Orders')=object_id

1	SELECT * FROM sys.partitions WHERE OBJECT_ID('Orders')=object_id

<a href="http://www.mobilo24.eu/wp-content/uploads/2011/04/part03.png"><img class="alignnone size-full wp-image-469" title="part03" src="http://www.mobilo24.eu/wp-content/uploads/2011/04/part03.png" alt="" width="902" height="129" /></a>

1	<a href="http://www.mobilo24.eu/wp-content/uploads/2011/04/part03.png"><img class="alignnone size-full wp-image-469" title="part03" src="http://www.mobilo24.eu/wp-content/uploads/2011/04/part03.png" alt="" width="902" height="129" /></a>

W takim razie zmieniamy scenariusz. Niektóre dane należy „przełożyć” do innej (również partycjonowanej) tabeli.

Zacznijmy od utworzenia funkcji i schematu partycjonowania:

CREATE PARTITION FUNCTION MyPFOld(DATE)
AS
RANGE RIGHT FOR VALUES('2008-01-01')
GO

CREATE PARTITION FUNCTION MyPFOld(DATE)

RANGE RIGHT FOR VALUES('2008-01-01')

CREATE PARTITION SCHEME MyPSOld
AS
PARTITION MyPFOld
TO
(FG1, FG2)
GO

CREATE PARTITION SCHEME MyPSOld

PARTITION MyPFOld

(FG1, FG2)

Potem na tym schemacie partycjonowania tworzymy tabelę:

CREATE TABLE OrdersOld
(OrderID INT NOT NULL,
OrderDate DATE)
ON myPSOld(OrderDate)

CREATE TABLE OrdersOld

(OrderID INT NOT NULL,

OrderDate DATE)

ON myPSOld(OrderDate)

Zauważ, że w tej tabeli OrderID nie musi być IDENTITY (chociaż może być), bo zakładam, że nikt nie będzie tu wpisywał swoich rekordów, a tabela ma jedynie charakter historyczny/archiwalny.
Przerzucamy teraz najstarszą partycję tabeli Orders (partycję 1) do pierwszej partycji tabeli OrdersOld. Ważne jest, że schemat partycjonowania musi wskazywać na tę samą grupę plików!

ALTER TABLE Orders
SWITCH PARTITION 1 TO OrderOld PARTITION 1

1 2	ALTER TABLE Orders SWITCH PARTITION 1 TO OrderOld PARTITION 1

No to zobaczmy zawartość partycji:

SELECT * FROM sys.partitions WHERE OBJECT_ID('Orders')=object_id
SELECT * FROM sys.partitions WHERE OBJECT_ID('OrdersOld')=object_id

1 2	SELECT * FROM sys.partitions WHERE OBJECT_ID('Orders')=object_id SELECT * FROM sys.partitions WHERE OBJECT_ID('OrdersOld')=object_id

Partycja nr 1 w tabeli Orders jest teraz pusta, za to 100 rekordów poleciało do tabeli OrdersOld na partycję pierwszą.

A wszystko odbyło się błyskawicznie. Nie odbywało się żadne fizyczne kopiowanie rekordów, a jedynie zmiana podpięcia partycji do innej tabeli!

Żeby zakończyć przykład pokażmy jeszcze, jak usunąć niepotrzebną już teraz pierwszą partycję tabeli Orders. Wystarczy w tym celu zmienić funkcję partycjonującą:

ALTER PARTITION FUNCTION MyPF()
MERGE RANGE ('2008-01-01')

1 2	ALTER PARTITION FUNCTION MyPF() MERGE RANGE ('2008-01-01')

Zobaczmy teraz jak wygląda spis partycji:

SELECT * FROM sys.partitions WHERE OBJECT_ID('Orders')=object_id
SELECT * FROM sys.partitions WHERE OBJECT_ID('OrdersOld')=object_id

1 2	SELECT * FROM sys.partitions WHERE OBJECT_ID('Orders')=object_id SELECT * FROM sys.partitions WHERE OBJECT_ID('OrdersOld')=object_id

Zniknęła jedna partycja z tabeli Orders!

Przepięcie partycji może się też odbyć do tabeli niepartycjonowanej, o ile ta tabela znajduje się w tej samej grupie plików, co przesuwana partycja. Można by więc teraz dodać następujące polecenia tworzące odpowiednią tabelę w odpowiedniej grupie plików i przesuwające partycję nr 1 do tej tabeli:

CREATE TABLE OrdersArch
(OrderlD INT NOT NULL,
OrderDate DATE)
ON FG1
GO

CREATE TABLE OrdersArch

(OrderlD INT NOT NULL,

OrderDate DATE)

ON FG1

ALTER TABLE Orders
SWITCH PARTITION 1 TO OrdersArch

1 2	ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersArch

Podobne

Dodaj komentarz:

Autor: Rafał Kraik

Komentarze (0)

Jak partycjonować tabele w SQL

Podobne

Dodaj komentarz:

Kursy

Archiwum

O tym piszemy