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:

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.

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:

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:

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

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:

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:

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

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:

Wpiszmy rekordy do tej partycji:

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

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:

Potem na tym schemacie partycjonowania tworzymy tabelę:

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!

No to zobaczmy zawartość partycji:

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ą:

Zobaczmy teraz jak wygląda spis partycji:

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:

Dodaj komentarz:

Autor: Rafał Kraik