Inspiracja , wiedza , realizacja
Jsystems

W przebudowie

Login



Java

Oracle

Linux

Android

PostgreSQL

Microsoft SQL Server

Tabele partycjonowane

Data dodania: Jun 17, 2016
Data aktualizacji: Jun 17, 2016

Wraz ze wzrostem ilości wierszy w tabeli, spada wydajność systemu i rośnie czas potrzebny na zarządzanie nią. Coraz dłużej trwa wykonanie kopii zapasowej takiej tabeli, odtworzenie jej, jak również zapytania obejmujące całą tabelę będą trwały coraz dłużej. Problemy związane z wydajnością dużych tabel, jak i zarządzanie nimi mozna zmniejszyć, dzieląc tabelę na kilka części. Taka operacja zwana jest właśnie partycjonowaniem. Tabelę nazywamy wtedy tabelą partycjonowaną, a jej części partycjami.

Dzięki takiemu zabiegowi wydajność zapytań odnoszących się do tabeli może ulec poprawie, ponieważ nie będzie potrzeby przeszukania całej tabeli, a tylko jej jednej lub kilku części (partycji).

Przy wykonaniu zapytania, optymalizator kosztowy sam będzie "wiedział" do ilu partycji będzie potrzebował "zajrzeć" aby dostać się do wymaganych danych.


Tworzenie tabeli partycjonowanej


Aby utworzyć tabelę partycjonowaną, nalezy przy poleceniu "create table" użyć klauzuli "partition by" i zdefiniować w jaki sposób chcemy podzielić wiersze w tej tabeli na partycje. Mamy możliwość utworzenia tabeli partycjonowanej względem kluczy:

- range partitioning (dane są mapowane do partycji w oparciu o zakresy wartości),

- list partitioning (dane są mapowane na podstawie listy dyskretnych wartości),

- hash partitioning (dane mapowane na podstawie wyliczeń Hash na kluczu partycjonowania – inaczej zwany partycjonowaniem mieszającym).



Tabela utworzona względem klucza range partitioning.




Przy takim partycjonowaniu tabeli, w zależności od wartości jaka znajdzie się w kolumnie SALARY (mniejsza od 5000, mniejsza od 10000, pozostałe), nowy wiersz znajdzie się w odpowiedniej partycji.


Informacje o tej tabeli możemy np sprawdzić wykonując zapytanie poniższe zapytanie:




Teraz tą tabelę sztucznie zasilę wierszami, pochodzącymi z tabel ze schematu użytkownika HR.




Jeszcze tylko odświeżenie statystyk dla tej tabeli




Zapytanie, które ukaże bardziej szczegółowe informacje na temat powstałych partycji.





Tabela utworzona względem klucza list partitioning.




W tym przypadku, to w której partycji wyląduje dany wiersz zależy od wartości w kolumnie CITY. Jeśli np. nie będzie to żadne z wymienionych przez nas miast, przy tworzeniu partycji, to wtedy taki rekord znajdzie się w ostatniej partycji poprzez zastosowanie klauzuli DEFAULT.


Zasilenie tabeli i odświeżenie statystyk.






Sprawdzenie czy wiersze się poukładały w odpowiednich partycjach, szczególnie chodzi tu o ostatnią partycję z klauzulą DEFAULT.




Gdybysmy nie utworzyli tej ostatniej partycji, to przy próbie wrzucenia rekordu, gdzie nazwa miasta nie pokrywałaby się z wartościemi, które zostały podane, przy tworzeniu pierwszych trzech partcyji otrzymalibyśmy informację o błędzie ORA-14400. A konkretniej informację o tym, iż "wstawiony klucz partycji nie jest odpowiedni dla żadnej partycji".


Tabela utworzona względem klucza hash partitioning.




Dalej te same kroki co poprzednio, zasilenie tabeli, odświeżenie statystyk i sprawdzenie jak się poukładały rekordy w partycjach.







Przy tworzeniu tabel partycjonowanych możemy określić dokładnie w jakiej przestrzeni tabel ma się znaleźć dana partycja.




Automatyczne dodawanie nowych partycji.


Istnieje możliwość stworzenia takiej tabeli partycjonowanej, w której nowe partycje będą się same dodawały, jeśli tylko pojawi się nowa wartość w kolumnie na którą nałożyliśmy klucz partycjonowania.


Jak widać została utworzona tabela z tylko jedną partycją, która uwzględnia daty w kolumnie "HIRE_DATE" jedynie do dnia 01/01/2002.


Jeśli do tej tabeli dorzucimy nowe wiersze, gdzie wartość dla tej kolumny będzie większa, automatycznie postaną nowe partycje. Dodatkowo dzięki zastosowaniu interwału czasowego (INTERVAL (NUMTOYMINTERVAL(1,'YEAR')), każda partycja będzie zawierała dane tylko z jednego roku. Co może się okazać szczególnie przydatne przy np. ewidencjonowaniu faktur firmowych.


Tabelę zasilę teraz danymi pochodzącymi z tabel ze schematu użytkownika HR, aby sprawdzić czy partycje zostaną automatycznie dodane dla pracowników zatrudnionych w różnych latach.



Przy okazji odświeżenie statystyk, aby w kolumnie NUM_ROWS od razu zobaczyć ile rekordów wylądowało w jakiej partycji (bez tego na razie były by wartości NULL).

I sprawdzenie jakie partycje powstały automatycznie w tabeli.



Jeszcze tylko szybki tylko test czy przy insercie ręcznym również zostanie dostawiona nowa partycja.






Jak widać, doszła jeszcze jedna partycja, dla tego pojedynczego rekordu.


Podpartycje.


Możemy również tworzyć tak zwane podpartycje, czyli partycje dla partycji. Co przy bardzo dużych i złożonych tabelach może się okazać bardzo efektywnym sposobem rozdzielenia danych na dające się zarządzać i dostrajać części.



  



Po uzupełnieniu takiej tabelki i odświeżeniu statystyk dla niej, możemy nie tylko sprawdzić ilość wierszy w danej partycji ale także i podpartycji.





Indeksowanie partycji.


Po utworzeniu tabeli partycjonowanej, powinniśmy również utworzyć na niej odpowiednie indeksy. Indeks również może być partycjonowany według tych samych wartości, które uźyte zostaly do partycjonowania tabeli.




Przy tworzeniu tego indeksu, nie został podany żaden zakres natomiast zostało uźyte slowo "LOCAL", które wskazuje aby utworzony został indeks dla każdej partycji w tej tabeli. W tym przypadku indeks będzie się składał z trzech partycji – po jednej na każdą partycję w tabeli. W związku z czym taki indeks nazywamy indeksem "lokalnym".


Na tabelach partycjonowanych mozna tworzyć również indeksy "globalne", które z kolei będą zawierały wartości z wielu partycji.