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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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 |
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.
1 2 3 4 |
CREATE PARTITION FUNCTION MyPF(DATETIME) AS RANGE RIGHT FOR VALUES('2008-01-01','2008-02-01') GO |
1 2 3 4 5 |
CREATE PARTITION SCHEME MyPS(DATE) AS PARTITION MyPF TO (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:
1 2 3 4 |
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:
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:
1 2 |
SET NOCOUNT ON DECLARE @i INT=1; |
1 2 3 4 5 6 |
WHILE (@i<=100) BEGIN INSERT INTO Orders VALUES ('2007-12-01') SET @i = @i+1 END GO |
1 2 |
SET NOCOUNT ON DECLARE @i INT=1; |
1 2 3 4 5 6 |
WHILE (@i<=200) BEGIN INSERT INTO Orders VALUES ('2008-01-01') SET @i = @i+1 END GO |
1 2 |
SET NOCOUNT ON DECLARE @i INT=1; |
1 2 3 4 5 |
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:
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:
1 |
ALTER DATABASE [TestPartition] ADD FILEGROUP [fg4] |
1 2 3 4 5 |
ALTER DATABASE [TestPartition] ADD FILE ( NAME = N'f4', FILENAME = N'C:\DATA\f4.ndf', SIZE = 2048KB, FILEGROWTH = 1024KB ) TO FILEGROUP [fg4] GO |
Teraz informujemy schemat partycjonowania o tym, że ma sobie używać nowej partycji.
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:
1 2 |
ALTER PARTITION FUNCTION MyPF() SPLIT RANGE ('2008-04-01') |
Wpiszmy rekordy do tej partycji:
1 2 |
SET NOCOUNT ON DECLARE @i INT=1; |
1 2 3 4 5 6 |
WHILE (@i<=400) BEGIN INSERT INTO Orders VALUES ('2008-04-01') SET @i = @i+1 END GO |
I zobaczmy, jak teraz rozłożyły się dane w partycjach:
1 |
SELECT * FROM sys.partitions WHERE OBJECT_ID('Orders')=object_id |
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:
1 2 3 4 |
CREATE PARTITION FUNCTION MyPFOld(DATE) AS RANGE RIGHT FOR VALUES('2008-01-01') GO |
1 2 3 4 5 6 |
CREATE PARTITION SCHEME MyPSOld AS PARTITION MyPFOld TO (FG1, FG2) GO |
Potem na tym schemacie partycjonowania tworzymy tabelę:
1 2 3 4 |
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!
1 2 |
ALTER TABLE Orders SWITCH PARTITION 1 TO OrderOld PARTITION 1 |
No to zobaczmy zawartość partycji:
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ą:
1 2 |
ALTER PARTITION FUNCTION MyPF() MERGE RANGE ('2008-01-01') |
Zobaczmy teraz jak wygląda spis partycji:
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:
1 2 3 4 5 |
CREATE TABLE OrdersArch (OrderlD INT NOT NULL, OrderDate DATE) ON FG1 GO |
1 2 |
ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersArch |