Rok akademicki 2013/2014
Politechnika Warszawska Wydziaª Elektroniki i Technik Informacyjnych Instytut Informatyki
PRACA DYPLOMOWA INYNIERSKA Katarzyna Kwa±niewska Partycjonowanie w Oracle Database 11gR2, ocena potencjalnych i rzeczywistych korzy±ci z ró»nych metod partycjonowania danych i indeksów
Opiekun pracy: dr in». Michaª Rudowski
Ocena . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
...................................... Podpis Przewodnicz¡cego
Komisji Egzaminu Dyplomowego
Specjalno±¢:
Informatyka In»ynieria systemów informatycznych
Data urodzenia:
30 kwietnia 1991 r.
Data rozpocz¦cia studiów:
1 pa¹dziernika 2010 r.
yciorys Urodziªam si¦ 30 kwietnia 1991 roku w Warszawie. W latach 2007-2010 ucz¦szczaªam do II Liceum Ogólnoksztaªc¡cego imienia Stefana Batorego w Warszawie do klasy o pro lu matematyczno- zyczno. W 2010 roku rozpocz¦ªam studia na Wydziale Elektroniki i Technik Informacyjnych Politechniki Warszawskiej, na kierunku Informatyka. Od semestru zimowego 2012/2013 moj¡ specjalno±ci¡ jest In»ynieria Systemów Informatycznych. W lecie 2013 roku (od lipca do wrze±nia) odbyªam praktyki w rmie Samsung Electronics Polska Sp.z o.o. Poza zainteresowaniami technicznymi do swoich pasji zaliczam podró»owanie, muzyk¦ oraz sport.
.................................... podpis studenta
Egzamin dyplomowy Zªo»yªa egzamin dyplomowy w dn. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Z wynikiem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Ogólny wynik studiów . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Dodatkowe wnioski i uwagi Komisji . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
.................................................................................
Streszczenie Tematem pracy jest partycjonowanie tabel i indeksów w Oracle Database 11gR2. Partycjonowanie jest dodatkow¡ opcj¡ dost¦pn¡ dla bazy danych Oracle Database Enterprise Edition. Jest to pot¦»ny mechanizm, szczególnie przydatny przy zarz¡dzaniu du»¡ ilo±ci¡ danych. Partycjonowanie mo»e znacz¡co uªatwi¢ administrowanie baz¡ oraz pozwoli¢ na zwi¦kszenie wydajno±ci, rozumianej gªównie jako szybko±¢ dost¦pu do danych. W pierwszych rozdziaªach opisaªam mo»liwe sposoby partycjonowania oraz dokonaªam analizy potencjalnych korzy±ci wynikaj¡cych z ich zastosowania. Nast¦pnie przedstawiªam wyniki bada« efektywno±ci partycjonowania i jego wpªywu na czas wykonywania polece«: SELECT w zale»no±ci od rozmiaru danych.
Sªowa kluczowe: Oracle,
Partycjonowanie, Baza Danych, Tabele, Indeksy.
Partitioning in Oracle Database 11gR2, assessment of potential and actual bene ts of the various methods of tables and indexes partitioning The subject of this work is partitioning tables and indexes in Oracle Database 11gR2. Partitioning is an additional option available for Oracle Database Enterprise Edition. It is a powerful mechanism particularly useful when managing large volumes of data. Partitioning can simplify database administration, improve availability and signi cantly increase productivity, understood primarily as a data access speed. In rst chapters, I described the possible ways of partitioning and made an analysis of potential bene ts arising from their usage. Then I presented results of study concerning partitioning eectivness and its eect on the performance of SELECT queries, depending on the size of the manipulated data.
Key words: Oracle,
Partitioning, Database, Tables, Indexes.
Spis tre±ci
1 Wst¦p
5
1.1
Wprowadzenie do tematu pracy
. . . . . . . . . . . . . . . . . . . . .
6
1.2
Cel i motywacja pracy
. . . . . . . . . . . . . . . . . . . . . . . . . .
7
1.3
Ukªad i zakres pracy
. . . . . . . . . . . . . . . . . . . . . . . . . . .
7
2 Opcja partycjonowania w Oracle Database 2.1
2.2
2.3
Korzy±ci . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
9
2.1.1
Efektywne administrowanie
. . . . . . . . . . . . . . . . . . .
9
2.1.2
Bezpiecze«stwo, dost¦pno±¢
. . . . . . . . . . . . . . . . . . .
10
2.1.3
Przycinanie partycji (ang. partitioning pruning)
. . . . . . . .
10
2.1.4
Zª¡czenia partycji (ang. partition-wise joins) . . . . . . . . . .
11
2.1.5
Zrównoleglenie, skalowalno±¢ . . . . . . . . . . . . . . . . . . .
11
Metody partycjonowania do wersji Oracle 10g
. . . . . . . . . . . . .
12
2.2.1
Partycjonowanie Zakresowe (ang. Range Partitioning) . . . . .
12
2.2.2
Partycjonowanie Mieszaj¡ce (ang. Hash Partitioning)
. . . . .
13
2.2.3
Partycjonowanie Listowe (ang. List Partitioning) . . . . . . . .
14
2.2.4
Partycjonowanie Dwupoziomowe (ang. Composite Partitioning) 15
Nowe w 11g metody i wªa±ciwo±ci partycjonowania
. . . . . . . . . .
17
2.3.1
Partycjonowanie Interwaªowe (ang. Interval Partitioning) . . .
17
2.3.2
Partycjonowanie Systemowe (ang. System Partitioning) . . . .
18
2.3.3
Partycjonowanie Referencyjne (ang. Reference Partitioning)
.
19
2.3.4
Partycjonowanie na Bazie Kolumny Wirtualnej (ang. Virtual Column Based Partitioning) . . . . . . . . . . . . . . . . . . .
21
Partycjonowanie Dwupoziomowe w Oracle 11gR2
. . . . . . .
21
. . . . . . . . . . . . . . . . . . . . . . . .
22
2.4.1
Lokalny Indeks Partycjonowany . . . . . . . . . . . . . . . . .
22
2.4.2
Indeks Globalny . . . . . . . . . . . . . . . . . . . . . . . . . .
23
Perspektywy dotycz¡ce partycji tabel i indeksów . . . . . . . . . . . .
26
2.3.5 2.4
2.5
9
Partycjonowanie indeksów
3 rodowisko testów opcji partycjonowania
29
3.1
Cel i zakres testów
. . . . . . . . . . . . . . . . . . . . . . . . . . . .
29
3.2
Opis ±rodowiska testowego . . . . . . . . . . . . . . . . . . . . . . . .
29
1
3.3
3.4
3.5
Relacyjny model danych
. . . . . . . . . . . . . . . . . . . . . . . . .
31
3.3.1
Partycjonowanie referencyjne (orem1) . . . . . . . . . . . . . .
34
3.3.2
Partycjonowanie mieszaj¡ce (orem2)
. . . . . . . . . . . . . .
37
3.3.3
Partycjonowanie na kolumnie wirtualnej (orem2) . . . . . . . .
41
3.3.4
Partycjonowanie interwaªowe (orem2) . . . . . . . . . . . . . .
43
3.3.5
Partycjonowanie dwupoziomowe interwaªowo-listowe (orem2) .
47
3.3.6
Partycjonowanie dwupoziomowe zakresowo-mieszaj¡ce (orem2)
50
Generacja danych testowych . . . . . . . . . . . . . . . . . . . . . . .
53
3.4.1
Generacja danych tabeli klienci
. . . . . . . . . . . . . . . . .
54
3.4.2
Generacja danych tabeli zamownienia . . . . . . . . . . . . . .
56
Aplikacja testowa . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
57
3.5.1
Budowa i dziaªanie aplikacji
. . . . . . . . . . . . . . . . . . .
57
3.5.2
Zapytania testowe . . . . . . . . . . . . . . . . . . . . . . . . .
58
4 Testy
61
4.1
Tabele klienci, klienci_ref oraz klienci_hash . . . . . . . . . . . . . .
62
4.2
Tabela zamowienia
64
4.3
Tabela zamowienia_ref
. . . . . . . . . . . . . . . . . . . . . . . . .
68
4.4
Tabela zamowienia_hash . . . . . . . . . . . . . . . . . . . . . . . . .
71
4.5
Tabela zamowienia_list
. . . . . . . . . . . . . . . . . . . . . . . . .
75
4.6
Tabela zamowienia_inter
. . . . . . . . . . . . . . . . . . . . . . . .
79
4.7
Tabela zam_inter_list
. . . . . . . . . . . . . . . . . . . . . . . . . .
83
4.8
Tabela zam_range_hash . . . . . . . . . . . . . . . . . . . . . . . . .
87
4.9
Wnioski z bada« testowych . . . . . . . . . . . . . . . . . . . . . . . .
91
. . . . . . . . . . . . . . . . . . . . . . . . . . . .
5 Podsumowanie
93
A Zaª¡cznik A - kod ¹ródªowy aplikacji testowej
95
2
Spis rysunków 1
Równolegªe wykonanie zª¡cze« na partycjach. Opracowano na podstawie rysunku Figure 3-1 Parallel Execution of a Full Partition-wise
Join [10] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Partycjonowanie zakresowe. Opracowano na podstawie rysunku Fi-
gure 2-2 List, Range, and Hash Partitioning [2] 3
. . . . . . . . . . . .
. . . . . . . . . . . .
. . . . .
. . . . . . . . . . . . . . . .
24
Globalny indeks partycjonowany tabeli partycjonowanej. Opracowano na podstawie rysunku Figure 2-7 Global Partitioned Index [2]
9
20
Indeks partycjonowany lokalnie. Opracowano na podstawie rysunku
Figure 2-6 Local Partitioned Index [2] . . . . . . . . . . . . . . . . . . 8
17
Partycjonowanie referencyjne. Opracowano na podstawie rysunku Fi-
gure 2-5 With Reference Partitioning [2] 7
15
Partycjonowanie dwupoziomowe zakresowo-mieszaj¡ce. Opracowano na podstawie rysunku Figure 2-3 Composite Partitioning [2]
6
14
Partycjonowanie listowe. Opracowano na podstawie rysunku Figure
2-2 List, Range, and Hash Partitioning [2] . . . . . . . . . . . . . . . 5
13
Partycjonowanie mieszaj¡ce. Opracowano na podstawie rysunku Fi-
gure 2-2 List, Range, and Hash Partitioning [2] 4
11
. . . .
26
Zwi¡zek mi¦dzy niepartycjonowanym indeksem globalnym i tabel¡ partycjonowan¡. Opracowano na podstawie rysunku Figure 2-8 Global
Nonpartitioned Index [2] 10
. . . . . . . . . . . . . . . . . . . . . . . . .
27
Lista gªównych parametrów kon guracyjnych wygenerowana przez narz¦dzie DBCA przy okazji tworzenia bazy danych dbtest na maszynie orem1.
11
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
31
Plan wykonania zapytania dla tabeli klienci_ref o 30 mln wierszy, potwierdzaj¡cy u»ycie mechanizmu przycinania partycji.
13
30
Diagram relacyjny przedstawiaj¡cy bazowy model danych. Opracowanie wªasne.
12
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . .
63
Plan wykonania zapytania dla tabeli klienci_hash o 30 mln wierszy, obrazuj¡cy przeprowadzenie zapytania równolegªego.
3
. . . . . . . . .
63
14
Plan wykonania zapytania nr 6 dla tabeli zamowienia o 60 mln wierszy, potwierdzaj¡cy u»ycie mechanizmu zrównoleglenia i wykorzystania indeksu globalnie partycjonowanego na kluczu obcym. . . . . . . .
15
67
Plan wykonania zapytania nr 3 dla tabeli zamowienia o 30 mln wierszy, potwierdzaj¡cy u»ycie mechanizmu przycinania partycji. Przeszukiwana byªa tylko pierwsza partycja indeksu globalnie partycjonowanego na kluczu obcym id_klienta.
16
. . . . . . . . . . . . . . . . . .
67
Plan wykonania zapytania nr 6 dla tabeli zamowienia_ref o 60 mln wierszy, wskazuj¡cy na równolegle wykonywane zª¡cze« na poziomie partycji oraz dynamiczne przycinanie partycji. . . . . . . . . . . . . .
17
71
Plan wykonania zapytania nr 6 dla tabeli zamowienia_hash o 60 mln wierszy, wskazuj¡cy na równolegle wykonywane zª¡cze« na poziomie partycji.
18
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
75
Plan wykonania zapytania nr 4 dla tabeli zamowienia_list o 60 mln wierszy, potwierdzaj¡cy u»ycie mechanizmu zrównoleglenia i przycinania partycji. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
19
79
Plan wykonania zapytania nr 2 dla tabeli zamowienia_inter o 60 mln wierszy, potwierdzaj¡cy u»ycie mechanizmu zrównoleglenia i przycinania partycji. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
20
83
Plan wykonania zapytania nr 5 dla tabeli zam_range_hash o 60 mln wierszy, wykorzystanie dwóch mechanizmów poprawiaj¡cych wydajno±¢ - przycinania partycji oraz zª¡cze« na poziomie partycji. . . . . .
4
90
1
Wst¦p W dzisiejszych czasach coraz wi¦cej rm stoi przed wyzwaniem obsªugi tzw.
Bardzo Du»ych Baz Danych (ang. Very Large Databases - VLDB). Nie ma jednoznacznej odpowiedzi na pytanie, jak¡ baz¦ danych mo»emy uzna¢ za bardzo du»¡. Kryteria VLDB do±¢ szybko zmieniaj¡ si¦ w czasie. Wiadomo jednak, »e w miar¦ przyrostu danych, wydajno±¢ bazy danych maleje. Dost¦p do danych staje si¦ wolniejszy, zapytania s¡ wykonywane znacznie dªu»ej ni» w pocz¡tkowej fazie eksploatacji systemu. Równie» administracja okazuje si¦ znacznie bardziej wymagaj¡ca, a mo»e nawet kosztowna. Problematyczne staje si¦ archiwizowanie nieu»ywanych danych. Równocze±nie operacje zwi¡zane z wykonywaniem kopii zapasowej czy odtwarzaniem zajmuj¡ coraz wi¦cej czasu. Wspóªcze±nie »adne powa»ne przedsi¦biorstwo nie mo»e pozwoli¢ sobie na odczuwalny przestój w dost¦pno±ci przechowywanych w bazie danych. Obecnie jeste±my równie» ±wiadkami szybkich zmian w wymaganiach dotycz¡cych analizy ogromnych zbiorów danych. Oprócz tradycyjnej postawy zorientowanej na gromadzenie informacji niezb¦dnych do poprawnego funkcjonowania instytucji, coraz wi¦ksz¡ rol¦ odgrywaj¡ równie» analizy zebranych danych. Wspóªcze±nie dzi¦ki szybkiej analizie bazuj¡cej na historycznej i aktualnej informacji o stanie rmy, kadra zarz¡dzaj¡ca mo»e podejmowa¢ trafniejsze decyzje o strategicznym znaczeniu dla rozwoju danego przedsi¦biorstwa. W odpowiedzi na takie zapotrzebowanie, powstaªa nowa gaª¡¹ technologii baz danych - hurtownie danych (ang. Data Warehouse). Na tego typu bazach pracuj¡ najcz¦±ciej aplikacje typu OLAP (ang. On-line Analytical Processing). Charakteryzuj¡ si¦ one stosunkowo nielicznymi, ale zlo»onymi transakcjami odczytu. Kluczowe znaczenie ma minimalizacja czasu odpowiedzi. Rozwój technologiczny stan¡ª na przeciw równie» tym wymaganiom. Na rynku istnieje wiele rozwi¡za«, w ró»nym stopniu poprawiaj¡cych szeroko rozumian¡ wydajno±¢, zarz¡dzalno±¢ i dost¦pno±¢ du»ych baz danych. W tej pracy skupi¦ si¦ na opcji partycjonowania tabel i indeksów oferowanej przez rm¦ Oracle. Mimo swojej prostoty, jest to pot¦»ny mechanizm, który zyskaª popularno±¢ w wielu przedsi¦biorstwach borykaj¡cych si¦ z wymienionymi wy»ej problemami.
5
1.1 Wprowadzenie do tematu pracy Firma Oracle po raz pierwszy zaprezentowaªa partycjonowanie tabel i indeksów w 1997 roku (Oracle8.0). Partycjonawanie jest dost¦pne dla bazy Oracle Database Enterprise Edition po wykupieniu dodatkowej licencji. Opcja ta uwa»ana jest za jedn¡ z najwa»niejszych i odnosz¡cych najwi¦kszy sukces funkcjonalno±ci bazy danych Oracle, poprawia wydajno±¢ setek tysi¦cy aplikacji bazodanowych [1]. Idea partycjonowania polega na podziale na mniejsze niezale»ne od siebie zycznie cz¦±ci du»ych struktur przechowywanych w bazie danych (tabel, indeksów, tabel zorganizowanych indeksowo) przy u»yciu klucza partycjonowania. Klucz partycjonowania mo»e skªada¢ si¦ z jednej lub wielu kolumn o uporz¡dkowanej kolejno±ci. De niowane s¡ reguªy na podstawie których serwer bazy danych decyduje, do której z partycji wstawi¢ nowy lub mody kowany rekord. Ka»da partycja mo»e mie¢ wªasn¡ nazw¦ i zyczn¡ charakterystyk¦ (np. inna przestrze« tabel oraz ró»ne warto±ci atrybutów skªadowania pctfree, pctused). Partycje mog¡ by¢ równie» opcjonalnie dzielone na podpartycje. Wymagane jest, aby partycja tabeli miaªa te same atrybuty logiczne (kolumny, klucze, wi¦zy integralno±ci) co caªa tabela oraz ka»da partycja indeksu musi indeksowa¢ te same kolumny co caªy indeks. Partycjonowanie poleca si¦ rozwa»y¢, gdy tabela staje si¦ wi¦ksza ni» kilka GB albo tabela przechowuje dane historyczne, które nale»y archiwizowa¢ [2]. Partycjonowanie jest na ogóª kojarzone ze ±rodowiskami hurtowni danych, czyli bazami analitycznymi, zazwyczaj znacznie wi¦kszymi ni» operacyjne. Te ±rodowiska s¡ wyj¡tkowo nara»one na problem wynikaj¡cy z obsªug¡ du»ych ilo±ci danych. Dane hurtowni równie» w naturalny sposób podlegaj¡ podziaªowi wzgl¦dem czasu czy lokalizacji geogra cznej, co stanowi typowe kryterium tworzenia partycji. W hurtowni nietrudno o du»¡ tabel¦, która obejmuje zakresem kilka/kilkana±cie lat dziaªalno±ci rmy. Je»eli taka tabela jest partycjonowana z podziaªem na lata b¡d¹ miesi¡ce, mo»na najstarsze z nich (najrzadziej u»ywane) ulokowa¢ w wolniejszych i ta«szych regionach pami¦ci, przez co dysponowa¢ zasobami w sposób bardziej ekonomiczny. Partycjonowanie niekiedy mo»na równie» z powodzeniem zastosowa¢ w klasycznych sysytemach informatycznych typu OLTP (ang. On-line Transaction Processing), charakteryzuj¡cych si¦ obci¡»eniem du»¡ liczb¡ prostych transakcji zapisu i odczytu.
6
1.2 Cel i motywacja pracy Celem pracy jest analiza mo»liwo±ci opcji partycjonowania tabel i indeksów w Oracle Database ze szczególnym uwzgl¦dnieniem nowych cech partycjonowania w wersjach 11g i 11gR2 oraz okre±lenie jej potencjalnego zastosowania. Cz¦±¢ teoretyczna pracy zawiera szeroki opis technik partycjonowania oferowanych przez Oracle oraz przedstawia korzy±ci wynikaj¡ce z ich u»ycia. Nast¦pne rozdziaªy zawieraj¡ opis stworzonego ±rodowisko badawcze oraz wyniki wyniki przeprowadzonych testów. Motywacj¡ do napisania pracy byªa ch¦¢ dokªadniejszego zapoznania si¦ z funkcjonalno±ciami bazy danych Oracle oraz poszerzenia wiedzy na temat oferowanych przez t¦ rm¦ rozwi¡za« technologicznych. Temat pracy - partycjonowanie tabel i indeksów - dotyczy uznanej i dopracowanej opcji Oracle Database, która stanowi dobry przykªad solidno±ci rmy. Kolejne wersje systemu Oracle Database s¡ wzbogacane o nowe mo»liwo±ci partycjonowania. W momencie wyboru tematu pracy aktualn¡ wersj¡ Oracle Database byªa 11gR2. Teraz dost¦pna jest ju» nowa wersja systemu - Oracle Database 12c. Informacje o opcji partycjonowania w 12c mo»na znale¹¢ w Magazynie Oracle z maja/czerwca 2014 roku [6]. Oracle jest uwa»any za jedno z najwa»niejszych ±wiatowych przedsi¦biorstw informatycznych. Z ich rozwi¡za« korzystaj¡ rmy o ró»nym pro lu bran»owym, z 145 pa«stw [3]. Ze wszystkich systemów zarz¡dzania baz¡ danych dost¦pnych na rynku, oprogramowanie oferowane przez Oracle jest najcz¦±ciej wybierane. Dochód rmy w 2013 roku wyniósª ponad 10 miliardów USD[4], a na badania i rozwój przeznaczane jest rocznie ponad 5 mld USD [5]. Oracle wypracowaª sobie stabiln¡ i presti»ow¡ pozycj¦ na rynku informatycznym dzi¦ki dynamicznemu rozwojowi, naciskowi na wydajno±¢ i niezawodno±¢ tworzonych technologii oraz umo»liwieniu klientom licencjonowania wygodnej usªugi asysty technicznej, 24/7 wsparcia do wykupionego oprogramowania.
1.3 Ukªad i zakres pracy Rozdziaª 1: Wst¦p Rozdziaª zawiera wprowadzenie teoretyczne oraz ogólny opis opcji partycjonowania tabel i indeksów w Oracle. Zawarte zostaªy równie» informacje dotycz¡ce zakresu tematyki pracy oraz przedstawiono motywacje autorki do jej napisania.
7
Rozdziaª 2: Opcja partycjonowania w Oracle Database W tym rozdziale autorka przedstawiªa potencjalne korzy±ci wynikaj¡ce z u»ycia partycjonowania, wymieniªa i opisaªa poszczególne metody z podziaªem na tradycyjne (do wersji Oracle 10g) i nowsze, wprowadzone od Oracle Database 11g. Opis ka»dej z metod zawiera równie» informacje o ich typowym, zalecanym przez Oracle wykorzystaniu. W rozdziale zaprezentowano równie» perspektywy przydatne do monitorowania partycji.
Rozdziaª 3: rodowisko testów Rozdziaª ma na celu przedstawienie ±rodowiska testowego, na którym przeprowadzone zostaªy badania opcji partycjonowania tabel i indeksów. W dalszej cz¦±ci autorka zamie±ciªa opis modelu relacyjnego, który zostaª potraktowany jako bazowy do przeprowadzania bada« oraz metod¦ generacji danych testowych. W rozdziale zostaªa zaprezentowana równie» aplikacja, która zostaªa stworzona w celu pomocy w przeprowadzeniu testów opcji partycjonowania.
Rozdziaª 4: Testy opcji partycjonowania w Oracle Database 11gR2 W tym rozdziale znajduje si¦ opis oraz wyniki testów w ramach bada« efektywno±ci partycjonowania tabel i indeksów w Oracle Database 11gR2. Przypadki testowe uwzgl¦dniaj¡ ró»ne metody partycjonowania i zostaªy wykonane na dwóch rozmiarach danych.
8
2
Opcja partycjonowania w Oracle Database
2.1 Korzy±ci Partycjonowanie tabel oraz indeksów mo»e przynie±¢ wiele ró»nego rodzaju potencjalnych korzy±ci. Ten rozdziaª zawiera ich opis.
2.1.1
Efektywne administrowanie
Podziaª du»ego obiektu bazodanowego na wiele mniejszych daje administratorowi elastyczno±¢ i kontrol¦ nad partycjonowanymi obiektami. Poniewa» dane tabeli partycjonowanej s¡ przechowywane w cz¦±ciach, ªadowanie oraz usuwanie danych w partycjach jest szybsze i efektywniejsze ni» w przypadku du»ej tabeli. Cz¦sto dodanie nowej partycji jest bardziej opªacalne ni» mody kowanie caªej tabeli. Równie» operacje wykonywania kopii zapasowej i odtwarzania przeprowadza si¦ sprawniej. Mo»na skorzysta¢ z wi¦kszej liczby opcji tworzenia kopii zapasowej i odtwarzania partycji, ni» mamy do dyspozycji w przypadku jednej wielkiej tabeli. Partycjonowanie w naturalny sposób mo»e równie» odgrywa¢ znacz¡c¡ rol¦ w procesie ILM (ang. Information Lifecycle Management), poniewa» ka»da z partycja mo»e by¢ zarz¡dzana indywidualnie i przechowywana na ró»nych typach urz¡dze«. Cz¦±¢ danych (partycji), które wymagaj¡ archiwizacji, mo»na przenie±¢ na wolniejsze, ta«sze no±niki. Inne, regularnie u»ytkowane mo»na ulokowa¢ na no±nikach kosztowniejszych, ale zapewniaj¡cych lepsz¡ jakos¢ i szybko±¢ dost¦pu. Dodatkowo, poniewa» z zasady partycje mog¡ ró»ni¢ si¦ parametrami zycznymi, mo»na równie» dokona¢ kompresji wybranych partycji tabeli [7]. Taka taktyka pozwala na ekonomiczne i wydajne zarz¡dzanie zasobami. Kolejn¡ przydatn¡ cech¡ partycjonowania jest mo»liwo±¢ gromadzenia statystyk zarówno na poziomie pojedynczej partycji, jak i caªej tabeli (ang. Incremental Global Statistics). Gdy fragment tabeli partycjonowanej ulega mody kacji, statystyki s¡ od±wie»ane w sposób przyrostowy analizowane s¡ tylko te partycje, w których nast¡piªy mody kacje. Opcja ta wymaga ustawienia parametrów DBMS_STATS tabeli partycjonowanej: GRANULARITY na AUTO i INCREMENTAL na TRUE [8]. Dzi¦ki takim ustawieniom mo»na zredukowa¢ czas zbierania statystyk, co jest szczególnie zauwa»alne przy du»ej liczbie partycji [9].
9
2.1.2
Bezpiecze«stwo, dost¦pno±¢
Fizyczny podziaª danych powoduje niezale»no±¢ partycji, a co za tym idzie, równie» zwi¦ksza poziom bezpiecze«stwa i dost¦pno±ci bazy. Administrator bazy danych mo»e okre±li¢, »e ka»da z partycji ma by¢ przechowywana w innej przestrzeni tabel, które mog¦ znajdowa¢ si¦ na ró»nych no±nikach danych. Strategia ta umo»liwia tworzenie kopii zapasowych i odzyskiwania ka»dej z partycji niezale»nie od innych cz¦±ci tabeli. W przypadku awarii bazy, mo»liwe staje si¦ odzyskanie tylko jej aktualnej, aktywnej cz¦±ci, a reszt¡ w tym momencie nie u»ytkowanych danych, zebranych w innych partycjach mo»na zaj¡¢ si¦ w dogodnym momencie. W ten sposób minimalizuje si¦ czas niedost¦pno±ci bazy danych. W razie awarii sprz¦tu, np. dysku uniemo»liwiony jest dost¦p tylko do partycji na tym dysku, natomiast partycje znajduj¡ce si¦ na nieuszkodzonych dyskach pozostaj¡ nadal aktywne.
2.1.3
Przycinanie partycji (ang. partitioning pruning)
Pomimo i» partycjonowanie jest postrzegane przede wszystkim jako element decyduj¡cy o dost¦pno±ci i ªatwo±ci zarz¡dzania baz¡ danych, to ma ono równie» niebagatelny wpªyw na wydajno±¢ u»ytkowania bazy. Jednym z najprzydatniejszych efektów zwi¡zanych z partycjonowaniem jest przycinanie partycji (ang. partitioning pruning). Jest to prosty mechanizm mog¡cy poprawi¢ szybko±¢ wykonywania zapytania. Przycinanie partycji jest ±ci±le zwi¡zane z dziaªaniem optymalizatora zapyta«, którego zadaniem jest analiza klauzul FROM oraz WHERE instrukcji SQL, pod k¡tem wyeliminowania z procesu wyszukiwania tych partycji, które na pewno nie zawieraj¡ »¡danych danych. W przypadku gdy optymalizator wykryje zapytanie si¦gaj¡ce do partycjonowanej tabeli i warunki selekcji w zapytaniu odpowiadaj¡ reguªom zde niowanym dla pewnej grupy partycji, to wygeneruje plan wykonania zapytania pomijaj¡cy te partycje, dla których ten warunek nie jest speªniony. Warto zaznaczy¢, i» mechanizm przycinania partycji mo»e by¢ ª¡czony z dost¦pem do indeksu. Gdy indeks jest podzielony wedªug innych kolumn ni» odpowiadaj¡ca mu tabela, wówczas niepotrzebne partycje indeksu zostan¡ odrzucone, nawet je±li partycje odpowiadaj¡cej mu tabeli pozostan¡. Technika ta mocno redukuje ilo±¢ danych pobieranych z dysku, a tak»e skraca czas przetwarzania zapytania SQL, co ma bezpo±redni wpªyw na zwi¦kszenie wydajno±ci. Przycinanie partycji sprawdza
10
si¦ szczególnie np. w ±rodowiskach hurtowni danych, gdzie wyszukiwanie informacji nale»y do najpowszechniejszych dziaªa«. Mechanizm ten wspóªpracuje z wszystkimi innymi technikami Oracle poprawiaj¡cymi wydajno±¢.
2.1.4
Zª¡czenia partycji (ang. partition-wise joins)
W wyniku stosowania partycjonowania mo»na udoskonali¢ wydajno±¢ zª¡czania wielu tabel, stosuj¡c w tym celu technik¦ partycjonowania na poziomie zª¡cze« (ang. partition-wise joins). Technika ta mo»e by¢ stosowa¢, gdy przynajmniej jedna z ª¡czonych tabel jest partycjonowana wedªug klucza zª¡czenia. Najbardziej jednak korzystne s¡ tzw. full partition-wise joins, kiedy obie tabele s¡ partycjonowane wedªug kolumny, na której wykonywane jest zª¡czenie. Partycjonowanie na poziomie zª¡cze« powoduje podziaª du»ych zª¡cze« na mniejsze obejmuj¡ce kolejne pary odnosz¡cych sie do siebie partycji. Dzi¦ki temu caªkowite zª¡czenie mo»e by¢ wykonane w krótszym czasie. Proces ª¡czenia par partycji mo»na dodatkowo zrównolegli¢. Poszczególne zª¡czenia mog¡ by¢ wykonywane przez równolegle pracuj¡ce procesy lub serwery. Umo»liwia to optymalne wykorzystanie mocy obliczeniowej.
Rysunek 1: Równolegªe wykonanie zª¡cze« na partycjach. Opracowano na podstawie rysunku Figure 3-1 Parallel Execution of a Full Partition-wise Join [10]
2.1.5
Zrównoleglenie, skalowalno±¢
Równie» polecenia DML mog¡ by¢ wykonywane równolegle dla wielu partycji. Mechanizm ten w istotny sposób wpªywa na logik¦ transakcji, blokad i zu»ycia pami¦ci, dlatego musi by¢ jawnie wª¡czony poleceniem ALTER SESSION ENABLE
11
PARALLEL DML [11]. Pomaga to zredukowa¢ czas odpowiedzi na zapytania intensywnie korzystaj¡ce z bazy danych. Warto zauwa»y¢, »e dobrze zaprojektowane u»ycie opcji partycjonowania mo»e doprowadzi¢ do tego, »e wydajno±¢ wielu zapyta« zostanie niemal uniezale»niona od przyrostu danych.
2.2 Metody partycjonowania do wersji Oracle 10g 2.2.1
Partycjonowanie Zakresowe (ang. Range Partitioning)
Przy tej metodzie rozdziaª rekordów pomi¦dzy partycje odbywa si¦ wedªug przynale»no±ci warto±ci kolumny-klucza do prede niowanych przedziaªów. Oznacza to, »e do danej partycji tra aj¡ rekordy ze ±ci±le okre±lonego dla niej zakresu, np. dat albo liczb. Dla ka»dej z partycji podaje si¦ najwi¦ksz¡ warto±¢ klucza partycjonowania jak¡ mo»e przyj¡¢ rekord do niej nale»¡cy (podajemy wyª¡cznie maksymaln¡ warto±¢ zakresu). Warto±¢ minimalna jest niejawnie okre±lona przez Oracle na podstawie de nicji poprzednich partycji. Je±li warto±¢ atrybutu partycjonuj¡cego dla wstawianego rekordu nie pasuje do »adnego z okre±lonych zakresów, wówczas system zgªasza bª¡d. Nie jest jednak konieczne okre±lanie maksymalnego zakresu dla ostatniej partycji. Sªowo kluczowe maxvalue wskazuje systemowi Oracle, aby korzystaª z tej partycji w odniesieniu do wszystkich danych, które nie mog¡ by¢ zapisane we wcze±niejszych partycjach. Kolejne warto±ci klucza partycjonowania s¡ zwykle zapisywane w tej samej partycji.
Listing 1: Przykªad tworzenia tabeli partycjonowanej zakresowo.
CREATE TABLE zamowienie ( zamowienie_id koszt_zamowienia data_zamowienia
NUMBER (5) ,
NUMBER (10) , DATE )
PARTITION BY RANGE ( data_zamowienia )( PARTITION zam_sty2013 VALUES LESS THAN ( TO_DATE ( '02/01/2013 ' , ' MM / DD / YYYY ')) , PARTITION zam_lut2013 VALUES LESS THAN ( TO_DATE ( '03/01/2013 ' , ' MM / DD / YYYY ')) , PARTITION zam_mar2013 VALUES LESS THAN ( TO_DATE ( '04/01/2013 ' , ' MM / DD / YYYY ')) , PARTITION zam_inne VALUES LESS THAN MAXVALUE );
12
Rysunek 2: Partycjonowanie zakresowe. Opracowano na podstawie rysunku Figure
2-2 List, Range, and Hash Partitioning [2]
2.2.2
Partycjonowanie Mieszaj¡ce (ang. Hash Partitioning)
W tej metodzie przynale»no±¢ do partycji jest determinowana przez funkcj¦ mieszaj¡c¡ dziaªaj¡c¡ na kluczu partycji, któr¡ tworzy Oracle bez udziaªu u»ytkownika. Im bardziej ró»norodne warto±ci klucza partycjonowania, tym lepsza dystrybucja wierszy w partycjach. Partycjonowanie mieszaj¡ce cz¦sto rozmieszcza zbiory rekordów w wi¦kszej liczbie partycji ni» metoda zakresowa, potencjalnie zmniejszaj¡c prawdopodobie«stwo opó¹nie« wej±cia-wyj±cia. Ze wzgl¦du na sposób implementowania odwzorowania partycji w Oracle, zalecane jest aby liczba partycji w tabeli byªa pot¦g¡ 2, co pomaga uzyska¢ równ¡ dystrybucj¦ danych. Podobnie jak w przypadku partycjonowania zakresowego mo»na okre±li¢ nazw¦ (mo»e tez by¢ generowana automatycznie przez system) ka»dej z partycji oraz wykorzystywan¡ przez ni¡ przestrze« tabel. Je±li liczba partycji przekracza liczb¦ przestrzeni tabel, to partycje s¡ umieszczane w kolejnych przestrzeniach tabel za pomoc¡ algorytmu karuzelowego (ang. round-robin). Ten typ partycjonowania jest najcz¦±ciej u»ywany, kiedy zale»y nam na równomiernym rozkªadzie wierszy mi¦dzy partycje, ale nie jeste±my w stanie sami tego zagwarantowa¢.
13
Listing 2: Przykªad tworzenia tabeli partycjonowanej metod¡ mieszaj¡c¡.
CREATE TABLE zamowienie ( zamowienie_id
NUMBER (5) ,
koszt_zamowienia data_zamowienia
NUMBER (10) , DATE
) PARTITION BY HASH ( zamowienie_id ) PARTITIONS 4 STORE IN ( z1 , z2 , z3 , z4 );
Rysunek 3: Partycjonowanie mieszaj¡ce. Opracowano na podstawie rysunku Figure
2-2 List, Range, and Hash Partitioning [2]
2.2.3
Partycjonowanie Listowe (ang. List Partitioning)
Przy partycjonowaniu listowym dla ka»dej z partycji wskazuje si¦ wszystkie mo»liwe warto±ci atrybutu partycjonuj¡cego wstawianych do niej wierszy. Je±li klucz partycjonowania rekordu (dla tej metody jest to zawsze jedna kolumna) nie pasuje do warto±ci »adnej z partycji, wówczas system zgªasza bª¡d. De nicj¦ tabeli partycjonowanej mo»na jednak rozszerzy¢ o partycj¦ umo»liwiaj¡c¡ przechowywanie wszystkich innych warto±ci. Przy de niowaniu takiej partycji wykorzystuje si¦ sªowo kluczowe default. Partycjonowanie zakresowe jest zazwyczaj nieprzydatne w przypadku pojedynczych warto±ci, które nie tworz¡ naturalnego i ci¡gªego zakresu (np. nazwy miast, kody pocztowe). Z kolei partycjonowanie mieszaj¡ce nie sprawdza si¦, gdy trzeba in-
14
dywidualne warto±ci przypisa¢ do konkretnej partycji, poniewa» ta metoda, z natury dziaªania, mo»e mapowa¢ kilka powi¡zanych ze sob¡ warto±ci na ró»ne partycje. W takich przypadkach warto zastosowa¢ partycjonowanie listowe.
Listing 3: Przykªad tworzenia tabeli partycjonowanej listowo.
CREATE TABLE zamowienie ( zamowienie_id koszt_zamowienia data_zamowienia
NUMBER (5) , NUMBER (10) , DATE ,
miejsce_zam VARCHAR2 (30) ) PARTITION BY LIST ( miejsce_zam )( PARTITION mazowieckie VALUES ( ' Warszawa ' , ' Radom ' , ' Plock ') , PARTITION slaskie VALUES ( ' Czestochowa ' , ' Katowice ' , ' Cieszyn ') , PARTITION malopolskie VALUES ( ' Zakopane ' , ' Tarnow ' , Krakow ) , PARTITION inne VALUES ( DEFAULT ) );
Rysunek 4: Partycjonowanie listowe. Opracowano na podstawie rysunku Figure 2-2
List, Range, and Hash Partitioning [2]
2.2.4
Partycjonowanie Dwupoziomowe (ang. Composite Partitioning)
Partycje uzyskane za pomoc¡ partycjonowania zakresowego mog¡ podlega¢ dalszemu podziaªowi za pomoc¡ partycjonowania mieszaj¡cego albo listowego. W przypadku du»ych tabel jest to opªacalny sposób dzi¦ki, któremu mo»na poª¡czy¢ zalety
15
ró»nych technik partycjonowania w zale»no±ci od potrzeb. Na przykªad partycjonowanie zakresowo-mieszaj¡ce zapewnia lepszy poziom zarz¡dzania wynikaj¡cy z podziaªu zakresowego i wydajne rozmieszczenie danych wynikaj¡ce z metody mieszaj¡cej. Partycjonowanie dwupoziomowe w odniesieniu do niektórych zapyta« pozwala na zwi¦kszenie korzy±ci wynikaj¡cych z przycinania partycji. S¡ to zapytania, które w klauzuli WHERE zawieraj¡ warunki selekcji opieraj¡ce si¦ na kolumnach kluczy partycji oraz subpartycji. U»ycie kolumny, na której cz¦sto wykonywane s¡ zª¡czenia, jako klucza subpartycji wspiera mechanizm zª¡cze« na poziomie partycji. Dochodzi wtedy do zª¡cze« jedynie na poziomie subpartycji. Partycjonowanie dwupoziomowe nie jest dost¦pne dla tabel o organizacji indeksowej IOT.
Listing 4: Przykªad tworzenia tabeli partycjonowanej metod¡ zakresowo-mieszaj¡c¡.
CREATE TABLE zamowienie ( zamowienie_id koszt_zamowienia data_zamowienia
NUMBER (5) , NUMBER (10) , DATE
) PARTITION BY RANGE ( data_zamowienia ) SUBPARTITION BY HASH ( zamowienie_id ) SUBPARTITION TEMPLATE ( SUBPARTITION zam1 TABLESPACE ts1 , SUBPARTITION zam2 TABLESPACE ts2 , SUBPARTITION zam3 TABLESPACE ts3 ,) ( PARTITION zam_sty2013 VALUES LESS THAN ( TO_DATE ( '02/01/2013 ' , ' MM / DD / YYYY ')) , PARTITION zam_lut2013 VALUES LESS THAN ( TO_DATE ( '03/01/2013 ' , ' MM / DD / YYYY ')) , PARTITION zam_mar2013 VALUES LESS THAN ( TO_DATE ( '04/01/2013 ' , ' MM / DD / YYYY ')) , );
16
Rysunek 5: Partycjonowanie dwupoziomowe zakresowo-mieszaj¡ce. Opracowano na podstawie rysunku Figure 2-3 Composite Partitioning [2]
2.3 Nowe w 11g metody i wªa±ciwo±ci partycjonowania 2.3.1
Partycjonowanie Interwaªowe (ang. Interval Partitioning)
Partycjonowanie interwaªowe poszerza mo»liwo±ci metody zakresowej. Eliminuje problem jawnego de niowania partycji zakresowych przez administratora. W przypadku monotonicznego wzrostu warto±ci klucza partycjonowania, kolejne partycje nie musz¡ by¢ na bie»¡co dodawane przez administratora bazy danych. System Oracle na podstawie zadanego interwaªu okre±la, do której partycji nale»y wstawi¢ nowy wiersz. Mo»na np. okre±li¢ zasad¦ tworzenia nowej partycji co miesi¡c. Pierwsz¡ partycj¦ nale»y utworzy¢ samodzielnie. Gdy warto±¢ klucza partycjonowania w nowych rekordach przekracza zakres istniej¡cych partycji, automatycznie tworzona jest nowa partycja zakresowa o zadanej szeroko±ci. Nazwa nowej partycji nadawana jest automatycznie. Istniej¡ jednak pewne dodatkowe ograniczenia dotycz¡ce tworzenia partycji interwaªowych. Klucz partycjonowania musi by¢ typu DATE lub NUMBER. Nie mo»liwe jest równie» zastosowanie tej techniki w stosunku do tabel o organizacji indeksowej - IOT. Szczególnie nale»y uwa»a¢ na tworzenie niechcianych partycji, które mog¡ powsta¢ w wyniku pomyªkowego wprowadzenia danych rekordu, np. bª¦dnej daty. W tym przypadku warto kontrolowa¢ warto±ci klucza partycjonowania przed wstawieniem nowego wiersza.
17
Listing 5: Przykªad tworzenia tabeli partycjonowanej interwaªowo.
CREATE TABLE zamowienie ( zamowienie_id
NUMBER (5) not null ,
koszt_zamowienia
NUMBER (10) not null ,
data_zamowienia
DATE not null
) PARTITION BY RANGE ( data_zamowienia ) INTERVAL ( numtoyminterval (1 , ' MONTH ')) ( PARTITION P1 VALUES LESS THAN ( TO_DATE ( '1 -1 -2013 ' , ' MM / DD / YYYY ')) , PARTITION P2 VALUES LESS THAN ( TO_DATE ( '1 -2 -2013 ' , ' MM / DD / YYYY ')) , PARTITION P3 VALUES LESS THAN ( TO_DATE ( '1 -3 -2013 ' , ' MM / DD / YYYY ')) );
2.3.2
Partycjonowanie Systemowe (ang. System Partitioning)
Partycjonowanie systemowe umo»liwia tworzenie partycji bez wskazywania klucza partycjonowania. Dla ka»dej z partycji nale»y jedynie wskaza¢ przestrze« tabel w jakiej ma si¦ znajdywa¢. Wszystkie aspekty dotycz¡ce partycji musz¡ by¢ kontrolowane przez aplikacj¦. Oznacza to, »e wymagane jest np. aby przy operacji INSERT wskaza¢ konkretn¡ partycj¦, inaczej operacja si¦ nie powiedzie. Z powodu braku wyszczególnienia kluczy partycjonowania, typowe korzy±ci wydajno±ciowe dost¦pne dla tabel partycjonowanych nie mog¡ by¢ zastosowane przy tej metodzie. Nie dziaªa mechanizm przycinania partycji ani efektywnych zª¡cze« (ang. wise-joins). Zalety wynikaj¡ce z podziaªu zycznego tabeli na mniejsze cz¦±ci jednak ci¡gle s¡ aktualne (dost¦pno±¢, bezpiecze«stwo, zarz¡dzalno±¢).
Listing 6: Przykªad tworzenia tabeli partycjonowanej systemowo.
CREATE TABLE zamowienie ( zamowienie_id
NUMBER (5) not null ,
koszt_zamowienia
NUMBER (10) not null ,
data_zamowienia
DATE not null )
PARTITION BY SYSTEM ( PARTITION P1 TABLESPACE zam1 18
PARTITION P2 TABLESPACE zam2 PARTITION P3 TABLESPACE zam3 PARTITION P3 TABLESPACE zam4 );
2.3.3
Partycjonowanie Referencyjne (ang. Reference Partitioning)
Metoda referencyjna pozwala na tworzenie partycji bazuj¡cych nie na warto±ciach z partycjonowanej tabeli, ale na odwoªaniu do klucza obcego z innej tabeli. Mo»e to by¢ przydatne przy partycjonowaniu tabel w spójny sposób, nawet je»eli nie wspóªdziel¡ one tych samych kolumn. Jest to szczególnie opªacalna metoda w przypadku, kiedy wiadomo, »e tabele b¦d¦ cz¦sto podlegaªy zª¡czeniom. Przy tym partycjonowaniu mamy doczynienia z dwoma tabelami b¦d¡cymi w relacji nadrz¦dna-podrz¦dna, które zostaj¡ logicznie tak samo partycjonowane. Na pocz¡tku nale»y utworzy¢ tabel¦ nadrz¦dn¡ rodzica, a potem tabel¦ podrz¦dn¡ dziecko z odpowiednim ograniczeniem integralno±ci klucza obcego, odwoªuj¡cego si¦ do tabeli rodzica, doª¡czaj¡c klauzul¦ partycjonowania wskazuj¡c¡ wspomniany klucz obcy. Tabela podrz¦dna dziedziczy sposób partycjonowania od tabeli nadrz¦dnej. W przypadku wcze±niejszego istnienia tabel mo»na skorzysta¢ z polecenia
alter table. Wszystkie podstawowe metody partycjonowania (zakresowe, mieszaj¡ce i listowe) s¡ dost¦pne w poª¡czeniu z partycjonowaniem referencyjnym. W przypadku partycjonowania referencyjnego administrowanie obiektami jest znacznie uªatwione, gdy» operacje utrzymania na partycji tabeli rodzica przenosz¡ si¦ kaskadowo na partycje tabeli- dziecka. W zwi¡zku z tym nie ma potrzeby wykonywania dodatkowych operacji na tabeli podrz¦dnej. Ponadto warto zauwa»y¢, »e partycjonowanie referencyjne zapobiega zb¦dnej redundancji danych, poniewa» nie ma potrzeby powielania klucza partycjonowania w tabeli podrz¦dnej.
Listing 7: Przykªad tworzenia tabeli partycjonowanej referencyjnie.
CREATE TABLE zamowienie ( zam_id
NUMBER (5) NOT NULL ,
koszt_zamowienia
NUMBER (10 ,2) NOT NULL ,
data_zam
NOT NULL ,
DATE
CONSTRAINT zamowienie_pk PRIMARY KEY ( zamowienie_id )) PARTITION BY RANGE ( data_zam ) ( PARTITION zam_sty2013 VALUES LESS 19
THAN ( TO_DATE ( '02/01/2013 ' , ' MM / DD / YYYY ')) , PARTITION zam_lut2013 VALUES LESS THAN ( TO_DATE ( '03/01/2013 ' , ' MM / DD / YYYY ')) , PARTITION zam_mar2013 VALUES LESS THAN ( TO_DATE ( '04/01/2013 ' , ' MM / DD / YYYY ')) , PARTITION zam_inne VALUES LESS THAN MAXVALUE ); CREATE TABLE pozycja ( pozycja_id nadrzedna_id nazwa
NUMBER NOT NULL , NUMBER NOT NULL , VARCHAR2 (50) NOT NULL ,
opis
VARCHAR2 (200) ,
cena
NUMBER (10 ,2) ,
CONSTRAINT pozycja_zam_pk PRIMARY KEY ( pozycja_id ) , CONSTRAINT zamowienie_pozycja_fk FOREIGN KEY ( nadrzedna_id ) REFERENCES zamowienie ( zam_id )) PARTITION BY REFERENCE ( zamowienie_pozycja_fk );
Rysunek 6: Partycjonowanie referencyjne. Opracowano na podstawie rysunku Figure
2-5 With Reference Partitioning [2]
20
2.3.4
Partycjonowanie na Bazie Kolumny Wirtualnej (ang. Virtual Column Based Partitioning)
Kolumny wirtualne zostaªy wprowadzone jako nowo±¢ Oracle 11g, w zwi¡zku z tym staªo si¦ równie» mo»liwe partycjonowanie na ich podstawie. Tym sposobem kluczem partycjonowania nie musi by¢ zyczna kolumna, ale równie» wyra»enie powstaªe na bazie jednej lub kilku istniej¡cych kolumn, przechowywane jako metadana. Kolumn wirtualnych mo»na u»ywa¢ jako kluczy partycjonowania we wszystkich z podstawowych strategii partycjonowania, jaki i partycjonowaniu interwaªowym oraz referencyjnym. Listing 8: Przykªad tworzenia tabeli partycjonowanej listowo na podstawie kolumny wirtualnej.
CREATE TABLE uzytkownicy ( id
NUMBER ,
nazwa
VARCHAR2 (20) ,
pierwsza_litera
VARCHAR2 (1)
GENERATED ALWAYS AS ( UPPER ( SUBSTR ( TRIM ( nazwa ) , 1 , 1)) ) VIRTUAL ) PARTITION BY LIST ( pierwsza_litera )( PARTITION part_a_g VALUES ( 'A ' , 'B ' , 'C ' , 'D ' , 'E ' , 'F ' , 'G ') , PARTITION part_h_n VALUES ( 'H ' , 'I ' , 'J ' , 'K ' , 'L ' , 'M ' , 'N ') , PARTITION part_o_u VALUES ( 'O ' , 'P ' , 'Q ' , 'R ' , 'S ' , 'T ' , 'U ') , PARTITION part_v_z VALUES ( 'V ' , 'W ' , 'X ' , 'Y ' , 'Z ') );
2.3.5
Partycjonowanie Dwupoziomowe w Oracle 11gR2
W Oracle Database 11gR2 dost¦pne s¡ nast¦puj¡ce kombinacje partycji dwupoziomowych na bazie podstawowych metod partycjonowania: zakresowo-zakresowe, zakresowo-listowe, zakresowo-mieszaj¡ce, listowo-mieszaj¡ce, listowo-listowe, listowomieszajace, mieszaj¡co-mieszaj¡ce. Ponadto z nowymi opcjami partycjonowania mo»na tworzy¢ partycje dwupoziomowe typu: interwaªowo-zakresowe, interwawaªowo-listowe, interwaªowo-mieszaj¡ce.
21
2.4 Partycjonowanie indeksów Partycjonowanie indeksów przynosi korzy±ci tego samego typu co partycjonowanie tabel. Dodatkowo pot¦guje mo»liwo±ci zwi¦kszenia stopnia wspóªbie»no±ci transakcji oraz minimalizuje rywalizacj¦ transakcji, poprzez rozproszenie operacji wej±cia/wyj±cia. Partycjonowanie indeksów jest równie» polecane, gdy zale»y nam na tym, aby przy przeprowadzaniu operacji dotycz¡cych konserwacji i utrzymania danych nie ingerowa¢ w caªy indeks, np. nie przebudowywa¢ caªego indeksu, po usuni¦ciu cz¦±ci danych z tabeli. Mo»liwe jest tworzenie indeksów partycjonowanych dla tabel partycjonowanych, jak i niepartycjonowanych. Tabela partycjonowana mo»e natomiast mie¢ zarówno indeks partycjonowany jaki i niepartycjonowany. Indeksy mog¡ by¢ partycjonowane niezale»nie od tabel (globalne) lub mog¡ te» by¢ uzale»nione od partycji tabel, do których nale»¡ (lokalne). W przypadku partycjonowania dwupoziomowego, indeksy subpartycji s¡ z zasady zawsze partycjonowane lokalnie.
2.4.1
Lokalny Indeks Partycjonowany
Przy partycjonowaniu indeksów lokalnie partycje indeksów s¡ zgodne z partycjami tabeli (zgodno±¢ atrybutów partycjonuj¡cych i zakresów partycji). Ka»da partycja indeksu lokalnego odpowiada tylko jednej partycji indeksowanej tabeli. Indeks tego typu jest w peªni uzale»niony od schematu partycjonowanej tabeli. Indeks bitmapowy mo»e by¢ tylko lokalny wzgl¦dem partycjonowanej tabeli. Indeks partycjonowany lokalnie mo»e by¢ unikatowy, je±li klucz partycjonowania tabeli jest podzbiorem kolumn klucza indeksu. System Oracle automatycznie zarz¡dza indeksami lokalnymi, utrzymuj¡c ich peªn¡ synchronizacj¦ z odpowiadaj¡c¡ partycj¡. Oznacza to, »e w przypadku dodania nowej partycji tabeli, automatycznie jest generowana odpowiadaj¡ca jej partycja indeksu. Usuni¦cie partycji tabeli spowoduje samoczynne usuni¦cie skorelowanej z ni¡ partycji indeksu, bez uniewa»niania »adnej innej partycji indeksu, jak w przypadku indeksu globalnego. Z tego powodu indeksy partycjonowane lokalnie s¡ uwa»ane za najprostsze w zarz¡dzaniu i utrzymaniu. Zaleca si¦ stosowanie lokalnego indeksowania je±li klucz partycjonowania jest podzbiorem klucza indeksu i priorytetem jest wygoda zarz¡dzania i przepustowo±¢
22
systemu. Dlatego indeksy lokalne s¡ polecane dla hurtowni danych czy systemów wspomagania decyzji DSS. Lokalny indeks partycjonowany mo»e byc pre ksowany lub niepre ksowany.
Indeks pre ksowany. Pierwsze kolumny indeksu bazuj¡ na kluczu partycjonowania tabeli, z zachowaniem ich kolejno±ci. Wykrywanie tego rodzaju indeksu jest mniej kosztowne. U»ycie lokalnego indeksu pre ksowanego umo»liwia pomini¦cie partycji przy u»yciu mechanizmu przycinania partycji, je±li zapytanie zawiera klucz partycjonowania w klauzuli WHERE.
Indeks niepre ksowany. Pierwsze kolumny indeksu nie bazuj¡ na atrybutach partycjonuj¡cych tabeli. Cz¦sto jest u»ywany do indeksowania kolumny, która nie wchodzi w skªad klucza partycjonowania tabeli, a chcemy, aby indeks byª partycjonowany na tym samym kluczu co tabela podstawowa. Nie wspomaga dodatkowo mechanizmu przycinania partycji. Nie jest zalecany w aplikacjach typu OLTP. Jest jednak skuteczny w dost¦pie do danych, które obejmuj¡ wiele partycji, poniewa» operacje te mo»na zrównolegli¢. Oznacza to, »e indeks lokalny niepre ksowany warto zastosowa¢ w systemach wspomagania decyzji. Cz¦sto wykonywane w nich zapytania zakresowe bazuj¡ce na kluczu indeksu mog¡ by¢ szybciej przeprowadzane, gdy» partycje indeksu mog¡ by¢ przeszukiwane równolegle. [10].
Listing 9: Przykªad tworzenia indeksu partycjonowanego lokalnie.
CREATE INDEX zamowienie_local ON zamowienie ( zamowienie_id ) LOCAL ;
2.4.2
Indeks Globalny
Indeks globalny mo»e by¢ partycjonowany lub niepartycjonowany. Indeksowanie globalne zalecane jest, gdy indeks jest unikatowy i priorytetem jest minimalizacja czasu odpowiedzi na »¡danie u»ytkownika, dlatego takie indeksowanie mo»e by¢ z korzy±ci¡ stosowane w aplikacjach typu transakcyjnego.
23
Rysunek 7: Indeks partycjonowany lokalnie. Opracowano na podstawie rysunku Fi-
gure 2-6 Local Partitioned Index [2]
Indeks globalny partycjonowany. Indeks partycjonowany globalnie jest jawnie de niowany i podlega partycjonowaniu metod¡ mieszaj¡c¡ lub zakresow¡. Klucz indeksu mo»e ró»ni¢ si¦ od klucza partycjonowania tabeli. Schemat partycjonowania indeksu mo»e by¢ wi¦c niezale»ny od partycji tabeli. Oracle nie wspiera niepre ksowanych globalnych indeksów partycjonowanych. Zarz¡dzanie globalnymi partycjonowanymi indeksami jest szczególnie skomplikowane. Przenoszenie, usuwanie rekordów wybranej partycji tabeli wpªywa na wszystkie partycje indeksu. Ogranicza to niezale»no±¢ partycji tabeli. Konsekwentnie próba przywrócenia danej partycji tabeli wymaga ponownego stworzenia wszystkich partycji indeksu globalnego. Indeksów globalnych nale»y wi¦c unika¢ w systamach bazuj¡cych na strategii rolling window, gdy regularnie co okres czasu tworzy si¦ now¡ partycj¦, a najstarsz¡ si¦ archiwizuje i usuwa. Domy±lnie wiele operacji sªu»¡cych zarz¡dzaniu indeksami globalnymi powoduje, »e danej partycji zostaje nadany status UNUSABLE. Mo»na zapobiec takiemu zachowaniu przy pomocy instrukcji UPDATE GLOBAL INDEXES, wtedy partycje indeksów globalnych s¡ aktualizowane. Konsekwencj¡ tej zmiany jest jednak dªu»sze wykonywanie instrukcji DDL. Generowanie globalnego indeksu partycjonowanego
24
zakresowo
uwzgl¦dnia re-
guªy podobne do tych, których u»ywa si¦, tworz¡c tabele partycjonowane zakresowo. Najwy»sza partycja indeksu musi mie¢ granic¦ okre±lon¡ jako maxvalue. To gwarantuje, »e wszystkie wiersze tabeli b¦d¡ miaªy przydzielony indeks. Warto jednak zauwa»y¢, »e nie mo»na w tradycyjny sposób doda¢ nowej partycji o najwy»szym zakresie, poniewa» jest on explicite okre±lony przez parametr maxvalue. W tym wypadku nale»y skorzysta¢ z instrukcji INDEX SPLIT PARTITION. Próba usuni¦cia niepustej partycji indeksu instrukcj¡ ALTER INDEX DROP PARTITION spowoduje, »e nast¦pna wg zakresu partycja zostanie oznaczona jako UNUSABLE. Nie mo»na oczywi±cie usun¡¢ najwy»szej partycji indeksu. Podobnie jak w przypadku globalnych indeksów partycjonowanych zakresowo, instrukcje tworzenia globalnych indeksów partycjonowanych metod¡
mieszaj¡c¡
maj¡ skªadni¦ wspóln¡ z poleceniami generowania tabel partycjonowanych t¡ sam¡ metod¡. Indeksy globalne partycjonowane t¡ technik¡ mog¡ zwi¦kszy¢ wydajno±¢ w sytuacjach, w których w ±rodowisku OLTP niewielka liczba bloków ko«cowych w¦zªów indeksu niepartycjonowanego poddawana jest du»ej rywalizacji. Z tego typu indeksów w znacz¡cym stopniu mog¡ korzysta¢ zapytania, które w klauzuli WHERE maj¡ znak równo±ci lub operator IN. Listing 10: Przykªad tworzenia indeksu globalnego partycjonowanego metod¡ mieszaj¡c¡.
CREATE INDEX zamowienie_global ON zamowienie ( zamowienie_id ) GLOBAL PARTITION BY HASH ( zamowienie_id ) ( PARTITION Z1 tablespace idx_1 PARTITION Z2 tablespace idx_2 PARTITION Z3 tablespace idx_3 PARTITION Z4 tablespace idx_4 );
Indeks globalny niepartycjonowany. Generalnie indeks globalny niepartycjonowany ma wªa±ciwo±ci zwykªego indeksu tabeli niepartycjonowanej. Równie» jego tworzenie przebiega tak samo jak generowanie zwykªego indeksu (skªadnia instrukcji jest identyczna). Tego typu indeksowanie jest szczególnie polecane w przypadku systemów OLTP.
25
Rysunek 8: Globalny indeks partycjonowany tabeli partycjonowanej. Opracowano na podstawie rysunku Figure 2-7 Global Partitioned Index [2]
Rysunek 9: Zwi¡zek mi¦dzy niepartycjonowanym indeksem globalnym i tabel¡ partycjonowan¡. Opracowano na podstawie rysunku Figure 2-8 Global Nonpartitioned
Index [2]
26
2.5 Perspektywy dotycz¡ce partycji tabel i indeksów W przedstawionej poni»ej tabeli zawarte s¡ wszystkie perspektywy sªownika danych dotycz¡ce partycji tabel i indeksów znajduj¡cych si¦ w bazie danych (pre ks DBA). Informacje te mog¡ by¢ przydatne do monitorowania poszczególnych partycji. Ka»da z wymieninych partycji ma równie» swój ekwiwalent przedstawiaj¡cy dane dast¦pne dla aktywnego u»ytkownika (w nazwie pre ks ALL) lub dane o obiektach, których wªa±cicielem jest aktualny u»ytkownik (w nazwie pre ks USER). Tabela powstaªa na podstawie Table 4-4 Views With Information Speci c to Partitioned
Tables and Indexes [12].
Perspektywa
Opis
DBA_PART_TABLES
Wy±wietla informacje dotycz¡ce wszystkich tabel partycjonowanych, np. metoda partycjonowania, status, liczba partycji, domy±lne warto±ci parametrów skªadowania.
DBA_TAB_PARTITIONS
Podaje informacje o poszczególnych partycjach tabel, takie jak: warto±ci parametrów skªadowania, statystyki (DBMS_STATS).
DBA_TAB_SUBPARTITIONS
Podaje informacje o poszczególnych podpartycjach tabel, takie jak: warto±ci parametrów skªadowania, statystyki (DBMS_STATS).
DBA_PART_KEY_COLUMNS
Wskazuje klucze partycjonowania poszczególnych partycjnowanych obiektów (tabel lub indeksów).
DBA_SUBPART_KEY_COLUMNS
Wskazuje
klucze
podpartycji
poszczególnych
obiektów (tabel lub indeksów) partycjonowanych dwupoziomowo. DBA_PART_COL_STATISTICS
Przedstawia statystyki partycji dotycz¡ce kolumn, wskazuje na rodzaj zebranych statystyk oraz informuje o typie lub braku histogramu.
DBA_SUBPART_COL_STATISTICS
Przedstawia statystyki podpartycji dotycz¡ce kolumn, wskazuje na rodzaj zebranych statystyk oraz informuje o typie lub braku histogramu.
27
DBA_PART_HISTOGRAMS
Przedstawia dane stworzonych histogramów dla danej partycji.
DBA_SUBPART_HISTOGRAMS
Przedstawia dane stworzonych histogramów dla danej podpartycji.
DBA_PART_INDEXES
Wy±wietla informacje dotycz¡ce wszystkich indeksów partycjonowanych, np. metoda partycjonowania, typ indeksu, liczba partycji, domy±lne warto±ci parametrów skªadowania.
DBA_IND_PARTITIONS
Podaje informacje dotycz¡ce poszczególnych partycji indeksów, takie jak: status, warto±ci parametrów skªadowania, statystyki (DBMS_STATS).
DBA_IND_SUBPARTITIONS
Podaje informacje dotycz¡ce poszczególnych podpartycji indeksów, takie jak: status, warto±ci parametrów skªadowania, statystyki (DBMS_STATS).
DBA_SUBPARTITION_TEMPLATES Podaje informacje dotycz¡ce istniej¡cych szablonów padpartycji.
28
3
rodowisko testów opcji partycjonowania
3.1 Cel i zakres testów Testy maj¡ na celu sprawdzenie efektywno±ci opcji partycjonowania tabel i indeksów w Oracle Database 11gR2 przy uwzgl¦dnieniu ró»nych rozmiarów danych testowych. Pod uwag¦ zostaªy wzi¦te ró»ne techniki partycjonowania bazuj¡ce na tym samym modelu relacyjnym. Efektywno±¢ zostaªa mierzona przy pomocy aplikacji testowej, która ª¡czy si¦ z wybran¡ baz¡ danych oraz wskazuje czas odpowiedzi na wyniki zapyta« typu SELECT.
3.2 Opis ±rodowiska testowego Badania zostaªy przeprowadzone przy u»yciu trzech maszyn wirtualnych, do których mo»na uzyska¢ dost¦p poprzez wydziaªowy serwer galera.ii.pw.edu.pl. Dwie maszyny orem1.ii.pw.edu.pl oraz orem2.ii.pw.edu.pl pracuj¡ pod kontrol¡ systemu Oracle Linux Server release 6.4. Na nich zostaªo zainstalowane oprogramowanie Oracle Database Enterprise Edition 11gR2 z opcj¡ partycjonowania oraz utworzone testowe bazy danych. Trzecia maszyna orem5.ii.pw.edu.pl z systemem Windows XP peªni charakter serwera aplikacyjnego ª¡cz¡cego si¦ z bazami danych z orem1 lub orem2 (na orem5 zostaªa stworzona oraz wykonywana aplikacja testowa). Inne wa»ne narz¦dzia z których jeszcze korzystaªam w trakcie pisania pracy, to:
•
PuTTY release 0.60 - wykorzystywany do ª¡czenia si¦ przez protokóª SSH z serwerem galera.ii.pw.edu.pl, z którego nast¦pnie uzyskiwano poª¡czenie przez ssh z maszynami wirtualnymi orem1, orem2 oraz orem5;
•
Xming X Server for Windows - umo»liwia zdalny dost¦p w sesji gra cznej przy u»yciu PuTTy;
•
Database Con guration Assistant (DBCA) - utworzenie baz danych;
•
Oracle Net Manager (netmgr) kon guracja procesów nasªuchuj¡cych ang. listener umo»liwiaj¡cych zdalne ª¡czenie z bazami danych przez sie¢;
•
Eclipse IDE for Java EE Developers - ±rodowisko programistyczne przeznaczone do j¦zyka Java, w którym zostaªa napisana aplikacja testuj¡ca;
29
Rysunek 10: Lista gªównych parametrów kon guracyjnych wygenerowana przez narz¦dzie DBCA przy okazji tworzenia bazy danych dbtest na maszynie orem1.
30
Na ka»dej z linuxowych maszyn wirtualnych przy u»yciu narz¦dzia Database Con guration Assistant (DBCA) zostaªy utworzone bazy danych z pojedynczymi instancjami. Przy ich tworzeniu skorzystaªam z jednego z dost¦pnych szablonów - General Purpose or Transaction Processing dotycz¡cego bazy danych ogólnego przeznaczania. Oznacza to, »e ka»da z baz zostaªa utworzona z takimi samymi ustawieniami kon guracyjnymi (warto±ci gªównych parametrów znajduj¡ si¦ na powy»szym obrazku). Lokalne nazwy (ORACLE_SID) tych baz danych to odpowiednio dla orem1 i orem2 dbtest i dbtest2.
3.3 Relacyjny model danych Poni»ej przedstawiony jest wyj±ciowy relacyjny model danych, który stanowiª baz¦ do tworzenia kolejnych jego wariacji uwzgl¦dniaj¡cych ró»ne techniki partycjonowania tabel oraz indeksów. Model skªada si¦ z dwóch tabel, nadrz¦dnej KLIENCI oraz podrz¦dnej ZAMOWIENIA. Warto zwróci¢ uwag¦ na fakt, i» tabela ZAMO-
WIENIA zawiera kolumn¦ wirtualn¡ region tworzon¡ na podstawie klucza gªównego id_zamowienia. Do tabeli ZAMOWIENIA zostaªy równie» dodane indeksy bitmapowy na kolumnie region oraz drzewiasty na kluczu obcym id_klienta, które b¦d¡ poddawane partycjonowaniu.
Rysunek 11: Diagram relacyjny przedstawiaj¡cy bazowy model danych. Opracowanie wªasne.
31
Listing 11: Polecenie tworz¡ce niepartycjonowan¡ tabel¦ KLIENCI.
CREATE TABLE klienci ( id_klienta NUMBER (15) NOT NULL , nazwisko VARCHAR2 (25) NOT NULL , imie VARCHAR2 (15) NOT NULL , miasto VARCHAR2 (20) NOT NULL , kod NUMBER (6) NOT NULL , adres VARCHAR2 (30) NOT NULL ) TABLESPACE TB_KLIENCI ; alter table klienci add constraint klienci_pk primary key ( id_klienta ) using index TABLESPACE IND_KLIENCI ;
Listing 12: Polecenie tworz¡ce niepartycjonowan¡ tabel¦ ZAMOWIENIA
CREATE TABLE zamowienia ( id_zamowienia VARCHAR2 (25) NOT NULL , data_zam DATE NOT NULL , kwota NUMBER (6 ,2) NOT NULL , id_klienta NUMBER (15) NOT NULL , region AS ( case when substr ( id_zamowienia ,1 ,1) = 'N ' then ' NORTH ' when substr ( id_zamowienia ,1 ,1) = 'E ' then ' EAST ' when substr ( id_zamowienia ,1 ,1) = 'S ' then ' SOUTH ' when substr ( id_zamowienia ,1 ,1) = 'W ' then ' WEST ' when substr ( id_zamowienia ,1 ,1) = 'C ' then ' CENTER ' end ) , CONSTRAINT zamowienia_pk PRIMARY KEY ( id_zamowienia ) , CONSTRAINT klient_zam_fk FOREIGN KEY ( id_klienta ) REFERENCES klienci ( id_klienta )) TABLESPACE TB_ZAM ;
32
Listing 13: Polecenie tworz¡ce dodatkowe niepartycjonowane indeksy na tabeli za-
mowienia
CREATE BITMAP INDEX zam_bitmap_ind ON zamowienia ( region ) TABLESPACE IND_ZAM ; CREATE INDEX zam_fk_ind ON zamowienia ( id_klienta ) TABLESPACE IND_ZAM ; Listing 14: Polecenie tworz¡ce indeks globalnie partycjonowany metod¡ zakresow¡ na kluczu obcym id_klienta tabeli zamowienia (w odniesieniu do tabeli klienci maj¡cej 60 mln wierszy)
CREATE INDEX zam_fk_ind ON zamowienia ( id_klienta ) GLOBAL PARTITION BY RANGE ( id_klienta ) ( PARTITION p1 VALUES LESS THAN (7500000) , PARTITION p2 VALUES LESS THAN (15000000) , PARTITION p3 VALUES LESS THAN (22500000) , PARTITION p4 VALUES LESS THAN (30000000) , PARTITION p5 VALUES LESS THAN (37500000) , PARTITION p6 VALUES LESS THAN (45000000) , PARTITION p7 VALUES LESS THAN (52500000) , PARTITION p_greater VALUES LESS THAN ( maxvalue )) TABLESPACE IND_ZAM PARALLEL NOLOGGING ;
Listing 15: Indeks globalny partycjonowany zakresowo tabeli zamowienia
SQL > SELECT partition_name , num_rows , high_value FROM dba_ind_partitions WHERE index_name = ' ZAM_FK_IND '; PARTITION_NAME
NUM_ROWS HIGH_VALUE
------------------------ ---------- -----------------P1
22777687 7500000
P2
22749614 15000000
P3
22733538 22500000
P4
21421454 30000000
P5
7455619
37500000
P6
7280534
45000000
P7
7354128
52500000
P_GREATER
7253998
MAXVALUE 33
W nast¦pnych podrozdziaªach zostan¡ przedstawione skrypty tworz¡ce partycjonowane tabele i indesy, które zostaªy uwzgl¦dnione w testach wraz z informacj¡ do schematu której bazy danych nale»¡. Tabele klienci oraz zamowienia niepartycjonowane s¡ skªadowane w bazie dbtest na orem1. Zaprezentowane równie» b¦d¡ wyniki zapyta« skierowanych do odpowiednich perspektyw pokazuj¡ce rozkªad danych w poszczególnych partycjach tabel czy indeksów.
3.3.1
Partycjonowanie referencyjne (orem1)
Tabela ZAMOWIENIA_REF zostaªa partycjonowana na wzór (partycjonowanie referencyjne) tabeli KLIENCI_REF, która jest przykªadem partycjonowania listowego wedªug kolumny miasto. Ka»da z tabel zostaªa podzielona na 5 partycji oraz wraz z indeksami jest skªadowana w bazie danych dbtest na maszynie orem1. Tabele te byªy testowane w dwóch kon guracjach indeksowych:
•
tylko z niepartycjonowanymi indeksami na kluczach gªównych;
•
z niepartycjonowanymi indeksami na kluczach gªównych oraz dla tabeli ZA-
MOWIENIA_REF partycjonowanymi lokalnie indeksami bitmapowym na kolumnie region oraz drzewiastym na kluczu obcym id_klienta.
Listing 16: Polecenie tworz¡ce tabel¦ klienci_ref partycjonowan¡ listowo oraz tabel¦
zamowienia_ref partycjonowan¡ referencyjnie
CREATE TABLE klienci_ref ( id_klienta NUMBER (15) NOT NULL , nazwisko VARCHAR2 (25) NOT NULL , imie VARCHAR2 (15) NOT NULL , miasto VARCHAR2 (20) NOT NULL , kod NUMBER (6) NOT NULL , adres VARCHAR2 (30) NOT NULL ) PARTITION BY LIST ( miasto ) ( PARTITION klienci_east VALUES ( ' WARSZAWA ' , ' BIALYSTOK ' , ' LUBLIN ' , ' RADOM ') , PARTITION klienci_west VALUES ( ' WROCLAW ' , ' OPOLE ' , ' LEGNICA ' , ' ZIELONA GORA ') , PARTITION klienci_center 34
VALUES ( ' LODZ ' , ' POZNAN ' , ' BYDGOSZCZ ' , ' TORUN ') , PARTITION klienci_north VALUES ( ' SZCZECIN ' , ' KOSZALIN ' , ' GDANSK ' , ' GDYNIA ' , ' OLSZTYN ') , PARTITION klienci_south VALUES ( ' KATOWICE ' , ' KRAKOW ' , ' RZESZOW ' , ' KIELCE ')) TABLESPACE TB_REF PARALLEL ; alter table klienci_ref add constraint klient_ref_pk primary key ( id_klienta ) using index global TABLESPACE TB_IND_REF ; CREATE TABLE zamowienia_ref ( id_zamowienia VARCHAR2 (25) NOT NULL , data_zam DATE NOT NULL , kwota NUMBER (6 ,2) NOT NULL , id_klienta NUMBER (15) NOT NULL , region AS ( case when substr ( id_zamowienia ,1 ,1) = 'N ' then ' NORTH ' when substr ( id_zamowienia ,1 ,1) = 'E ' then ' EAST ' when substr ( id_zamowienia ,1 ,1) = 'S ' then ' SOUTH ' when substr ( id_zamowienia ,1 ,1) = 'W ' then ' WEST ' when substr ( id_zamowienia ,1 ,1) = 'C ' then ' CENTER ' end ) , CONSTRAINT klient_zam_ref_fk FOREIGN KEY ( id_klienta ) REFERENCES klienci_ref ( id_klienta )) PARTITION BY REFERENCE ( klient_zam_ref_fk ) TABLESPACE TB_REF PARALLEL ; alter table zamowienia_ref add constraint zam_ref_pk primary key ( id_zamowienia ) using index global TABLESPACE TB_IND_REF ;
Listing 17: Polecenie tworz¡ce partycjonowane lokalnie indeksy tabeli zamowie-
nia_ref.
CREATE BITMAP INDEX zam_ref_bitmap ON zamowienia_ref ( region ) TABLESPACE TB_IND_REF LOCAL PARALLEL NOLOGGING ; CREATE INDEX zam_ref_fk ON zamowienia_ref ( id_klienta ) LOCAL TABLESPACE TB_IND_REF PARALLEL NOLOGGING ;
35
Listing 18: Rozkªad danych mi¦dzy partycje tabel klienci_ref (60 mln wierszy) i
zamowienia_ref (120 mln wierszy)
SQL > SELECT
partition_name , num_rows ,
high_value FROM dba_tab_partitions WHERE table_name = ' KLIENCI_REF '; PARTITION_NAME
NUM_ROWS
HIGH_VALUE
--------------- ---------- ------------- -------------------- ------------------KLIENCI_CENTER
11428570
' LODZ ' , ' POZNAN ' , ' BYDGOSZCZ ' , ' TORUN '
KLIENCI_EAST
11428572
' WARSZAWA ' , ' BIALYSTOK ' , ' LUBLIN ' , ' RADOM '
KLIENCI_NORTH
14285714
' SZCZECIN ' , ' KOSZALIN ' , ' GDANSK ' , ' GDYNIA ' , ' OLSZTYN '
KLIENCI_SOUTH
11428572
' KATOWICE ' , ' KRAKOW ' , ' RZESZOW ' , ' KIELCE '
KLIENCI_WEST
11428572
' WROCLAW ' , ' OPOLE ' , ' LEGNICA ' , ' ZIELONA GORA '
SQL > SELECT
partition_name , num_rows FROM dba_tab_partitions
WHERE table_name = ' ZAMOWIENIA_REF '; PARTITION_NAME
NUM_ROWS
-------------------------- ---------KLIENCI_CENTER
22860093
KLIENCI_EAST
22854570
KLIENCI_NORTH
28564512
KLIENCI_SOUTH
22863576
KLIENCI_WEST
22857249
Listing 19: Indeksy lokalne tabeli zamowienia_ref
SQL > SELECT partition_name , num_rows FROM dba_ind_partitions WHERE index_name = ' ZAM_REF_FK '; PARTITION_NAME
NUM_ROWS
------------------------------ ---------KLIENCI_CENTER
23432401
KLIENCI_EAST
23755607
KLIENCI_NORTH
29502361
KLIENCI_SOUTH
23328675
KLIENCI_WEST
22857756
SQL > SELECT partition_name , num_rows FROM dba_ind_partitions WHERE index_name = ' ZAM_REF_BITMAP '; PARTITION_NAME
NUM_ROWS
------------------------------ ---------KLIENCI_CENTER
4482
36
KLIENCI_EAST
4481
KLIENCI_NORTH
5598
KLIENCI_SOUTH
4482
KLIENCI_WEST
4480
3.3.2
Partycjonowanie mieszaj¡ce (orem2)
Tabele KLIENCI_HASH oraz ZAMOWIENIA_HASH zostaªy poddane partycjonowaniu mieszaj¡cemu wedªug kolumny id_klienta, co potencjalnie mo»e by¢ opªacalne przy zapytaniach bazuj¡cych na ich zª¡czeniach. Ka»da z tabel zostaªa podzielona na 8 partycji. Obie tabele wraz z indeksami znajduj¡ si¦ w bazie danych dbtest2 na maszynie orem2. Tabele te byªy testowane w nast¦puj¡cych kon guracjach indeksowych:
•
tylko z niepartycjonowanymi indeksami na kluczach gªównych;
•
z niepartycjonowanymi indeksami na kluczach gªównych oraz dla tabeli ZA-
MOWIENIA_HASH partycjonowanymi lokalnie indeksami bitmapowym na kolumnie region oraz drzewiastym na kluczu obcym id_klienta ;
•
z niepartycjonowanymi indeksami na kluczach gªównych oraz dla tabeli ZA-
MOWIENIA_HASH partycjonowanym lokalnie indeksem bitmapowym na kolumnie region oraz drzewiastym globalnym niepartycjonowanym utworzonym na kluczu obcym id_klienta ;
•
z niepartycjonowanymi indeksami na kluczach gªównych oraz dla tabeli ZA-
MOWIENIA_HASH partycjonowanym lokalnie indeksem bitmapowym na kolumnie region oraz pre ksowanym globalnym drzewiastym partycjonowanym zakresowo na kluczu obcym id_klienta.
Listing 20: Polecenie tworz¡ce tabel¦ klienci_hash oraz zamowienia_hash partycjonowane metod¡ mieszaj¡c¡
CREATE TABLE klienci_hash ( id_klienta NUMBER (15) NOT NULL , nazwisko VARCHAR2 (25) NOT NULL , imie VARCHAR2 (15) NOT NULL , miasto VARCHAR2 (20) NOT NULL , kod NUMBER (6) NOT NULL ,
37
adres VARCHAR2 (30) NOT NULL ) PARTITION BY HASH ( id_klienta ) PARTITIONS 8 TABLESPACE TB_K_HASH ; alter table klienci_hash add constraint klient_hash_pk primary key ( id_klienta ) using index global TABLESPACE IND_KLIENCI ; CREATE TABLE zamowienia_hash ( id_zamowienia VARCHAR2 (25) NOT NULL , data_zam DATE NOT NULL , kwota NUMBER (6 ,2) NOT NULL , id_klienta NUMBER (15) NOT NULL , region AS ( case when substr ( id_zamowienia ,1 ,1) = 'N ' then ' NORTH ' when substr ( id_zamowienia ,1 ,1) = 'E ' then ' EAST ' when substr ( id_zamowienia ,1 ,1) = 'S ' then ' SOUTH ' when substr ( id_zamowienia ,1 ,1) = 'W ' then ' WEST ' when substr ( id_zamowienia ,1 ,1) = 'C ' then ' CENTER ' end ) , CONSTRAINT klient_zam_hash_fk FOREIGN KEY ( id_klienta ) REFERENCES klienci_hash ( id_klienta )) PARTITION BY HASH ( id_klienta ) PARTITIONS 8 TABLESPACE TB_K_HASH ; alter table zamowienia_hash add constraint zam_hash_pk primary key ( id_zamowienia ) using index global TABLESPACE IND_ZAM ; Listing 21: Polecenia tworz¡ce partycjonowane lokalnie indeksy tabeli zamowie-
nia_hash.
CREATE INDEX zam_hash_fk ON zamowienia_hash ( id_klienta ) LOCAL TABLESPACE IND_ZAM PARALLEL NOLOGGING ; CREATE BITMAP INDEX zam_hash_bitmap ON zamowienia_hash ( region ) LOCAL PARALLEL TABLESPACE IND_ZAM NOLOGGING ; 38
Listing 22: Skrypt tworz¡cy globalny niepartycjonowany indeks tabeli zamowie-
nia_hash.
CREATE INDEX zam_hash_fk ON zamowienia_hash ( id_klienta ) GLOBAL TABLESPACE IND_ZAM NOLOGGING ;
Listing 23: Polecenie tworz¡ce globalny partycjonowany zakresowo indeks tabeli za-
mowienia_hash (gdy tabela klienci_hash zawieraªa 30mln wierszy).
CREATE INDEX zam_hash_fk ON zamowienia_hash ( id_klienta ) GLOBAL PARTITION BY RANGE ( id_klienta ) ( PARTITION p1 VALUES LESS THAN (3750000) , PARTITION p2 VALUES LESS THAN (7500000) , PARTITION p3 VALUES LESS THAN (11250000) , PARTITION p4 VALUES LESS THAN (15000000) , PARTITION p5 VALUES LESS THAN (18750000) , PARTITION p6 VALUES LESS THAN (22500000) , PARTITION p7 VALUES LESS THAN (26250000) , PARTITION p_greater VALUES LESS THAN ( maxvalue ) ) TABLESPACE IND_ZAM PARALLEL NOLOGGING ;
Listing 24: Rozkªad danych mi¦dzy partycje tabel klienci_hash i zamowienia_hash (po 60 mln wierszy)
SQL > SELECT
partition_name , num_rows FROM dba_tab_partitions
WHERE table_name = ' KLIENCI_HASH '; PARTITION_NAME
NUM_ROWS
-------------------------- ---------SYS_P41
7500299
SYS_P42
7498104
SYS_P43
7502604
SYS_P44
7495839
SYS_P45
7497233
SYS_P46
7499631
SYS_P47
7503399
SYS_P48
7502891
39
SQL > SELECT
partition_name , num_rows FROM dba_tab_partitions
WHERE table_name = ' ZAMOWIENIA_HASH '; PARTITION_NAME
NUM_ROWS
--------------------------- ---------SYS_P101
7504835
SYS_P102
7501024
SYS_P103
7500795
SYS_P104
7495734
SYS_P105
7499738
SYS_P106
7495923
SYS_P107
7503046
SYS_P108
7498905
Listing 25: Indeks lokalny ZAM_HASH_FK tabeli zamowienia_hash
SQL > SELECT partition_name , num_rows FROM dba_ind_partitions WHERE index_name = ' ZAM_HASH_FK '; PARTITION_NAME
NUM_ROWS
------------------------------ ---------SYS_P209
7218192
SYS_P210
7590410
SYS_P211
7380034
SYS_P212
7841330
SYS_P213
7401678
SYS_P214
7660292
SYS_P215
7533011
SYS_P216
7663615
Listing 26: Indeks globalny partycjonowany zakresowo tabeli zamowienia_hash (gdy
klienci_hash miaªa 30 mln wierszy)
SQL > SELECT partition_name , num_rows , high_value FROM dba_ind_partitions WHERE index_name = ' ZAM_HASH_FK '; PARTITION_NAME
NUM_ROWS HIGH_VALUE
-------------- --------
----------
P1
7427314
3750000
P2
7501872
7500000
P3
7475270
11250000
P4
7244029
15000000 40
P5
7533282
18750000
P6
7374100
22500000
P7
7509397
26250000
P_GREATER
7208202
MAXVALUE
3.3.3
Partycjonowanie na kolumnie wirtualnej (orem2)
Tabela ZAMOWIENIA_LIST zostaªa partycjonowana listowo. Kluczem partycjonowania jest kolumna wirtualna region. Tabela zostaªa podzielona na 5 partycji, wraz z indeksami nale»y do bazy danych dbtest2 na maszynie orem2. Tabela ZAMOWIENIA_LIST byªa testowana w nast¦puj¡cych kon guracjach indeksowych:
•
tylko z niepartycjonowanym indeksem na kluczu gªównym;
•
z niepartycjonowanymi globalnymi indeksami na kluczu gªównym oraz na kluczu obcym id_klienta ;
•
z niepartycjonowanym globalnym indeksem na kluczu gªównym oraz partycjonowanym lokalnie indeksem na kluczu obcym id_klienta ;
•
z niepartycjonowanym globalnym indeksem na kluczu gªównym oraz globalnym pre ksowanym partycjonowanym metod¡ mieszaj¡c¡ na kluczu obcym
id_klienta.
Listing 27: Polecenie tworz¡ce tabel¦ zamowienia_list partycjonowan¡ metod¡ listow¡.
CREATE TABLE zamowienia_list ( id_zamowienia VARCHAR2 (25) NOT NULL , data_zam DATE NOT NULL , kwota NUMBER (6 ,2) NOT NULL , id_klienta NUMBER (15) NOT NULL , region AS ( case when substr ( id_zamowienia ,1 ,1) = 'N ' then ' NORTH ' when substr ( id_zamowienia ,1 ,1) = 'E ' then ' EAST ' when substr ( id_zamowienia ,1 ,1) = 'S ' then ' SOUTH ' when substr ( id_zamowienia ,1 ,1) = 'W ' then ' WEST ' when substr ( id_zamowienia ,1 ,1) = 'C ' then ' CENTER ' end ) ,
41
CONSTRAINT klient_zam_list_fk FOREIGN KEY ( id_klienta ) REFERENCES klienci_hash ( id_klienta )) PARTITION BY LIST ( region ) ( PARTITION zam_west VALUES ( ' WEST ') , PARTITION zam_east VALUES ( ' EAST ') , PARTITION zam_central VALUES ( ' CENTER ') , PARTITION zam_north VALUES ( ' NORTH ') , PARTITION zam_south VALUES ( ' SOUTH ') ) TABLESPACE ZAM_TB PARALLEL ; alter table zamowienia_list add constraint zam_list_pk primary key ( id_zamowienia ) using index global TABLESPACE IND_ZAM ;
Listing 28: Polecenie tworz¡ce partycjonowany lokalnie indeks na kluczu obcym
id_klienta tabeli zamowienia_list.
CREATE INDEX zam_list_fk ON zamowienia_list ( id_klienta ) LOCAL TABLESPACE IND_ZAM PARALLEL NOLOGGING ;
Listing 29: Polecenie tworz¡ce globalny niepartycjonowany indeks tabeli zamowie-
nia_list.
CREATE INDEX zam_list_fk ON zamowienia_list ( id_klienta ) GLOBAL TABLESPACE IND_ZAM NOLOGGING ;
Listing 30: Rozkªad danych mi¦dzy partycje tabeli zamowienia_list (dla 120mln wierszy).
SQL > SELECT
partition_name , num_rows , high_value FROM dba_tab_partitions
WHERE table_name = ' ZAMOWIENIA_LIST '; PARTITION_NAME
NUM_ROWS
HIGH_VALUE
------------------- ---------- -----------------------------------------ZAM_CENTRAL
24000096
' CENTER '
ZAM_EAST
23993296
' EAST '
ZAM_NORTH
24002670
' NORTH '
ZAM_SOUTH
24006678
' SOUTH '
ZAM_WEST
23997260
' WEST '
42
Listing 31: Indeks lokalny zam_list_fk tabeli zamowienia_list.
SQL > SELECT
WHERE index_name = ' ZAM_LIST_FK ';
PARTITION_NAME
NUM_ROWS
------------------------------ ---------ZAM_CENTRAL
23317979
ZAM_EAST
23097547
ZAM_NORTH
24128378
ZAM_SOUTH
23782057
ZAM_WEST
23673529
Listing 32: Indeks globalny partycjonowany metod¡ mieszaj¡c¡ tabeli zamowie-
nia_list (gdy klienci_hash miaªa 60 mln wierszy).
SQL > SELECT partition_name , num_rows FROM dba_ind_partitions WHERE index_name = ' ZAM_LIST_FK '; PARTITION_NAME
NUM_ROWS
------------------------------ ---------SYS_P509
13949878
SYS_P510
15236847
SYS_P511
15071437
SYS_P512
14400264
SYS_P513
15419528
SYS_P514
15246985
SYS_P515
15635793
SYS_P516
15245726
3.3.4
Partycjonowanie interwaªowe (orem2)
Tabela ZAMOWIENIA_INTER zostaªa partycjonowana interwaªowo, wedªug kolumny data_zam (12 partycji, ka»da zawiera dane z innego miesi¡ca 2014 roku). Tabela wraz z indeksami nale»y do bazy danych dbtest2 na maszynie orem2. Tabela ZAMOWIENIA_INTER byªa testowana w nast¦puj¡cych kon guracjach indeksowych:
•
tylko z niepartycjonowanym indeksem na kluczu gªównym;
•
z niepartycjonowanymi globalnymi indeksami na kluczu gªównym oraz na kluczu obcym id_klienta oraz lokalnie partycjonowanym bitmapowym indeksem na kolumnie region ;
43
•
z niepartycjonowanym globalnym indeksem na kluczu gªównym oraz partycjonowanym lokalnie indeksem na kluczu obcym id_klienta oraz równie» lokalnie partycjonowanym bitmapowym indeksem na kolumnie region ;
•
z niepartycjonowanym globalnym indeksem na kluczu gªównym oraz globalnym pre ksowanym partycjonowanym metod¡ mieszaj¡c¡ na kluczu obcym
id_klienta i lokalnie partycjonowanym bitmapowym indeksem na kolumnie region.
Listing 33: Skrypt tworz¡cy tabel¦ zamowienia_inter partycjonowan¡ metod¡ interwaªow¡.
CREATE TABLE zamowienia_inter ( id_zamowienia VARCHAR2 (25) NOT NULL , data_zam DATE NOT NULL , kwota NUMBER (6 ,2) NOT NULL , id_klienta NUMBER (15) NOT NULL , region AS ( case when substr ( id_zamowienia ,1 ,1) = 'N ' then ' NORTH ' when substr ( id_zamowienia ,1 ,1) = 'E ' then ' EAST ' when substr ( id_zamowienia ,1 ,1) = 'S ' then ' SOUTH ' when substr ( id_zamowienia ,1 ,1) = 'W ' then ' WEST ' when substr ( id_zamowienia ,1 ,1) = 'C ' then ' CENTER ' end ) , CONSTRAINT klient_zam_inter_fk FOREIGN KEY ( id_klienta ) REFERENCES klienci_hash ( id_klienta )) PARTITION BY RANGE ( data_zam ) INTERVAL ( NUMTOYMINTERVAL (1 , ' MONTH ')) ( PARTITION sty2014 VALUES LESS THAN ( TO_DATE ( '1 -02 -2014 ' , 'DD - MM - YYYY ')) ) TABLESPACE ZAM_TB PARALLEL ; alter table zamowienia_inter add constraint zam_inter_pk primary key ( id_zamowienia ) using index global TABLESPACE IND_ZAM ;
Listing 34: Polecenie tworz¡ce partycjonowane lokalnie indeksy tabeli zamowie-
nia_inter.
44
CREATE INDEX zam_inter_fk ON zamowienia_inter ( id_klienta ) LOCAL TABLESPACE IND_ZAM PARALLEL NOLOGGING ; CREATE BITMAP INDEX zam_inter_bitmap ON zamowienia_inter ( region ) LOCAL PARALLEL TABLESPACE IND_ZAM NOLOGGING ;
Listing 35: Polecenie tworz¡ce globalny niepartycjonowany indeks tabeli zamowie-
nia_inter.
CREATE INDEX zam_inter_fk ON zamowienia_inter ( id_klienta ) GLOBAL TABLESPACE IND_ZAM NOLOGGING ;
Listing 36: Skrypt tworz¡cy globalny partycjonowany metod¡ mieszaj¡c¡ indeks tabeli zamowienia_inter.
CREATE INDEX zam_inter_fk on zamowienia_inter ( id_klienta ) GLOBAL PARTITION BY HASH ( id_klienta ) PARTITIONS 8 TABLESPACE IND_ZAM PARALLEL NOLOGGING ;
Listing 37: Rozkªad danych mi¦dzy partycje tabeli zamowienia_inter (dla 60mln wierszy).
SQL > SELECT
partition_name , num_rows , high_value FROM dba_tab_partitions
WHERE table_name = ' ZAMOWIENIA_INTER '; PARTITION_NAME
NUM_ROWS HIGH_VALUE
--------------- ------- -- -- --- -- --- --- -- --- --- -- --- --- -- --- --- -- --- -- --- --- -- STY2014
5080479 TO_DATE ( ' 2014 -02 -01 00:00:00 ' , ' SYYYY - MM - DD HH24 : MI : SS
SYS_P189
4589110 TO_DATE ( ' 2014 -03 -01 00:00:00 ' , ' SYYYY - MM - DD HH24 : MI : SS
SYS_P190
5080357 TO_DATE ( ' 2014 -08 -01 00:00:00 ' , ' SYYYY - MM - DD HH24 : MI : SS
SYS_P191
5081236 TO_DATE ( ' 2014 -09 -01 00:00:00 ' , ' SYYYY - MM - DD HH24 : MI : SS
SYS_P192
5083158 TO_DATE ( ' 2014 -11 -01 00:00:00 ' , ' SYYYY - MM - DD HH24 : MI : SS
SYS_P193
5079152 TO_DATE ( ' 2014 -06 -01 00:00:00 ' , ' SYYYY - MM - DD HH24 : MI : SS
SYS_P194
4923316 TO_DATE ( ' 2014 -07 -01 00:00:00 ' , ' SYYYY - MM - DD HH24 : MI : SS
SYS_P195
5082742 TO_DATE ( ' 2015 -01 -01 00:00:00 ' , ' SYYYY - MM - DD HH24 : MI : SS
SYS_P196
4921914 TO_DATE ( ' 2014 -05 -01 00:00:00 ' , ' SYYYY - MM - DD HH24 : MI : SS
SYS_P197
4916954 TO_DATE ( ' 2014 -12 -01 00:00:00 ' , ' SYYYY - MM - DD HH24 : MI : SS 45
SYS_P198
4916023 TO_DATE ( ' 2014 -10 -01 00:00:00 ' , ' SYYYY - MM - DD HH24 : MI : SS
SYS_P199
5081875 TO_DATE ( ' 2014 -04 -01 00:00:00 ' , ' SYYYY - MM - DD HH24 : MI : SS
Listing 38: Indeksy lokalne tabeli zamowienia_inter (dla 120 mln wierszy).
SQL > SELECT partition_name , num_rows FROM dba_ind_partitions WHERE index_name = ' ZAM_INTER_FK '; PARTITION_NAME
NUM_ROWS
------------------------------ ---------STY2014
10192195
SYS_P581
9431365
SYS_P582
10023925
SYS_P583
9807155
SYS_P584
10250801
SYS_P585
9971679
SYS_P586
9993482
SYS_P587
10214292
SYS_P588
10156418
SYS_P589
10255595
SYS_P590
10118726
SYS_P591
10122842
SQL > SELECT partition_name , num_rows FROM dba_ind_partitions WHERE index_name = ' ZAM_INTER_BITMAP '; PARTITION_NAME
NUM_ROWS
---------------- -------STY2014
2422
SYS_P481
1900
SYS_P482
1999
SYS_P483
1965
SYS_P484
1997
SYS_P485
1966
SYS_P486
1997
SYS_P487
1999
SYS_P488
1965
SYS_P489
2000
SYS_P490
1965
SYS_P491
2000
46
Listing 39: Indeks globalny partycjonowany metod¡ mieszaj¡c¡ tabeli zamowie-
nia_inter (gdy klienci_hash miaªa 60 mln wierszy).
SQL > SELECT partition_name , num_rows FROM dba_ind_partitions WHERE index_name = ' ZAM_INTER_FK '; PARTITION_NAME
NUM_ROWS
------------------------------ ---------SYS_P501
15554913
SYS_P502
15470661
SYS_P503
14984925
SYS_P504
14132895
SYS_P505
15250336
SYS_P506
15295063
SYS_P507
15048292
SYS_P508
15061026
3.3.5
Partycjonowanie dwupoziomowe interwaªowo-listowe (orem2)
Tabela ZAM_INTER_LIST zostaªa partycjonowana dwupoziomowo, interwaªowo na podstawie kolumny data_zam (12 partycji) i nast¦pnie listowo wedªug kolumny wirtualnej region (po 5 subpartycji na ka»d¡ partycj¦ tabeli). Tabela wraz z indeksami nale»y do bazy danych dbtest2 na maszynie orem2. Tabela ZAM_INTER_LIST byªa testowana w nast¦puj¡cych kon guracjach indeksowych:
•
tylko z niepartycjonowanym indeksem na kluczu gªównym;
•
z niepartycjonowanymi globalnymi indeksami na kluczu gªównym oraz na kluczu obcym id_klienta ;
•
z niepartycjonowanym globalnym indeksem na kluczu gªównym oraz partycjonowanym dwupoziomowym lokalnym indeksem na kluczu obcym id_klienta ;
•
z niepartycjonowanym globalnym indeksem na kluczu gªównym oraz globalnym pre ksowanym partycjonowanym metod¡ zakresow¡ na kluczu obcym
id_klienta.
47
Listing 40: Skrypt tworz¡cy tabel¦ zam_inter_list partycjonowan¡ dwupoziomowo interwaªowo-listow¡.
CREATE TABLE zam_inter_list ( id_zamowienia VARCHAR2 (25) NOT NULL , data_zam DATE NOT NULL , kwota NUMBER (6 ,2) NOT NULL , id_klienta NUMBER (15) NOT NULL , region AS ( case when substr ( id_zamowienia ,1 ,1) = 'N ' then ' NORTH ' when substr ( id_zamowienia ,1 ,1) = 'E ' then ' EAST ' when substr ( id_zamowienia ,1 ,1) = 'S ' then ' SOUTH ' when substr ( id_zamowienia ,1 ,1) = 'W ' then ' WEST ' when substr ( id_zamowienia ,1 ,1) = 'C ' then ' CENTER ' end ) , CONSTRAINT klient_zam_inter_list_fk FOREIGN KEY ( id_klienta ) REFERENCES klienci_hash ( id_klienta )) PARTITION BY RANGE ( data_zam ) INTERVAL ( NUMTOYMINTERVAL (1 , ' MONTH ')) SUBPARTITION BY LIST ( region ) SUBPARTITION TEMPLATE ( SUBPARTITION sp_N VALUES ( ' NORTH ') , SUBPARTITION sp_E VALUES ( ' EAST ') , SUBPARTITION sp_S VALUES ( ' SOUTH ') , SUBPARTITION sp_W VALUES ( ' WEST ') , SUBPARTITION sp_C VALUES ( ' CENTER ')) ( PARTITION p1_data VALUES LESS THAN ( TO_DATE ( '1 -02 -2014 ' , 'DD - MM - YYYY ')) ) TABLESPACE ZAM_TB PARALLEL ; alter table zam_inter_list add constraint zam_inter_list_pk primary key ( id_zamowienia ) using index global TABLESPACE IND_ZAM ;
Listing 41: Polecenie tworz¡ce partycjonowany lokalnie indeks na kluczu obcym
id_klienta tabeli zam_inter_list.
CREATE INDEX zam_inter_list_fk ON zamowienia_inter_list ( id_klienta ) LOCAL TABLESPACE IND_ZAM PARALLEL NOLOGGING ; 48
Listing
42:
Polecenie
tworz¡ce
globalny
niepartycjonowany
indeks
tabeli
zam_inter_list.
CREATE INDEX zam_inter_list_fk ON zam_inter_list_fk ( id_klienta ) GLOBAL TABLESPACE IND_ZAM NOLOGGING ;
Listing 43: Skrypt tworz¡cy globalny partycjonowany zakresowo indeks tabeli
zam_inter_list (gdy tabela klienci_hash zawieraªa 30mln wierszy).
CREATE INDEX zam_inter_list_fk ON zam_inter_list ( id_klienta ) GLOBAL PARTITION BY RANGE ( id_klienta ) ( PARTITION p1 VALUES LESS THAN (3750000) , PARTITION p2 VALUES LESS THAN (7500000) , PARTITION p3 VALUES LESS THAN (11250000) , PARTITION p4 VALUES LESS THAN (15000000) , PARTITION p5 VALUES LESS THAN (18750000) , PARTITION p6 VALUES LESS THAN (22500000) , PARTITION p7 VALUES LESS THAN (26250000) , PARTITION p_greater VALUES LESS THAN ( maxvalue )) TABLESPACE IND_ZAM PARALLEL NOLOGGING ;
Listing 44: Rozkªad danych mi¦dzy partycje tabeli zam_inter_list z uwzgl¦dnieniem ilo±ci subpartycji przypadaj¡cych na ka»d¡ z partycji (dla 60 mln wierszy).
SQL > SELECT partition_name , num_rows , subpartition_count , high_value FROM dba_tab_partitions WHERE table_name = ' ZAM_INTER_LIST '; PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE -------------- -------- ------------------ ----------------------------P1_DATA
5080479
5 TO_DATE ( ' 2014 -02 -01 00:00:00 '
SYS_P382
4589110
5 TO_DATE ( ' 2014 -03 -01 00:00:00 '
SYS_P388
5081875
5 TO_DATE ( ' 2014 -04 -01 00:00:00 '
SYS_P394
4921914
5 TO_DATE ( ' 2014 -05 -01 00:00:00 '
SYS_P400
5079152
5 TO_DATE ( ' 2014 -06 -01 00:00:00 '
SYS_P406
4923316
5 TO_DATE ( ' 2014 -07 -01 00:00:00 '
SYS_P412
5080357
5 TO_DATE ( ' 2014 -08 -01 00:00:00 '
SYS_P418
5081236
5 TO_DATE ( ' 2014 -09 -01 00:00:00 '
SYS_P424
4916023
5 TO_DATE ( ' 2014 -10 -01 00:00:00 '
SYS_P430
5083158
5 TO_DATE ( ' 2014 -11 -01 00:00:00 '
SYS_P436
4916954
5 TO_DATE ( ' 2014 -12 -01 00:00:00 '
SYS_P442
5082742
5 TO_DATE ( ' 2015 -01 -01 00:00:00 '
49
Listing 45: Indeks lokalny tabeli zam_inter_list partycjonowany dwupoziomowo.
SQL > SELECT partition_name , num_rows , composite FROM dba_ind_partitions WHERE index_name = ' ZAM_INTER_LIST_FK '; PARTITION_NAME
NUM_ROWS
COM
--------------- ---------- --P1_DATA
5065874
YES
SYS_P461
4521945
YES
SYS_P462
4984444
YES
SYS_P463
4991925
YES
SYS_P464
5097581
YES
SYS_P465
4632332
YES
SYS_P466
5031892
YES
SYS_P467
5090625
YES
SYS_P468
5076736
YES
SYS_P469
5336190
YES
SYS_P470
5144916
YES
SYS_P471
5034303
YES
Listing 46: Indeks globalny partycjonowany zakresowo tabeli zam_inter_list.
SQL > SELECT partition_name , num_rows , composite FROM dba_ind_partitions WHERE index_name = ' ZAM_INTER_LIST_FK '; PARTITION_NAME
NUM_ROWS
COM
--------------- ---------- --P1
7375520
NO
P2
7702527
NO
P3
7007122
NO
P4
7880180
NO
P5
7489088
NO
P6
7367646
NO
P7
7631917
NO
P_GREATER
7542944
NO
3.3.6
Partycjonowanie dwupoziomowe zakresowo-mieszaj¡ce (orem2)
Tabela ZAM_RANGE_HASH zostaªa partycjonowana dwupoziomowo, zakresowo na podstawie kolumny data_zam (12 partycji)) i nast¦pnie metod¡ mieszaj¡c¡ na kluczu obcym id_klienta (po 8 subpartycji na ka»d¡ partycj¦). Tabela wraz z indeksami nale»y do bazy danych dbtest2 na maszynie orem2.
50
Tabela ZAMOWIENIA_INTER byªa testowana w dwóch kon guracjach indeksowych:
•
tylko z niepartycjonowanym indeksem na kluczu gªównym;
•
z niepartycjonowanym globalnym indeksem na kluczu gªównym oraz dwupoziomowymi partycjonowanymi lokalnie indeksami na kluczu obcym id_klienta oraz bitmapowym na kolumnie region ;
Listing 47: Skrypt tworz¡cy tabel¦ zam_range_hash partycjonowan¡ dwupoziomowo zakresowo-mieszaj¡co.
CREATE TABLE zam_range_hash ( id_zamowienia VARCHAR2 (25) NOT NULL , data_zam DATE NOT NULL , kwota NUMBER (6 ,2) NOT NULL , id_klienta NUMBER (15) NOT NULL , region AS ( case when substr ( id_zamowienia ,1 ,1) = 'N ' then ' NORTH ' when substr ( id_zamowienia ,1 ,1) = 'E ' then ' EAST ' when substr ( id_zamowienia ,1 ,1) = 'S ' then ' SOUTH ' when substr ( id_zamowienia ,1 ,1) = 'W ' then ' WEST ' when substr ( id_zamowienia ,1 ,1) = 'C ' then ' CENTER ' end ) , CONSTRAINT klient_zam_range_hash_fk FOREIGN KEY ( id_klienta ) REFERENCES klienci_hash ( id_klienta )) PARTITION BY RANGE ( data_zam ) SUBPARTITION BY HASH ( id_klienta ) SUBPARTITIONS 8 ( PARTITION zam_sty14 VALUES LESS THAN ( TO_DATE ( '1 -02 -2014 ' , 'DD - MM - YYYY ')) , PARTITION zam_lut14 VALUES LESS THAN ( TO_DATE ( '1 -03 -2014 ' , 'DD - MM - YYYY ')) , PARTITION zam_marz14 VALUES LESS THAN ( TO_DATE ( '1 -04 -2014 ' , 'DD - MM - YYYY ')) , PARTITION zam_kwie14 VALUES LESS THAN ( TO_DATE ( '1 -05 -2014 ' , 'DD - MM - YYYY ')) , PARTITION zam_maj14 VALUES LESS THAN ( TO_DATE ( '1 -06 -2014 ' , 'DD - MM - YYYY ')) , PARTITION zam_czer14 VALUES LESS THAN ( TO_DATE ( '1 -07 -2014 ' , 'DD - MM - YYYY ')) , PARTITION zam_lip14 VALUES LESS THAN ( TO_DATE ( '1 -08 -2014 ' , 'DD - MM - YYYY ')) , PARTITION zam_sier14 VALUES LESS THAN ( TO_DATE ( '1 -09 -2014 ' , 'DD - MM - YYYY ')) , PARTITION zam_wrze14 VALUES LESS THAN ( TO_DATE ( '1 -10 -2014 ' , 'DD - MM - YYYY ')) , PARTITION zam_paz14 VALUES LESS THAN ( TO_DATE ( '1 -11 -2014 ' , 'DD - MM - YYYY ')) , PARTITION zam_lis14 VALUES LESS THAN ( TO_DATE ( '1 -12 -2014 ' , 'DD - MM - YYYY ')) , 51
PARTITION zam_gru14 VALUES LESS THAN ( TO_DATE ( '1 -01 -2015 ' , 'DD - MM - YYYY '))) TABLESPACE ZAM_TB PARALLEL ; alter table zam_range_hash add constraint \ textit { zam_range_hash_pk } primary key ( id_zamowienia ) using index global TABLESPACE IND_ZAM ;
Listing
48:
Polecenia
tworz¡ce
partycjonowane
lokalnie
indeksy
tabeli
zam_range_hash.
CREATE INDEX zam_range_hash_fk ON zam_range_hash ( id_klienta ) LOCAL TABLESPACE IND_ZAM NOLOGGING ; CREATE BITMAP INDEX zam_range_hash_bitmap ON zam_range_hash ( region ) LOCAL PARALLEL TABLESPACE IND_ZAM NOLOGGING ;
Listing 49: Rozkªad danych mi¦dzy partycje tabeli zam_range_hash z uwzgl¦dnieniem ilo±ci subpartycji przypadaj¡cych na ka»d¡ z partycji (dla 60 mln wierszy).
SQL > SELECT partition_name , num_rows , subpartition_count , high_value FROM dba_tab_partitions WHERE table_name = ' ZAM_RANGE_HASH '; PARTITION_NAME NUM_ROWS
SUBPARTITION_COUNT HIGH_VALUE
-------------- ---------- ------------------ --------------------------ZAM_STY14
5080479
8 TO_DATE ( ' 2014 -02 -01 00:00:00 '
ZAM_LUT14
4589110
8 TO_DATE ( ' 2014 -03 -01 00:00:00 '
ZAM_MARZ14
5081875
8 TO_DATE ( ' 2014 -04 -01 00:00:00 '
ZAM_KWIE14
4921914
8 TO_DATE ( ' 2014 -05 -01 00:00:00 '
ZAM_MAJ14
5079152
8 TO_DATE ( ' 2014 -06 -01 00:00:00 '
ZAM_CZER14
4923316
8 TO_DATE ( ' 2014 -07 -01 00:00:00 '
ZAM_LIP14
5080357
8 TO_DATE ( ' 2014 -08 -01 00:00:00 '
ZAM_SIER14
5081236
8 TO_DATE ( ' 2014 -09 -01 00:00:00 '
ZAM_WRZE14
4916023
8 TO_DATE ( ' 2014 -10 -01 00:00:00 '
ZAM_PAZ14
5083158
8 TO_DATE ( ' 2014 -11 -01 00:00:00 '
ZAM_LIS14
4916954
8 TO_DATE ( ' 2014 -12 -01 00:00:00 '
ZAM_GRU14
5082742
8 TO_DATE ( ' 2015 -01 -01 00:00:00 '
52
Listing 50: Indeks lokalny tabeli zam_range_hash partycjonowany dwupoziomowo.
SQL > SELECT partition_name , num_rows , composite FROM dba_ind_partitions WHERE index_name = ' ZAM_RANGE_HASH_FK '; PARTITION_NAME
NUM_ROWS
COM
--------------- ---------- --ZAM_STY14
5466078
YES
ZAM_LUT14
4678640
YES
ZAM_MARZ14
5042471
YES
ZAM_KWIE14
4797830
YES
ZAM_MAJ14
5153882
YES
ZAM_CZER14
4884805
YES
ZAM_LIP14
5180683
YES
ZAM_SIER14
4970592
YES
ZAM_WRZE14
4866715
YES
ZAM_PAZ14
5056396
YES
ZAM_LIS14
4855964
YES
ZAM_GRU14
5009948
YES
3.4 Generacja danych testowych Na potrzeby generacji danych testowych powstaªy dwa skrypty zam_c.sql i
klienci_c.sql napisne w j¦zyku PL/SQL. Pierwszy z nich dotyczy tabeli zamowienia lub jej pochodnych tabel partycjonowanych - zamowienia_hash, zamowienia_ref,
zamowienia_inter, zamowienia_list, zam_range_hash, zam_inter_list, drugi za± tabeli klienci lub jej pochodnych tabel partycjonowanych - klienci_hash, klienci_ref. Sposób generowania wierszy oraz nast¦pnego ich wstawiania do tabeli jest identyczny bez znaczenia na to czy tabela jest partycjonowana i w jaki sposób. Ka»dy ze skryptów skªada si¦ z dwóch logicznych cz¦±ci: pseudolosowego generowania warto±ci poszczególnych pól rekordu oraz nast¦puj¡cego po tym wstawieniu go do wybranej tabeli. Te dziaªania s¡ przeprowadzane w p¦tli, która wykonuje si¦ tyle razy ile chcemy wstawi¢ wierszy do tabeli. Dane generowane s¡ pseudolosowo przy u»yciu funkcji pakietu DBMS_RANDOM, który przy ka»dym wywoªaniu skryptu byª inicjowany t¡ sam¡ warto±ci¡ ziarna:
dbms_random . initialize (681457802);
53
To gwarantuje, »e skrypt przy ka»dym wywoªaniu wstawi takie same rekordy danych do tabeli (zakªadaj¡c brak ingerencji w parametry). Korzystaªam z tej wªa±ciwo±ci, aby móc wypeªnia¢ kolejne partycjonowane tabele w dogodnym momencie, a nie by¢ zmuszon¡ wstawia¢ do nich danych w tym samym momencie.
3.4.1
Generacja danych tabeli klienci
Tabele klienci, klienci_ref oraz klienci_hash byªy wypeªniane danymi w trzech turach. W pierwszej wstawiono do ka»dej z tabel 30 mln rekordów - id_klienta od 1 do 30000000 (licznik p¦tli FOR, linia nr 10), a w drugiej wstawiono drugie tyle wierszy - id_klienta od 30000001 do 60000000. Nast¦pnie zdecydowano si¦ przeprowadzi¢ testy równie» na po±redniej wielko±ci tabel - 45 mln rekordów. Oznacza to, »e tabele te byªy testowane w trzech rozmiarach przy 30, 45 i 60 mln wierszy. Pole id_klienta byªo w ka»dym obiegu p¦tli FOR ustawiana jako aktualna warto±¢ jej licznika. Aby zapewni¢, »e wstawiane do tabeli dane ka»dego z klientów b¦d¡ zawieraªy kolumn¦ miasto z warto±ci¡ nale»¡c¡ do listy 21 nazw miast, na podstawie której potem byªo dokonywane partycjonowanie listowe na tabeli klienci_ref, stworzono pomocnicz¡ tabel¦ miasta. W skrypcie na podstawie warto±ci kolumny
miasta.id i aktualnej warto±ci licznika p¦tli FOR dokonywano przyporz¡dkowania miasta do klienta (linia nr 12).
Listing 51: Polecenie tworz¡ce pomocnicz¡ tabel¦ miasta.
create table miasta ( id NUMBER (2) , nazwa VARCHAR2 (20) , constraint miasta_PK primary key ( id )); Kolumny nazwisko, imie, kod i adres byªy generowane bez dodatkowych zale»no±ci, poniewa» ich warto±ci nie miaªy znaczenia w odniesieniu do zastosowanych metod partycjonowania czy zapyta«, które byªy wykonywane na tabelach w czasie testów.
54
Listing 52: Skrypt klienci_c.sql generuj¡cy i wstawiaj¡cy 30 mln rekordów danych do tabeli klienci. 1
DECLARE
2
naz VARCHAR2 (25);
3
im
4
mias VARCHAR2 (20);
5
k NUMBER (6);
6
ad VARCHAR2 (30);
7
n NUMBER (2);
8
VARCHAR2 (15);
BEGIN dbms_random . initialize (681457802);
9 10
FOR i IN 1..30000000
11
LOOP
12
n := MOD (i , 21) + 1;
13
SELECT m . nazwa INTO mias FROM miasta m WHERE m . id = n ;
14
naz := dbms_random . string ( 'L ' , 25);
15
im := dbms_random . string ( 'L ' , 15);
16
k := TRUNC ( dbms_random . value (10000 , 99999));
17
ad := ( dbms_random . string ( 'L ' , 20)|| ' ' || TRUNC ( dbms_random . value (1 , 200)));
18
INSERT INTO klienci
19
( id_klienta , nazwisko , imie , miasto , kod , adres )
20
VALUES
21
(i , naz , im , mias , k , ad );
22
END LOOP ;
23
dbms_random . terminate ;
24
COMMIT ;
25
END ;
26
/
-- wybor miasta
55
3.4.2
Generacja danych tabeli zamownienia
Tabele zamownienia, zamowienia_ref, zamowienia_hash, zamowienia_list, za-
mowienia_inter, zamowienia_inter_list, zamowienia_range_hash byªy wypeªniane danymi rówenie» w dwóch turach. W pierwszej wstawiono do ka»dej z tabel 60 mln rekordów - id_zamowienia od 1 do 60000000 (licznik p¦tli FOR, linia nr 10), a w drugiej wstawiono drugie tyle wierszy - id_zamowienia od 60000001 do 120000000. Nast¦pnie zdecydowano si¦ przeprowadzi¢ testy równie» na po±redniej wielko±ci tabel - 90 mln rekordów. Oznacza to, »e tabele te byªy testowane w trzech rozmiarach przy 60, 90 i 120 mln wierszy. Pole id_zamowienia (na podstawie którego jest tworzona kolumna wirtualna
zamowienia.region ) skªada si¦ z dwóch cz¦±ci oddzielonych znakiem '_'. Pierwsza cz¦±¢ jest jedn¡ z 5 mo»liwych liter 'CNSWE', a druga to liczba ustawiana na podstawie aktualnej warto±ci licznika p¦tli FOR (linia nr 12). Data zamowienia - kolumna zamowienia.data_zam przyjmuje pseudolosow¡ warto±¢ z zakresu roku 2014 (linia nr 14). Jest ona kluczem partycjonowania dla tabel: zamowienia_inter,
zamowienia_inter_list, zamowienia_range_hash. Warto±¢ klucza obcego zamowienia.id_klienta jest pseudolosowo przyporz¡dkowywana na podstawie wiedzy ile wierszy danych zawiera odpowiednio tabela klienci, klienci_hash lub klienci_ref (linia nr 16). Pole zamowienia.kwota jest generowane bez dodatkowych zale»no±ci, poniewa» jego warto±¢ nie miaªa znaczenia w odniesieniu do zastosowanych metod partycjonowania czy zapyta«, które byªy wykonywane na tabelach w czasie testów. Listing 53: Skrypt zam_c.sql generuj¡cy i wstawiaj¡cy 60 mln rekordów danych do tabeli zamowienia. 1
DECLARE
2
id_zam VARCHAR2 (25);
3
kw NUMBER (6 ,2);
4
d DATE ;
5
id_kl NUMBER (15);
6
n NUMBER (15);
7
-- liczba wierszy tabeli klienci
BEGIN
8
dbms_random . initialize (681457802);
9
SELECT COUNT (*) INTO n FROM klienci ;
10
FOR i IN 1..60000000
11
LOOP 56
12
id_zam :=( SUBSTR ( ' CNSWE ' , TRUNC ( dbms_random . value (1 ,6)) ,1)|| '_ ' || TO_CHAR ( i ));
13
-- losowa data z roku 2014
14
d := TO_DATE ( ' 01 -01 -2014 ' , 'dd - mm - yyyy ') + TRUNC ( DBMS_RANDOM . value (0 ,366));
15
kw := round ( dbms_random . value (5.01 , 9999.98) ,2);
16
id_kl := TRUNC ( dbms_random . value (1 , n +1)); -- losowy klient
17
INSERT INTO zamowienia
18
( id_zamowienia , data_zam , kwota , id_klienta )
19
VALUES
20
( id_zam , d , kw , id_kl );
21
END LOOP ;
22
dbms_random . terminate ;
23
COMMIT ;
24
END ;
25
/
3.5 Aplikacja testowa Aplikacja testowa zostaªa napisana w j¦zyku Java przy u»yciu sterowników JDBC Oracle przeznaczonych do bazy Oracle 11gR2 (archiwum ojdbc6.jar) umo»liwiaj¡cych nawi¡zanie zdalnego poª¡czenia z bazami danych z maszyn orem1 lub orem2. Po nawi¡zaniu poª¡czenia z wybran¡ z baz danych aplikacja wysyªa do niej kolejno zapytania SQL typu SELECT i mierzy czas odpowiedzi na rezultaty ka»dego z nich. Dokªadniejsze informacje zostaªy zawarte w dwóch nast¦pnych podrozdziaªach. Kod ¹ródªowy aplikacji jest przedstawiony w Zaª¡czniku A - kod ¹ródªowy
aplikacji testowej.
3.5.1
Budowa i dziaªanie aplikacji
Aplikacja oparta jest na klasie Polaczenie, która zawiera trzy metody:
•
public void polaczenie_z_baza() - jej gªówn¡ funkcjonalno±ci¡ jest zaªadowanie odpowiednich sterowników oraz nawi¡zanie poª¡czenia z wybran¡ baz¡ danych (linie 35- 34). Nast¦pnie ustawiane s¡ dodatkowe parametry dotycz¡ce wykonywania zapyta«. Pierwszy z nich sprawia (linia nr 62), »e lista wysyªanych zapyta« b¦dzie traktowana jako jedna transakcja, zatwierdzanie nast¦puje po zako«czonym sukcesem wykonaniu zapyta« testowych. Dwie kolejne (linie 64,
57
65) wywoªuj¡ polecenia DDL maj¡ce na celu wyczyszczenie buforów bazy danych ALTER SYSTEM FLUSH BUFFER_CACHE oraz obszaru dzielonego
ALTER SYSTEM FLUSH SHARED_POOL. Uznaªam, »e usuni¦cie wyników oraz planów wykona« poprzednich zapyta« jest rozs¡dne w przypadku powtarzania testów skªadaj¡cych si¦ z tych samych zapyta«, bo pomoga w zachowaniu ich niezale»no±ci. Chciaªam uzyska¢ jak najbardziej warto±ciowe wyniki bada«. Nast¦pnie dla ka»dego z zapyta« testowych wywoªywana jest metoda
test. Na koniec zwalniane s¡ wszystkie zasoby oraz zamykane jest poª¡czenie z baz¡ danych. W przypadku przechwycenia wyj¡tku w konsoli wy±wietlana jest tre±¢ bª¦du. Ponadto w przypadku bª¦dów dotycz¡cych wykonania polece« SQL dokonywane jest zwolnienie zaj¦tych zasobów, przywracany jest stan bazy danych sprzed rozpocz¦cia transakcji (polecenie ROLLBACK ) oraz zamykane jest poª¡czenie.
•
private void test(Statement stm, String query, PrintWriter zapis)
- metoda
wywoªywana dla ka»dego z zapyta« testowych. Wykonuje zapytanie, które dostaje w argumencie String query i mierzy okres czasu (w milisekundach) jaki upªyn¡ª od momentu wysªania zapytania do bazy danych i otrzymania jego wyników. Listing 54: Sposób mierzenia czasu wykonania zapytania, fragment aplikacji testowej.
long start = System . currentTimeMillis (); ResultSet res = stm . executeQuery ( query ); long time = System . currentTimeMillis () - start ; Tre±¢ zapytania oraz tak obliczany czas jego wykonania s¡ nast¦pnie wypisywane na konsol¦ oraz zapisywane do pliku dane.txt.
•
public static void main(String[] args) - tworzy nowy obiekt klasy Polaczenie i wywoluje jego metod¦ polaczenie_z_baza().
3.5.2
Zapytania testowe
W testach korzystam z 7 zapyta«. Ka»de z nich zostaªo tak skonstruowane, aby umo»liwiaªo skorzystnie z ewentualnych dodatkowych mechanizmów dotycz¡cych partycjonowania (przycinanie partycji, zª¡czenia partycji), które w teorii maj¡ skró-
58
ci¢ czas jego wykonywania w stosunku do tabeli niepartycjonowanej. Z tego wzgl¦du klauzule WHERE zawieraj¡ warunki selekcji oparte na kluczach partycjonowania. Pod Listingiem 48 dokonane jest obja±nienie jak przedstawione w nim zapytania byªy wykorzystywane w testach. Listing 55: Zapytania testowe na przykªadzie niepartycjonowanych tabel zamowienia oraz klienci (fragment kodu aplikacji testowej)
String query1 = " SELECT COUNT (*) FROM klienci WHERE miasto = ' KATOWICE ' " ; String query2 = " SELECT COUNT (*) FROM zamowienia WHERE region = ' WEST ' " ; String query3 = " SELECT SUM ( kwota ) FROM zamowienia " + " WHERE data_zam BETWEEN ( TO_DATE ( '01 -06 -2014 ' , 'DD - MM - YYYY ')) " + " AND ( TO_DATE ( '01 -07 -2014 ' , 'DD - MM - YYYY ')) " ; String query4 = " SELECT sum ( kwota ) FROM zamowienia " + " WHERE id_klienta = 123456 AND " + " z . data_zam BETWEEN ( TO_DATE ( '01 -02 -2014 ' , 'DD - MM - YYYY ')) " + " AND ( TO_DATE ( '01 -03 -2014 ' , 'DD - MM - YYYY ')) " ; String query5 = " SELECT AVG ( kwota ) FROM zamowienia
" +
" WHERE data_zam BETWEEN ( TO_DATE ( '01 -02 -2014 ' , 'DD - MM - YYYY ')) " + " AND ( TO_DATE ( '01 -05 -2014 ' , 'DD - MM - YYYY ')) AND region = ' CENTER ' " ; String query6 = " SELECT k . nazwisko , sum ( z . kwota ) wartosc_zamowien " + " FROM zamowienia z , klienci k
WHERE z . id_klienta = k . id_klienta AND " +
" z . data_zam BETWEEN ( TO_DATE ( '01 -06 -2014 ' , 'DD - MM - YYYY ')) " + " AND ( TO_DATE ( '01 -07 -2014 ' , 'DD - MM - YYYY ')) GROUP BY k . nazwisko " ; String query7 = " COUNT (*) liczba_zamowien " + " FROM zamowienia z , klienci k WHERE z . id_klienta = k . id_klienta AND " + " k . miasto IN ( ' WARSZAWA ' , ' BIALYSTOK ' , ' LUBLIN ' , ' RADOM ') " ;
•
query1 - zapytanie zwraca liczb¦ klientów mieszkaj¡cych w Katowicach. Byªo wykonanywane na tabeli klienci oraz jej pochodnych tabelach partycjonowanych - klienci_ref i klienci_hash ;
•
query2 - zapytanie zwraca liczb¦ zamówie« zªo»onych w regionie WEST . Byªo wykonywane na tabeli zamowienia oraz na jej pochodnych tabelach partycjonowanych - zamowienia_ref, zamowienia_hash, zamowienia_inter, za-
mowienia_list, zam_inter_list i zam_range_hash ;
•
query3 - zapytanie zwraca sum¦ warto±ci zamówie« z okresu jednego miesi¡ca. Byªo wykonywane na tabeli zamowienia oraz na jej pochodnych tabelach par-
59
tycjonowanych - zamowienia_ref, zamowienia_hash, zamowienia_inter, za-
mowienia_list, zam_inter_list i zam_range_hash ;
•
query4 - zapytanie zwraca sum¦ warto±ci zamówie« dla wybranego klienta z okresu jednego miesi¡ca. Byªo wykonywane na tabeli zamowienia oraz na jej pochodnych tabelach partycjonowanych - zamowienia_ref, zamowienia_hash,
zamowienia_inter, zamowienia_list, zam_inter_list i zam_range_hash ;
•
query5 - zapytanie zwraca ±redni¡ sum¦ warto±ci zamowie« z okresu trzech miesi¦cy w regionie CENTER .
•
query6, query7 - zapytania wymagaj¡ce zª¡cze« mi¦dzy tabelami typu zamowienia oraz klienci. Nast¦puj¡ce kon guracje zª¡cze« zostaªy uwzgl¦dnione w testach:
tabela klienci byªa zª¡czana z tabel¡ zamowienia,
tabela klienci_ref byªa zª¡czana z tabel¡ zamowienia_ref,
tabela klienci_hash byªa zª¡czana z pozostaªymi tabelami partycjonowanymi czyli z zamowienia_hash, zamowienia_inter, zamowienia_list,
zam_inter_list i zam_range_hash. Zapytanie query6 zwraca sum¦ warto±ci zamowie« dla ka»dego z klientów z okresu jednego miesi¡ca, za± query7 podaje liczb¦ zamówie« zªo»onych przez klientów mieszkaj¡cych w Warszawie, Biaªymstoku, Lublinie lub Radomiu.
60
4
Testy Tak jak ju» zostaªo wspomniane w poprzednim rozdziale, testy byªy przepro-
wadzane na trzch rozmiarach danych. Dla tabel typu klienci byªo to 30, 45 i 60 milionów wierszy, za± tabele typu zamowienia byªy testowane dla 60, 90 i 120 milionów wierszy. Zª¡czenia mi¦dzy tabelami odbywaªy si¦ w trzech kon guracjach rozmiarowych: mi¦dzy tabelami typu klienci o 30 mln rekordów i zamowienia o 60 mln, dla tabel typu klienci o 45 mln rekordów i zamowienia o 90 mln oraz trzecia dla tabel typu klienci o 60 mln rekordów i zamowienia o 120 mln rekordów. Ka»dy z pomiarów czasu (w milisekunach) wykonania ka»dego z zapyta« dla danej tabeli o wybranej kon guracji indeksowej byª przeprowadzany trzykrotnie. Rozrzut mi¦dzy warto±ciami otrzymanych wyników pomiarów wynosiª ok. 30%. Aby zachowa¢ przejrzysto±¢ w pracy znajduj¡ si¦ u±rednione warto±ci tych trzech wyników (tabele w nast¦pnych podrozdziaªach). Przed wykonaniem testów dla nowej kon guracji partycji tabeli i indeksów, gromadzone byªy statystyki. Po wykonaniu skryptu zam_c.sql lub kllienci_c.sql w celu zwi¦kszenia rozmiarów tabel ich indeksy byªy przebudowywane. Listing 56: Przykªad zbierania statystyk dla tabeli zamowienia_ref oraz jej indeksów.
exec dbms_stats . gather_table_stats ( ' SYS ' , ' ZAMOWIENIA_REF ' , GRANULARITY = > ' ALL ' ); exec dbms_stats . gather_index_stats ( ' SYS ' , ' ZAM_REF_FK ' , GRANULARITY = > ' ALL ' ); exec dbms_stats . gather_index_stats ( ' SYS ' , ' ZAM_REF_BITMAT , GRANULARITY = > ' ALL ' ); exec dbms_stats . gather_index_stats ( ' SYS ' , ' ZAM_REF_PK ' ); W nast¦pnych podrozdziaªach zostan¡ przedstawione wyniki bada« efektywno±ci poszczególnych metod partycjonowania tabel oraz indeksów w odniesieniu do przygotowanej listy zapyta« testowych.
61
4.1 Tabele klienci,
klienci_ref
oraz
klienci_hash
Wyniki testów (wykonanych przez aplikacj¦ testow¡) na niepartycjonowanej tabeli klienci, partycjonowanej listowo wg kolumny miasto tabeli klienci_ref
oraz
partycjonowanej metod¡ mieszaj¡c¡ opart¡ na kluczu gªównym id_klienta tabeli
klienci_hash zostaªy przedstawione w Tablicy 2 oraz Tablicy 3. Dokªadny opis tabel zostaª zamieszczony we wcze±niejszej cz¦±ci pracy, w rozdziale 3.3 Relacyjny model
danych - dla tabeli klienci, podrozdziale 3.3.1 Partycjonowanie referencyjne - dla tabeli klienci_ref oraz 3.3.2 Partycjonowanie mieszaj¡ce - dla tabeli klienci_hash. Mierzono czas wykonania nast¦puj¡cego zapytania (odpowiednio dla ka»dej z tabel):
SELECT COUNT (*) FROM klienci WHERE miasto = ' KATOWICE '; SELECT COUNT (*) FROM klienci_ref WHERE miasto = ' KATOWICE '; SELECT COUNT (*) FROM klienci_hash WHERE miasto = ' KATOWICE '; Zapytanie zwraca liczb¦ klientów mieszkaj¡cych w Katowicach.
Tablica 2: Czas wykonania zapytania testowego (w ms) dla tabel klienci, klienci_ref oraz klienci_hash dla dwóch rozmiarów tabel (w mln wierszy)
rozmiar tabel
tabela
tabela
tabela
klienci [ms]
klienci_ref [ms]
klienci_hash [ms]
30
33630
19354
28239
45
163000
29547
153625
60
229880
38235
176422
Wyniki testów s¡ zgodne dla wszystkich rozmiarów tabel. Najdªu»ej zapytanie byªo wykonywane na niepartycjonowanej tabeli klienci, najkrócej za± na tabeli
klienci_ref. Przyczyn¡ takich rezultatów jest fakt, i» klauzula WHERE zapytania zawiera warunek równo±ci oparty na kluczu partycjonowania tabeli klienci_ref
i
zostaªo przeprowadzone przycinanie partycji (dost¦p byª jedynie do parycji nr 5). W przypadku tabeli klienci_hash pozytywny wpªyw na czas wykonania mogªa mie¢ mo»liwo±¢ równolegªego skanowania partycji.
62
Rysunek 12: Plan wykonania zapytania dla tabeli klienci_ref
o 30 mln wierszy,
potwierdzaj¡cy u»ycie mechanizmu przycinania partycji.
Rysunek 13: Plan wykonania zapytania dla tabeli klienci_hash o 30 mln wierszy, obrazuj¡cy przeprowadzenie zapytania równolegªego.
63
4.2 Tabela zamowienia Wyniki testów (wykonanych przez aplikacj¦ testow¡) na niepartycjonowanej tabeli zamowienia zostaªy przedstawione w Tablicy 3, Tablicy 4 oraz Tablicy 5. Kolumny tych tablic wskazuj¡ sposób indeksowania tabeli zamowienia w danym przypadku testowym. Kolejno nazwy tych kolumn oznaczaj¡:
•
bez indeksów - zaªo»ony jest tylko niepartycjonowany indeks na klyczu gªównym id_zamowienia ;
•
niepartycjonowane - zaªo»one s¡ niepartycjonowane indeksy na kluczu gªównym, kluczu obcym id_klienta oraz bitmapowy na kolumnie region ;
•
globalny - zaªo»one s¡ niepartycjonowane indeksy na kluczu gªównym i na kolumnie region, indeks na kluczu obcym jest partycjonowany globalnie metod¡ zakresow¡.
Dokªadny opis tabeli zamowienia zostaª zamieszczony we wcze±niejszej cz¦±ci pracy, w rozdziale 3.3 Relacyjny model danych. Mierzono czas wykonania nast¦puj¡cych zapyta«:
1. Zapytanie zwraca liczb ¦ zam ó wie « z ª o » onych w regionie " WEST " . SELECT COUNT (*) FROM zam_inter_list WHERE region = ' WEST '; 2. Zapytanie zwraca sum ¦ warto ± ci zam ó wie « z okresu jednego miesi ¡ ca . SELECT SUM ( kwota ) FROM zamowienia WHERE data_zam BETWEEN ( TO_DATE ( ' 01 -06 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -07 -2014 ' , 'DD - MM - YYYY ' )); 3. Zapytanie zwraca sum ¦ warto ± ci zam ó wie « dla wybranego klienta z okresu jednego miesi ¡ ca . SELECT sum ( kwota ) FROM zamowienia WHERE id_klienta = 123456 AND z . data_zam BETWEEN ( TO_DATE ( ' 01 -02 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -03 -2014 ' , 'DD - MM - YYYY ' )); 4. Zapytanie zwraca ± redni ¡ sum ¦ warto ± ci zamowie « z okresu trzech miesi ¦ cy w regionie " CENTER " . SELECT AVG ( kwota ) FROM zamowienia WHERE data_zam BETWEEN ( TO_DATE ( ' 01 -02 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -05 -2014 ' , 'DD - MM - YYYY ' )) AND region = ' CENTER '; 5. Zapytanie wymagaj ¡ ce z ª ¡ czenia tabeli zamowienia oraz klienci , zwraca sum ¦ warto ± ci zamowie « dla ka » dego z klient ó w z okresu jednego miesi ¡ ca . 64
SELECT k . nazwisko , sum ( z . kwota ) wartosc_zamowien FROM zamowienia z , klienci k WHERE z . id_klienta = k . id_klienta AND z . data_zam BETWEEN ( TO_DATE ( ' 01 -06 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -07 -2014 ' , 'DD - MM - YYYY ' )) GROUP BY k . nazwisko ; 6. Zapytanie wymagaj ¡ ce z ª ¡ czenia tabeli zamowienia oraz klienci , podaje liczb ¦ zam ó wie « z ª o » onych przez klient ó w mieszkaj ¡ cych w Warszawie , Bia ª ymstoku , Lublinie lub Radomiu . SELECT COUNT (*) liczba_zamowien FROM zamowienia z , klienci k WHERE z . id_klienta = k . id_klienta AND k . miasto IN ( ' WARSZAWA ' , ' BIALYSTOK ' , ' LUBLIN ' , ' RADOM ' );
Tablica 3: Czas wykonania zapyta« testowych (w ms) dla tabeli zamowienia o 60 mln wierszy i ro»nych kon guracjach indeksowych.
nr zapytania
bez indeksów [ms]
niepartycjonowane [ms] globalny [ms]
1
77890
2209
2568
2
2078
34189
3568
3
2101
16
5
4
2223
2235
2245
5
2045
2076
2105
6
179703
116709
187568
Tablica 4: Czas wykonania zapyta« testowych (w ms) dla tabeli zamowienia o 90 mln wierszy i ro»nych kon guracjach indeksowych.
nr zapytania
bez indeksów [ms]
niepartycjonowane [ms] globalny [ms]
1
111453
3569
2157
2
172734
91484
78828
3
33391
38
101
4
113453
56406
54906
5
4943
42094
54954
6
242563
181656
288125
65
Tablica 5: Czas wykonania zapyta« testowych (w ms) dla tabeli zamowienia o 120 mln wierszy i ro»nych kon guracjach indeksowych.
nr zapytania
bez indeksów [ms]
niepartycjonowane [ms] globalny [ms]
1
123912
1791
2073
2
213198
165721
160812
3
41897
6
73
4
12152
98025
169508
5
5484
48022
78666
6
494288
312926
534343
Wyniki przeprowadzonych testów wskazuj¡ no to, »e zastosowanie indeksu globalnie partycjonowanego na kluczu obcym id_klienta tabeli zamowienia przyniosªo korzy±ci dla rozmiarów tabeli 90 i 120 mln dla zapytania nr 2, chocia» nie zostaª przy jego wykonaniu wykarzystany »aden z mechanizmów dotycz¡cych partycjonowania poprawiaj¡cych efektywno±¢. Dla rozmiaru tabeli równego 30 mln wierszy i zapytania nr 3, osi¡gni¦to wynik 3 razy lepszy ni» w przypadku zastosowania indeksów niepartycjonowanych. Zostaª osi¡gni¦ty lepszy wynik dla partycjonowanego indeksu, gdy» miaªo miejsce statyczne przycinanie partycji. Przeszukiwana jest tylko jedna z 8 partycji indeksu, ta w której znajduj¡ si¦ dane klienta o id_klienta równym 12345. Zapytanie nr 1 zostaªo szybciej wykonane przy u»yciu indeksu partycjonowanego przy rozmiarze tabeli równym 90 mln. Indeks partycjonowany nie sprawdziª si¦ jednak w odniesieniu do ostatnich trzech zapyta«. Warto zwróci¢ uwag¦ na fakt, i» w niektórych przypadkach globalny indeks partycjonowany spowodowaª wydªu»enie czasu oczekiwania na rezultaty zapytania nawet w porównaniu z tabel¡ bez indeksu na kluczu obcym. Zaobserwowa¢ mo»na równie» ciekawy przypadek dla tabeli zamo-
wienia z niepartycjonowanymi indeksami, zapytanie nr 3 wykonane zostaªo szybciej dla wi¦kszego rozmiaru tabeli.
66
Rysunek 14: Plan wykonania zapytania nr 6 dla tabeli zamowienia o 60 mln wierszy, potwierdzaj¡cy u»ycie mechanizmu zrównoleglenia i wykorzystania indeksu globalnie partycjonowanego na kluczu obcym.
Rysunek 15: Plan wykonania zapytania nr 3 dla tabeli zamowienia o 30 mln wierszy, potwierdzaj¡cy u»ycie mechanizmu przycinania partycji. Przeszukiwana byªa tylko pierwsza partycja indeksu globalnie partycjonowanego na kluczu obcym id_klienta.
67
4.3 Tabela zamowienia_ref Wyniki testów (wykonanych przez aplikacj¦ testow¡) na tabeli zamowienia_ref partcjonowanej referencyjnie wg wzoru tabeli klienci_ref zostaªy przedstawione w Tablicy 6, Tablicy 7 oraz Tablicy 8. Kolumny tych tablic wskazuj¡ sposób indeksowania tabeli zamowienia_ref w danym przypadku testowym. Kolejno nazwy tych kolumn oznaczaj¡:
•
bez indeksów - tylko globalny niepartycjonowany indeks na kluczu gªównym
id_zamowienia ;
•
z lokalnymi - z globalnym niepartycjonowanym indeksem na kluczu gªównym, z partycjonowanymi lokalnie indeksami bitmapowym na kolumnie region oraz drzewiastym na kluczu obcym id_klienta ;
Dokªadny opis tabeli zamowienia_ref zostaª zamieszczony we wcze±niejszej cz¦±ci pracy, w podrozdziale 3.3.1 Partycjonowanie referencyjne. Mierzono czas wykonania nast¦puj¡cych zapyta«:
1. Zapytanie zwraca liczb ¦ zam ó wie « z ª o » onych w regionie " WEST " . SELECT COUNT (*) FROM zamowienia_ref WHERE region = ' WEST '; 2. Zapytanie zwraca sum ¦ warto ± ci zam ó wie « z okresu jednego miesi ¡ ca . SELECT SUM ( kwota ) FROM zamowienia_ref WHERE data_zam BETWEEN ( TO_DATE ( ' 01 -06 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -07 -2014 ' , 'DD - MM - YYYY ' )); 3. Zapytanie zwraca sum ¦ warto ± ci zam ó wie « dla wybranego klienta z okresu jednego miesi ¡ ca . SELECT sum ( kwota ) FROM zamowienia_ref WHERE id_klienta = 123456 AND z . data_zam BETWEEN ( TO_DATE ( ' 01 -02 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -03 -2014 ' , 'DD - MM - YYYY ' )); 4. Zapytanie zwraca ± redni ¡ sum ¦ warto ± ci zamowie « z okresu trzech miesi ¦ cy w regionie " CENTER " . SELECT AVG ( kwota ) FROM zamowienia_ref WHERE data_zam BETWEEN ( TO_DATE ( ' 01 -02 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -05 -2014 ' , 'DD - MM - YYYY ' )) AND region = ' CENTER '; 5. Zapytanie wymagaj ¡ ce z ª ¡ czenia tabeli zamowienia_ref oraz klienci_ref , zwraca sum ¦ warto ± ci zamowie « dla ka » dego z klient ó w z okresu jednego miesi ¡ ca . SELECT k . nazwisko , sum ( z . kwota ) wartosc_zamowien 68
FROM zamowienia_ref z , klienci_ref k WHERE z . id_klienta = k . id_klienta AND z . data_zam BETWEEN ( TO_DATE ( ' 01 -06 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -07 -2014 ' , 'DD - MM - YYYY ' )) GROUP BY k . nazwisko ; 6. Zapytanie wymagaj ¡ ce z ª ¡ czenia tabeli zamowienia_ref oraz klienci_ref , podaje liczb ¦ zam ó wie « z ª o » onych przez klient ó w mieszkaj ¡ cych w Warszawie , Bia ª ymstoku , Lublinie lub Radomiu . SELECT COUNT (*) liczba_zamowien FROM zamowienia_ref z , klienci_ref k WHERE z . id_klienta = k . id_klienta AND k . miasto IN ( ' WARSZAWA ' , ' BIALYSTOK ' , ' LUBLIN ' , ' RADOM ' );
Tablica 6: Czas wykonania zapyta« testowych (w ms) dla tabeli zamowienia_ref o 60 mln wierszy i dwóch kon guracjach indeksowych.
nr zapytania
bez indeksów [ms]
z lokalnymi [ms]
1
12370
249
2
1452
9969
3
344
78
4
1527
1654
5
16562
8328
6
58677
63708
Tablica 7: Czas wykonania zapyta« testowych (w ms) dla tabeli zamowienia_ref o 90 mln wierszy dwóch kon guracjach indeksowych.
nr zapytania
bez indeksów [ms]
z lokalnymi [ms]
1
10657
1005
2
51765
47683
3
876
156
4
3563
3891
5
51975
49611
6
96583
78239
69
Tablica 8: Czas wykonania zapyta« testowych (w ms) dla tabeli zamowienia_ref o 120 mln wierszy dwóch kon guracjach indeksowych.
nr zapytania
bez indeksów [ms]
z lokalnymi [ms]
1
108395
1552
2
69221
65143
3
1180
250
4
48226
49811
5
70315
72133
6
195967
135730
W przypadku zapytania nr 1 dla wszystkich rozmiarów tabela zamowienia_ref z lokalnymi indeksami otrzymaªa lepsze wyniki ni» niepartycjonowana tabela za-
mowienia. Dla rozmiarów tabel równych 90 i 120 mln wierszy oraz zapytania nr 2 partycjonowanie referencyjnie tabeli zamowienia równie» okazaªo si¦ przynosi¢ lepsze rezultaty. Za± dla rozmiarów równych 60 i 90 mln wierszy szybciej wykonaªo si¦ w obu kon guracjach indeksowych zapytanie nr 4. Najlepszy jednak wynik, bo nawet trzykrotne przy±pieszenie wykonania nast¡piªo dla obu rozmiarów i modeli indeksów w odniesiu do ostatniego zapytania. Mamy tu doczynienia z równolegªym wykonaniu zª¡cze« na poziomie partycji oraz przycinaniem partycji, poniewa» tabele
zamowienia_ref i klienci_ref s¡ partycjonowane referencyjnie (zª¡czenia na poziomie partycji) listowo na kolumnie miasto, która znajduje si¦ w klauzuli WHERE przy predykacie IN warunku zawierania (przycinanie partycji). Wyst¦puje tu jednak tzw. dynamiczne przycinanie partycji, mniej efektywne ni» statyczne, ale ci¡gle korzystne. Wskazuje na to sªowo kluczowe KEY w kolumnach Pstart i Pstop w zaprezentowanym poni»ej planie wykonania.
70
Rysunek 16: Plan wykonania zapytania nr 6 dla tabeli zamowienia_ref o 60 mln wierszy, wskazuj¡cy na równolegle wykonywane zª¡cze« na poziomie partycji oraz dynamiczne przycinanie partycji.
4.4 Tabela zamowienia_hash Wyniki testów (wykonanych przez aplikacj¦ testow¡) na tabeli zamowienia_hash partycjonowanej metod¡ mieszaj¡c¡ na podstawie klucza obcego id_klienta zostaªy przedstawione w Tablicy 9, Tablicy 10 oraz Tablicy 11. Kolumny tych tablic wskazuj¡ sposób indeksowania tabeli zamowienia_hash w danym przypadku testowym. Kolejno nazwy tych kolumn oznaczaj¡:
•
bez indeksów - tylko globalny niepartycjonowany indeks na kluczu gªównym
id_zamowienia ;
•
globalny niepartycjonowany - z globalnym niepartycjonowanymi indeksami na kluczu gªównym, obcym id_klienta oraz indeksem bitmapowym na kolumnie
region ;
•
globalny partycjonowany - z niepartycjonowanymi indeksem na kluczu gªównym, partycjonowanym lokalnie indeksem bitmapowym na kolumnie region oraz globalnym drzewiastym partycjonowanym zakresowo na kluczu obcym
id_klienta.
•
z lokalnymi - z globalnym niepartycjonowanym indeksem na kluczu gªównym, z partycjonowanymi lokalnie indeksami bitmapowym na kolumnie region oraz
71
drzewiastym na kluczu obcym id_klienta ;
Dokªadny opis tabeli zamowienia_hash zostaª zamieszczony we wcze±niejszej cz¦±ci pracy, w podrozdziale 3.3.2 Partycjonowanie mieszaj¡ce. Mierzono czas wykonania nast¦puj¡cych zapyta«:
1. Zapytanie zwraca liczb ¦ zam ó wie « z ª o » onych w regionie " WEST " . SELECT COUNT (*) FROM zamowienia_hash WHERE region = ' WEST '; 2. Zapytanie zwraca sum ¦ warto ± ci zam ó wie « z okresu jednego miesi ¡ ca . SELECT SUM ( kwota ) FROM zamowienia_hash WHERE data_zam BETWEEN ( TO_DATE ( ' 01 -06 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -07 -2014 ' , 'DD - MM - YYYY ' )); 3. Zapytanie zwraca sum ¦ warto ± ci zam ó wie « dla wybranego klienta z okresu jednego miesi ¡ ca . SELECT sum ( kwota ) FROM zamowienia_hash WHERE id_klienta = 123456 AND z . data_zam BETWEEN ( TO_DATE ( ' 01 -02 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -03 -2014 ' , 'DD - MM - YYYY ' )); 4. Zapytanie zwraca ± redni ¡ sum ¦ warto ± ci zamowie « z okresu trzech miesi ¦ cy w regionie " CENTER " . SELECT AVG ( kwota ) FROM zamowienia_hash WHERE data_zam BETWEEN ( TO_DATE ( ' 01 -02 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -05 -2014 ' , 'DD - MM - YYYY ' )) AND region = ' CENTER '; 5. Zapytanie wymagaj ¡ ce z ª ¡ czenia tabeli zamowienia_hash oraz klienci_hash , zwraca sum ¦ warto ± ci zamowie « dla ka » dego z klient ó w z okresu jednego miesi ¡ ca . SELECT k . nazwisko , sum ( z . kwota ) wartosc_zamowien FROM zamowienia_hash z , klienci_hash k WHERE z . id_klienta = k . id_klienta AND z . data_zam BETWEEN ( TO_DATE ( ' 01 -06 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -07 -2014 ' , 'DD - MM - YYYY ' )) GROUP BY k . nazwisko ; 6. Zapytanie wymagaj ¡ ce z ª ¡ czenia tabeli zamowienia_hash oraz klienci_hash , podaje liczb ¦ zam ó wie « z ª o » onych przez klient ó w mieszkaj ¡ cych w Warszawie , Bia ª ymstoku , Lublinie lub Radomiu . SELECT COUNT (*) liczba_zamowien FROM zamowienia_hash z , klienci_hash k WHERE z . id_klienta = k . id_klienta AND k . miasto IN ( ' WARSZAWA ' , ' BIALYSTOK ' , ' LUBLIN ' , ' RADOM ' );
72
Tablica 9: Czas wykonania zapyta« testowych (w ms) dla tabeli zamowienia_hash o 60 mln wierszy i ro»nych kon guracjach indeksowych.
nr zapytania
bez
globalny
globalny
z
indeksów [ms]
niepartycjonowany
partycjonowany
[ms]
[ms]
[ms]
lokalnymi
1
29406
1890
4479
2063
2
2624
17359
28677
12005
3
329
302
2187
15
4
1729
3067
63228
1349
5
6260
3187
48781
8609
6
54849
49479
87052
87052
Tablica 10: Czas wykonania zapyta« testowych (w ms) dla tabeli zamowienia_hash o 90 mln wierszy i ro»nych kon guracjach indeksowych.
nr zapytania
bez
globalny
globalny
z
indeksów [ms]
niepartycjonowany
partycjonowany
[ms]
[ms]
[ms]
lokalnymi
1
32958
30984
31643
31090
2
18385
19851
15943
19371
3
10762
9041
8310
10137
4
3908
3593
3701
3656
5
168237
165490
170462
154031
6
178264
179026
177292
174841
73
Tablica 11: Czas wykonania zapyta« testowych (w ms) dla tabeli zamowienia_hash o 120 mln wierszy i ro»nych kon guracjach indeksowych.
nr zapytania
bez
globalny
globalny
z
indeksów [ms]
niepartycjonowany
partycjonowany
[ms]
[ms]
[ms]
lokalnymi
1
45344
46985
35953
46985
2
23656
24625
10594
24625
3
23047
21734
22609
21734
4
5437
4969
4641
4969
5
211829
209969
217234
209969
6
279078
288047
290297
288047
Przy 60 mln rekordów partycjonowanie mieszaj¡ce na bazie klucza obcego okazaªo si¦ opªacalne tylko w stosunku do zapytania nr 2 i 6, jednak nie w przypadku zastosowania globalnego indesu partycjonowanego na kluczu obcym. Zastosowanie tego sposobu partycjonowania indeksu sprawiªo, »e wynik byª znacznie gorszy. W przypadku tabeli zamowienia_hash w drugiej kon guracji indeksowej czyli z wszystkimi indeksami niepartycjonowanymi przy zapytaniu nr 6, otrzymano wynik 2 razy lepszy ni» dla tabeli niepartycjonowanej. Równie» zapytanie nr 2 wykonane zostaªo troch¦ szybciej w kon guracji z indeksami niepartycjonowanymi i lokalnymi na kluczu obcym oraz kolumnie region. Dla razmiarów 90 i 120 mln wierszy ju» 3 z 7 zapyta« (nr 2, 4, 6) zostaªo wykonanych szybciej ni» przy tabeli niepartycjonowanej. Zapytania te w ka»dej kon guracji indeksowej zostaªy wykonane w krótszym czasie ni» na tabeli zamowienia. W przypadku zapyta« nr 2 i 4 jest to znacz¡ca poprawa, bo uzyskano kilkukrotnie lepsze wyniki. Wybór sposobu partycjonowania indeksu jednak nie miaª du»ego wpªywu, poniewa» wyniki te dla ka»dej kon guracji s¡ zbli»one. Warty odnotowania jest fakt, »e w przypadku zª¡cze« mi¦dzy tabelami zamowie-
nia_hash i klienci_hash dochodzi do zostosowania mechanizmu wspomagaj¡cego zª¡cze« na poziomie partycji (obie tabele s¡ partycjonowane na kluczu zª¡czenia), które jeszcze dodatkowo mo»e by¢ wykonywane równolegle. Czym wi¦ksza tabela tym bardziej to wpªywa na zwi¦kszenie efektywno±ci przeprowadzania zª¡cze«.
74
Rysunek 17: Plan wykonania zapytania nr 6 dla tabeli zamowienia_hash o 60 mln wierszy, wskazuj¡cy na równolegle wykonywane zª¡cze« na poziomie partycji.
4.5 Tabela zamowienia_list Wyniki testów (wykonanych przez aplikacj¦ testow¡) na tabeli zamowienia_list partycjonowanej listow¡ na podstawie kolumny wirtualnej region zostaªy przedstawione w Tablicy 12, Tablicy 13 oraz Tablicy 14. Kolumny tych tablic wskazuj¡ sposób indeksowania tabeli zamowienia_list w danym przypadku testowym. Kolejno nazwy tych kolumn oznaczaj¡:
•
bez indeksów - tylko globalny niepartycjonowany indeks na kluczu gªównym
id_zamowienia ;
•
globalny niepartycjonowany - z globalnym niepartycjonowanymi indeksami na kluczu gªównym, obcym id_klienta.
•
globalny partycjonowany - z niepartycjonowanymi indeksem na kluczu gªównym, partycjonowanym oraz globalnym drzewiastym partycjonowanym zakresowo na kluczu obcym id_klienta.
•
z lokalnymi - z globalnym niepartycjonowanym indeksem na kluczu gªównym i z partycjonowanym lokalnie drzewiastym na kluczu obcym id_klienta ;
Dokªadny opis tabeli zamowienia_list zostaª zamieszczony we wcze±niejszej cz¦±ci pracy, w podrozdziale 3.3.3 Partycjonowanie na kolumnie wirtualnej.
75
Mierzono czas wykonania nast¦puj¡cych zapyta«:
1. Zapytanie zwraca liczb ¦ zam ó wie « z ª o » onych w regionie " WEST " . SELECT COUNT (*) FROM zamowienia_list WHERE region = ' WEST '; 2. Zapytanie zwraca sum ¦ warto ± ci zam ó wie « z okresu jednego miesi ¡ ca . SELECT SUM ( kwota ) FROM zamowienia_list WHERE data_zam BETWEEN ( TO_DATE ( ' 01 -06 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -07 -2014 ' , 'DD - MM - YYYY ' )); 3. Zapytanie zwraca sum ¦ warto ± ci zam ó wie « dla wybranego klienta z okresu jednego miesi ¡ ca . SELECT sum ( kwota ) FROM zamowienia_list WHERE id_klienta = 123456 AND z . data_zam BETWEEN ( TO_DATE ( ' 01 -02 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -03 -2014 ' , 'DD - MM - YYYY ' )); 4. Zapytanie zwraca ± redni ¡ sum ¦ warto ± ci zamowie « z okresu trzech miesi ¦ cy w regionie " CENTER " . SELECT AVG ( kwota ) FROM zamowienia_list WHERE data_zam BETWEEN ( TO_DATE ( ' 01 -02 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -05 -2014 ' , 'DD - MM - YYYY ' )) AND region = ' CENTER '; 5. Zapytanie wymagaj ¡ ce z ª ¡ czenia tabeli zamowienia_list oraz klienci_hash , zwraca sum ¦ warto ± ci zamowie « dla ka » dego z klient ó w z okresu jednego miesi ¡ ca . SELECT k . nazwisko , sum ( z . kwota ) wartosc_zamowien FROM zamowienia_list z , klienci_hash k WHERE z . id_klienta = k . id_klienta AND z . data_zam BETWEEN ( TO_DATE ( ' 01 -06 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -07 -2014 ' , 'DD - MM - YYYY ' )) GROUP BY k . nazwisko ; 6. Zapytanie wymagaj ¡ ce z ª ¡ czenia tabeli zamowienia_list oraz klienci_hash , podaje liczb ¦ zam ó wie « z ª o » onych przez klient ó w mieszkaj ¡ cych w Warszawie , Bia ª ymstoku , Lublinie lub Radomiu . SELECT COUNT (*) liczba_zamowien FROM zamowienia_list z , klienci_hash k WHERE z . id_klienta = k . id_klienta AND k . miasto IN ( ' WARSZAWA ' , ' BIALYSTOK ' , ' LUBLIN ' , ' RADOM ' );
76
Tablica 12: Czas wykonania zapyta« testowych (mierzony w ms) dla tabeli zamo-
wienia_list o 60 mln wierszy i ro»nych kon guracjach indeksowych.
nr zapytania
bez
globalny
globalny
z
indeksów [ms]
niepartycjonowany
partycjonowany
[ms]
[ms]
[ms]
lokalnymi
1
567
10614
17380
13036
2
1641
36562
54773
43609
3
1505
140
271
420
4
317
1099
1145
661
5
15755
13740
20203
2859
6
87901
42823
99296
72854
Tablica 13: Czas wykonania zapyta« testowych (mierzony w ms) dla tabeli zamo-
wienia_list o 90 mln wierszy i ro»nych kon guracjach indeksowych.
nr zapytania
bez
globalny
globalny
z
indeksów [ms]
niepartycjonowany
partycjonowany
[ms]
[ms]
[ms]
lokalnymi
1
15937
17802
17473
16005
2
52086
51737
58719
51843
3
16307
15534
14953
15096
4
4956
12845
12982
5190
5
49625
48153
85635
89375
6
16291
160362
144822
157362
77
Tablica 14: Czas wykonania zapyta« testowych (mierzony w ms) dla tabeli zamo-
wienia_list o 120 mln wierszy i ro»nych kon guracjach indeksowych.
nr zapytania
bez
globalny
globalny
z
indeksów [ms]
niepartycjonowany
partycjonowany
[ms]
[ms]
[ms]
lokalnymi
1
29145
32062
33412
31947
2
87807
85967
88654
84007
3
27573
19677
29327
37057
4
8599
20854
21345
10454
5
50978
131807
109148
160802
6
246542
286276
175771
213093
Zapytanie nr 1 dla rozmiaru 90 i 120 mln wierszy mimo, »e jest wykonywane dla tabeli zamowienia_list z u»yciem mechanizmu przycinania partycji, to mimo wszystko szybciej zwraca rezultaty dla tabeli zamowienia z indeksem bitmapowym na kolumnie region, która jest kluczem partycjonowania tabeli zamowienia_list. Jednak przy zapytaniu nr 4, które zawiera dwa warunki w klauzuli WHERE bardziej efektywne okazuje si¦ przycinanie partycji poª¡czone ze zrównolegleniem. Jest to szczególnie widoczne dla tabeli zamowienia_list z tylko indeksem na kluczu gªównym i w przypadku tego samego indeksu, ale w poª¡czeniu z lokalnym partycjonowanym na kluczu obcym. Zapytanie nr 6 w ka»dej kon guracji indeksowej zostaªo wykonane szybciej ni» w przypadku tabeli zamowienia niepartycjonowanej. Dla tego zapytania równie» zostaªo zastosowane zrówleglenie wykonania zapytania.
78
Rysunek 18: Plan wykonania zapytania nr 4 dla tabeli zamowienia_list o 60 mln wierszy, potwierdzaj¡cy u»ycie mechanizmu zrównoleglenia i przycinania partycji.
4.6 Tabela zamowienia_inter Wyniki testów (wykonanych przez aplikacj¦ testow¡) na tabeli zamowienia_inter partycjonowanej interwaªowo na kolumnie data_zam zostaªy przedstawione w Tablicy 15, Tablicy 16 oraz Tablicy 17. Kolumny tych tablic wskazuj¡ sposób indeksowania tabeli zamowienia_inter w danym przypadku testowym. Kolejno nazwy tych kolumn oznaczaj¡:
•
bez indeksów - tylko globalny niepartycjonowany indeks na kluczu gªównym
id_zamowienia ;
•
globalny niepartycjonowany - z globalnym niepartycjonowanymi indeksami na kluczu gªównym, obcym id_klienta oraz indeksem bitmapowym na kolumnie
region ;
•
globalny partycjonowany - z niepartycjonowanymi indeksem na kluczu gªównym, partycjonowanym lokalnie indeksem bitmapowym na kolumnie region oraz globalnym drzewiastym partycjonowanym metod¡ mieszaj¡c¡ na kluczu obcym id_klienta.
•
z lokalnymi - z globalnym niepartycjonowanym indeksem na kluczu gªównym, z partycjonowanymi lokalnie indeksami bitmapowym na kolumnie region oraz drzewiastym na kluczu obcym id_klienta ;
79
Dokªadny opis tabeli zamowienia_inter zostaª zamieszczony we wcze±niejszej cz¦±ci pracy, w podrozdziale 3.3.4 Partycjonowanie interwaªowe. Mierzono czas wykonania nast¦puj¡cych zapyta«:
1. Zapytanie zwraca liczb ¦ zam ó wie « z ª o » onych w regionie " WEST " . SELECT COUNT (*) FROM zamowienia_inter WHERE region = ' WEST '; 2. Zapytanie zwraca sum ¦ warto ± ci zam ó wie « z okresu jednego miesi ¡ ca . SELECT SUM ( kwota ) FROM zamowienia_inter WHERE data_zam BETWEEN ( TO_DATE ( ' 01 -06 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -07 -2014 ' , 'DD - MM - YYYY ' )); 3. Zapytanie zwraca sum ¦ warto ± ci zam ó wie « dla wybranego klienta z okresu jednego miesi ¡ ca . SELECT sum ( kwota ) FROM zamowienia_inter WHERE id_klienta = 123456 AND z . data_zam BETWEEN ( TO_DATE ( ' 01 -02 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -03 -2014 ' , 'DD - MM - YYYY ' )); 4. Zapytanie zwraca ± redni ¡ sum ¦ warto ± ci zamowie « z okresu trzech miesi ¦ cy w regionie " CENTER " . SELECT AVG ( kwota ) FROM zamowienia_inter WHERE data_zam BETWEEN ( TO_DATE ( ' 01 -02 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -05 -2014 ' , 'DD - MM - YYYY ' )) AND region = ' CENTER '; 5. Zapytanie wymagaj ¡ ce z ª ¡ czenia tabeli zamowienia_inter oraz klienci_hash , zwraca sum ¦ warto ± ci zamowie « dla ka » dego z klient ó w z okresu jednego miesi ¡ ca . SELECT k . nazwisko , sum ( z . kwota ) wartosc_zamowien FROM zamowienia_inter z , klienci_hash k WHERE z . id_klienta = k . id_klienta AND z . data_zam BETWEEN ( TO_DATE ( ' 01 -06 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -07 -2014 ' , 'DD - MM - YYYY ' )) GROUP BY k . nazwisko ; 6. Zapytanie wymagaj ¡ ce z ª ¡ czenia tabeli zamowienia_inter oraz klienci_hash , podaje liczb ¦ zam ó wie « z ª o » onych przez klient ó w mieszkaj ¡ cych w Warszawie , Bia ª ymstoku , Lublinie lub Radomiu . SELECT COUNT (*) liczba_zamowien FROM zamowienia_inter z , klienci_hash k WHERE z . id_klienta = k . id_klienta AND k . miasto IN ( ' WARSZAWA ' , ' BIALYSTOK ' , ' LUBLIN ' , ' RADOM ' );
80
Tablica 15: Czas wykonania zapyta« testowych (mierzony w ms) dla tabeli zamo-
wienia_inter o 60 mln wierszy i ro»nych kon guracjach indeksowych.
nr zapytania
bez
globalny
globalny
z
indeksów [ms]
niepartycjonowany
partycjonowany
[ms]
[ms]
[ms]
lokalnymi
1
16166
10615
18976
14573
2
458
738
2001
1435
3
473
432
5390
6177
4
2094
2077
3685
1843
5
167031
1261140
171135
156379
6
52849
406125
31948
30094
Tablica 16: Czas wykonania zapyta« testowych (mierzony w ms) dla tabeli zamo-
wienia_inter o 90 mln wierszy i ro»nych kon guracjach indeksowych.
nr zapytania
bez
globalny
globalny
z
indeksów [ms]
niepartycjonowany
partycjonowany
[ms]
[ms]
[ms]
lokalnymi
1
137638
6842
14300
9812
2
37835
41980
51207
35207
3
4981
2187
2391
2082
4
4825
52842
12983
4290
5
201742
238713
220820
213623
6
165309
1763925
144712
155926
81
Tablica 17: Czas wykonania zapyta« testowych (mierzony w ms) dla tabeli zamo-
wienia_inter o 120 mln wierszy i ro»nych kon guracjach indeksowych.
nr zapytania
bez
globalny
globalny
z
indeksów [ms]
niepartycjonowany
partycjonowany
[ms]
[ms]
[ms]
lokalnymi
1
141682
4927
13564
10981
2
6411
6981
9450
6004
3
7208
3562
3552
3823
4
7375
22760
23490
15073
5
281302
267354
259671
275453
6
250385
272880
171807
187083
Zapytanie nr 2 dla wszystkich rozmiarów tabeli i wszystkich kon guracji indeksów zamowienia_inter zostaªo wykonane szybciej w stosunku do niepartycjonowanej tabeli zamowienia. Ten wzrost efektywno±ci jest spowodowany mechanizmem przycinania partycji, gdy» jedyny warunek selekcji tego zapytania dotyczy klucza partycjonowania tabeli zamowienia_inter. Przeszukiwane s¡ tylko dwie partycje tabeli zamiast caªej. Równie» zapytanie nr 4 dla indeksów innych ni» z globalnym partycjonowanym metod¡ mieszaj¡c¡ zwraca wyniki w krótszym czasie. Co ciekawe zapytanie nr 6 mimo braku dodatkowych mechanizmów wspomagaj¡cych oprócz zrównoleglenia zapytania, wykonuje si¦ szybciej ni» dla tabeli zamowienia we wszystkich kon guracjach oprócz dla rozmiaru tabeli 60 mln wierszy, kiedy indeks na kluczu obcym nie jest partycjonowany.
82
Rysunek 19: Plan wykonania zapytania nr 2 dla tabeli zamowienia_inter o 60 mln wierszy, potwierdzaj¡cy u»ycie mechanizmu zrównoleglenia i przycinania partycji.
4.7 Tabela zam_inter_list Wyniki testów (wykonanych przez aplikacj¦ testow¡) na tabeli zam_inter_list partycjonowanej interwaªowo na kolumnie data_zam oraz nast¦pnie listowo na kolumnie wirtualnej region zostaªy przedstawione w Tablicy 18, Tablicy 19 oraz Tablicy 20. Kolumny tych tablic wskazuj¡ sposób indeksowania tabeli zam_inter_list w danym przypadku testowym. Kolejno nazwy tych kolumn oznaczaj¡:
•
bez indeksów - tylko globalny niepartycjonowany indeks na kluczu gªównym
id_zamowienia ;
•
globalny niepartycjonowany - z globalnym niepartycjonowanymi indeksami na kluczu gªównym, obcym id_klienta.
•
globalny partycjonowany - z niepartycjonowanymi indeksem na kluczu gªównym, partycjonowanym oraz globalnym drzewiastym partycjonowanym metod¡ mieszaj¡c¡ na bazie klucza obcego id_klienta.
•
z lokalnymi - z globalnym niepartycjonowanym indeksem na kluczu gªównym i z partycjonowanym lokalnie drzewiastym na kluczu obcym id_klienta ;
Dokªadny opis tabeli zam_inter_list zostaª zamieszczony we wcze±niejszej cz¦±ci pracy, w podrozdziale 3.3.5 Partycjonowanie dwupoziomowe interwaªowo-listowe.
83
Mierzono czas wykonania nast¦puj¡cych zapyta«:
1. Zapytanie zwraca liczb ¦ zam ó wie « z ª o » onych w regionie " WEST " . SELECT COUNT (*) FROM zam_inter_list WHERE region = ' WEST '; 2. Zapytanie zwraca sum ¦ warto ± ci zam ó wie « z okresu jednego miesi ¡ ca . SELECT SUM ( kwota ) FROM zam_inter_list WHERE data_zam BETWEEN ( TO_DATE ( ' 01 -06 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -07 -2014 ' , 'DD - MM - YYYY ' )); 3. Zapytanie zwraca sum ¦ warto ± ci zam ó wie « dla wybranego klienta z okresu jednego miesi ¡ ca . SELECT sum ( kwota ) FROM zam_inter_list WHERE id_klienta = 123456 AND z . data_zam BETWEEN ( TO_DATE ( ' 01 -02 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -03 -2014 ' , 'DD - MM - YYYY ' )); 4. Zapytanie zwraca ± redni ¡ sum ¦ warto ± ci zamowie « z okresu trzech miesi ¦ cy w regionie " CENTER " . SELECT AVG ( kwota ) FROM zam_inter_list WHERE data_zam BETWEEN ( TO_DATE ( ' 01 -02 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -05 -2014 ' , 'DD - MM - YYYY ' )) AND region = ' CENTER '; 5. Zapytanie wymagaj ¡ ce z ª ¡ czenia tabeli zam_inter_list oraz klienci_hash , zwraca sum ¦ warto ± ci zamowie « dla ka » dego z klient ó w z okresu jednego miesi ¡ ca . SELECT k . nazwisko , sum ( z . kwota ) wartosc_zamowien FROM zam_inter_list z , klienci_hash k WHERE z . id_klienta = k . id_klienta AND z . data_zam BETWEEN ( TO_DATE ( ' 01 -06 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -07 -2014 ' , 'DD - MM - YYYY ' )) GROUP BY k . nazwisko ; 6. Zapytanie wymagaj ¡ ce z ª ¡ czenia tabeli zam_inter_list oraz klienci_hash , podaje liczb ¦ zam ó wie « z ª o » onych przez klient ó w mieszkaj ¡ cych w Warszawie , Bia ª ymstoku , Lublinie lub Radomiu . SELECT COUNT (*) liczba_zamowien FROM zam_inter_list z , klienci_hash k WHERE z . id_klienta = k . id_klienta AND k . miasto IN ( ' WARSZAWA ' , ' BIALYSTOK ' , ' LUBLIN ' , ' RADOM ' );
84
Tablica
18:
Czas
wykonania
zapyta«
testowych
(mierzony
w
ms)
dla
tabeli
zam_inter_list o 60 mln wierszy i ro»nych kon guracjach indeksowych.
nr zapytania
bez
globalny
globalny
z
indeksów [ms]
niepartycjonowany
partycjonowany
[ms]
[ms]
[ms]
lokalnymi
1
14536
32099
40375
30056
2
5927
16718
18542
6529
3
5567
3088
9494
4979
4
1442
7229
8480
1740
5
205062
265974
179969
169422
6
84734
130104
147224
29203
Tablica
19:
Czas
wykonania
zapyta«
testowych
(mierzony
w
ms)
dla
tabeli
zam_inter_list o 90 mln wierszy i ro»nych kon guracjach indeksowych.
nr zapytania
bez
globalny
globalny
z
indeksów [ms]
niepartycjonowany
partycjonowany
[ms]
[ms]
[ms]
lokalnymi
1
16529
33839
40934
38301
2
7620
16963
19002
6027
3
6726
7109
7310
6110
4
2290
7531
8952
2105
5
414935
398618
42811
439112
6
176012
164091
179981
158713
85
Tablica
20:
Czas
wykonania
zapyta«
testowych
(mierzony
w
ms)
dla
tabeli
zam_inter_list o 120 mln wierszy i ro»nych kon guracjach indeksowych.
nr zapytania
bez
globalny
globalny
z
indeksów [ms]
niepartycjonowany
partycjonowany
[ms]
[ms]
[ms]
lokalnymi
1
18219
36509
42910
41981
2
9176
17890
19876
7564
3
11849
7104
7526
8697
4
2901
8812
9801
2598
5
507614
458588
521766
530953
6
281239
277046
295627
189963
Dzi¦ki temu, »e tabela zam_inter_list jest partycjonowana dwupoziomowo, zapytanie nr 4 z dwoma warunkami w kluzuli WHERE dotycz¡cymi kluczy partycjonowania, mo»e by¢ wykonane z u»yciem podwójnego przycinania partycji. Dla tabeli
zam_inter_list o 60 mln wierszy zpytanie to jest przetwarzane krócej dla indeksów lokalnych albo tylko z globalnym niepartcjonowanym indeksem klucza gªównego. W przypadku rozmiarów równych 90 i 120 mln wierszy wszystkie kon guracje indeksów u»yte w testach pozwalaj¡ uzyka¢ szybciej rezulataty zapytania ni» dla niepartycjonowanej tabeli zamowienia. Dla 90 i 120 mln wierszy wszystkie kon guracje indeksów tabeli zam_inter_list pozwalaj¡ na szybsze otrzymanie wyników równie» zapytania nr 6. Szczególnie du»y zysk odnosi si¦ w przypadku zastosowania indeksu lokalnego na kluczu obcym id_klienta. Warto równie» zwrócic uwag¦ na to, »e mimo i» metoda partycjonowania tabeli zam_inter_list pozwala na przycinanie partycji na podstawie kolumny data_zam, to najlepszy wyniki otrzymujemy w odniesieniu do zapyta«, które mog¡ przycina¢ równie» subpartycje. Dla zapytania z warunkiem dotycz¡cym tylko kolumny data_zam bardziej opªacalne okazaªo sie zastosowanie tabeli zamowienia_inter.
86
4.8 Tabela zam_range_hash Wyniki testów (wykonanych przez aplikacj¦ testow¡) na tabeli zam_range_hash partycjonowanej dwupoziomowo, zakresowo na kolumnie data_zam oraz nast¦pnie metod¡ mieszaj¡c¡ na kluczu obcym id_klienta, zostaªy przedstawione w Tablicy 21, Tablicy 22 oraz Tablicy 23. Kolumny tych tablic wskazuj¡ sposób indeksowania tabeli zam_range_hash w danym przypadku testowym. Nazwy kolumn oznaczaj¡:
•
bez indeksów - tylko globalny niepartycjonowany indeks na kluczu gªównym
id_zamowienia ;
•
z lokalnymi - z globalnym niepartycjonowanym indeksem na kluczu gªównym, z partycjonowanymi lokalnie indeksami bitmapowym na kolumnie region oraz drzewiastym na kluczu obcym id_klienta ;
Dokªadny opis tabeli zam_range_hash zostaª zamieszczony we wcze±niejszej cz¦±ci pracy, w podrozdziale 3.3.6 Partycjonowanie dwupoziomowe zakresowo-mieszaj¡ce. Mierzono czas wykonania nast¦puj¡cych zapyta«:
1. Zapytanie zwraca liczb ¦ zam ó wie « z ª o » onych w regionie " WEST " . SELECT COUNT (*) FROM zam_range_hash WHERE region = ' WEST '; 2. Zapytanie zwraca sum ¦ warto ± ci zam ó wie « z okresu jednego miesi ¡ ca . SELECT SUM ( kwota ) FROM zam_range_hash WHERE data_zam BETWEEN ( TO_DATE ( ' 01 -06 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -07 -2014 ' , 'DD - MM - YYYY ' )); 3. Zapytanie zwraca sum ¦ warto ± ci zam ó wie « dla wybranego klienta z okresu jednego miesi ¡ ca . SELECT sum ( kwota ) FROM zam_range_hash WHERE id_klienta = 123456 AND z . data_zam BETWEEN ( TO_DATE ( ' 01 -02 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -03 -2014 ' , 'DD - MM - YYYY ' )); 4. Zapytanie zwraca ± redni ¡ sum ¦ warto ± ci zamowie « z okresu trzech miesi ¦ cy w regionie " CENTER " . SELECT AVG ( kwota ) FROM zam_range_hash WHERE data_zam BETWEEN ( TO_DATE ( ' 01 -02 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -05 -2014 ' , 'DD - MM - YYYY ' )) AND region = ' CENTER '; 5. Zapytanie wymagaj ¡ ce z ª ¡ czenia tabeli zam_range_hash oraz klienci_hash , zwraca sum ¦ warto ± ci zamowie « dla ka » dego z klient ó w z okresu jednego miesi ¡ ca . SELECT k . nazwisko , sum ( z . kwota ) wartosc_zamowien 87
FROM zam_range_hash z , klienci_hash k WHERE z . id_klienta = k . id_klienta AND z . data_zam BETWEEN ( TO_DATE ( ' 01 -06 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -07 -2014 ' , 'DD - MM - YYYY ' )) GROUP BY k . nazwisko ; 6. Zapytanie wymagaj ¡ ce z ª ¡ czenia tabeli zam_range_hash oraz klienci_hash , podaje liczb ¦ zam ó wie « z ª o » onych przez klient ó w mieszkaj ¡ cych w Warszawie , Bia ª ymstoku , Lublinie lub Radomiu . SELECT COUNT (*) liczba_zamowien FROM zam_range_hash z , klienci_hash k WHERE z . id_klienta = k . id_klienta AND k . miasto IN ( ' WARSZAWA ' , ' BIALYSTOK ' , ' LUBLIN ' , ' RADOM ' );
Tablica
21:
Czas
wykonania
zapyta«
testowych
(mierzony
w
ms)
dla
tabeli
dla
tabeli
zam_range_hash o 60 mln wierszy i dwóch kon guracjach indeksowych.
Tablica
nr zapytania
bez indeksów [ms]
z lokalnymi [ms]
1
49198
6296
2
469
25026
3
144
1520
4
1916
34348
5
34494
78042
6
47234
66208
22:
Czas
wykonania
zapyta«
testowych
(mierzony
w
ms)
zam_range_hash o 90 mln wierszy i dwóch kon guracjach indeksowych.
nr zapytania
bez indeksów [ms]
z lokalnymi [ms]
1
51981
12871
2
1871
27800
3
281
2301
4
2319
3412
5
54981
82616
6
49896
84941
88
Tablica
23:
Czas
wykonania
zapyta«
testowych
(mierzony
w
ms)
dla
tabeli
zam_range_hash o 120 mln wierszy i dwóch kon guracjach indeksowych.
nr zapytania
bez indeksów [ms]
z lokalnymi [ms]
1
52987
18967
2
2876
35610
3
1180
3021
4
5410
4037
5
65387
87123
6
76001
104652
Tabela zam_range_hash osi¡gn¦ªa lepsze wyniki od niepartycjonowanej tabeli
zamowienia tylko dla zapytania nr 6. Jednak w stosunku do reszty tabel partycjonowanych szybciej zostaªo wykonane zapytanie nr 3, szczególnie bior¡c pod uwag¦ pierwsz¡ kon guracj¦ - tylko niepartycjonowany indeks na kluczu gªównym. Dochodzi tu do przycinania partycji na dwóch poziomach zgodnie z kluczami partycjonowania i subpartycjonowania. Równie» dobre wyniki w porównaniu do innych metod partycjonowania zostaªy uzyskane dla zapytania nr 5. Mamy tu doczynienia z jednoczesnym przycinaniem partycji po kolumnie data_zam oraz zª¡czeniami na poziomie partycji. Na pocz¡tku zostaje wyselekcjonowna partycja do której nale»¡ daty nale»¡ce do przedziaªu z warunku WHERE zapytania, a potem dokonywane s¡ zª¡czenia mi¦dzy wybranymi subpartycjami tabeli zam_range_hash i partycjami tabeli klienci_hash.
89
Rysunek 20: Plan wykonania zapytania nr 5 dla tabeli zam_range_hash o 60 mln wierszy, wykorzystanie dwóch mechanizmów poprawiaj¡cych wydajno±¢ - przycinania partycji oraz zª¡cze« na poziomie partycji.
90
4.9 Wnioski z bada« testowych Przeprowadzone testy potwierdziªy tez¦, »e partycjonowanie daje tym wi¦ksze korzy±ci im wi¦kszyh dotyczy tabel. Przy maªych rozmiarach tabel, ze wzgl¦dy na efektywno±c wykonywania zapyta«, cz¦sto nie ma sensu tworzy¢ partycji. Zwykle wystarcz¡ m¡drze dodane niepartycjonowane indeksy. Wa»ne jest równie», aby w przemy±lany sposób dobiera¢ metod¦ partycjonowania, poniewa» ¹le dopasowana mo»e przynie±¢ skutki przeciwne do zamierzonych. Przeprowadzone w ramach tej pracy badania wykazaªy, »e zapytania bez warunków selekcji czy zª¡cze« opartych na kluczach partycjonowania wykonywane s¡ nawet kilkukrotnie dªu»ej w stosunku do tabel niepartycjonowanych. Czasem równie» dla wi¦kszych tabel partycjonowanie mo»e okaza¢ si¦ zb¦dne. W czasie wykonywania testów zaobserwowano, »e dla rozmiaru tabel równego 90 i 120 mln wierszy zapytanie:
SELECT COUNT (*) FROM zamowienia_list WHERE region = ' WEST '; mimo, »e jest wykonywane dla tabeli zamowienia_list z u»yciem mechanizmu przycinania partycji, to jednak szybciej zwraca rezultaty dla tabeli zamowienia z indeksem bitmapowym na kolumnie region, która jest kluczem partycjonowania tabeli zamo-
wienia_list. Warto zwróci¢ uwag¦, »e w tym przypadku zostaje skanowana jedna z pi¦ciu partycji tabeli zamowienia_list, ale te partycje rosn¡ª wraz ze wzrostem liczby rekordów tabeli. Przy 90 i 120 mln wierszy bardziej efektywny staje si¦ indeks bitmapowy. W przypadku partycjonowania tabel najlepsze rezultaty otrzymuje si¦ przy zapytaniach korzystaj¡cych z kolumn, które s¡ kluczami partycjonowania. Najkorzystniej jest, gdy klucze partycjonowania brane s¡ pod uwag¦ we wszystkich warunkach selekcji zapytania, co umo»liwia skorzystanie z najefektywniejszego mechanizmu przycinania partycji. Zª¡czenia s¡ wykonywane najszybciej dla tabel partycjonowanych metod¡ referencyjn¡ lub takich, których klucze partycjonowania s¡ zgodne z kluczami zª¡czenia. Sposobem na uzyskanie zª¡czenia na poziomie partycji oraz przycinania partycji jest stworzenie tabeli partycjonowanej dwupoziomowo, tak aby jej klucz partycjonowania nale»aª do warunku selekcji w klauzuli where zapytania i dodatkowo byªa subpartycjonowana na kluczu zª¡czenia. Zale»no±ci te widoczne s¡ równie» w wynikach testów. Korzystny wpªyw przycinania partycji mo»na byªo
91
zaobserwowa¢ np. przy zapytaniu:
SELECT SUM ( kwota ) FROM zamowienia_inter WHERE data_zam BETWEEN ( TO_DATE ( ' 01 -06 -2014 ' , 'DD - MM - YYYY ' )) AND ( TO_DATE ( ' 01 -07 -2014 ' , 'DD - MM - YYYY ' )); kiedy dla ka»dej z trzech wielko±ci (60, 90 120 mln wierszy) tabeli zamowienia_inter otrzymano kilkukrotnie lepsze wyniki czasowe w porównaniu z tabel¡ niepartycjonowan¡. Pozytywny wpªyw zª¡cze« na poziomie partycji oraz zrównoleglenia wykonania byª szczególnie widoczny dla tabel klienci_ref i zamowienia_ref. Zapytanie:
SELECT COUNT (*) liczba_zamowien FROM zamowienia_ref z , klienci_ref k WHERE z . id_klienta = k . id_klienta AND k . miasto IN ( ' WARSZAWA ' , ' BIALYSTOK ' , ' LUBLIN ' , ' RADOM ' ); dla wszystkich rozmiarów testowanych tabel zostaªo szybciej wykonane w przypadku tabel partycjonowanych referencyjnie. Dla tabeli klienci_ref o 60 mln wierszy i za-
mowienia_ref o 120 mln wierszy i zastosowaniu indeksów partycjonowanych lokalnie zapytanie zwróciªo wyniki o 57% szybciej ni» dla tabeli niepartycjonowanej. Partycjonowanie indeksów jest bardziej skomplikowane. Ci¦»ko jest stworzy¢ jednoznaczn¡ reguª¦, kiedy warto stosowa¢ partycjonowanie indeksów i jak dopasowa¢ metod¦. Równie» w dokumentach Oracle dotycz¡cych partycjonowania, mo»na wyczyta¢, »e podj¦cie decyzji o sposobie partycjonowania najlepiej poprzedzi¢ testami. W ten sposób mo»emy uzyska¢ pewno±¢, »e spowodujemy wzrost w wydajno±ci wykonywania zapyta«. W ramach tej pracy partycjonowanie byªo gªównie sprawdzane pod k¡tem szybko±ci wykonania zapyta« typy SELECT. Testy wykazaªy, »e najbezpieczniej jest stosowa¢ indeksy niepartycjonowane lub partycjonowane lokalnie. Jak wskazaªam w cz¦±ci teoretycznej pracy, partycjonowanie globalne mo»e sta¢ si¦ uci¡»liwe, jesli regularnie wykonuje si¦ operacje administratorskie dotycz¡ce przenoszenia partycji, usuwania du»ych fragmentów danych itp.. Ci¦»ko mi jednak byªo zasymulowa¢ ±rodowisko wielodost¦powe, z wieloma jednoczesnymi transakcjami, a wtedy wg dokumentacji Oracle, partycjonowanie globalne przynosi najwi¦ksze korzy±ci.
92
5
Podsumowanie Celem niniejszej pracy in»ynierskiej byªo zapoznanie sie z opcj¡ partycjonowania
tabel i indeksów w Oracle Database 11gR2 i przeprowadzenie bada« nad efektywno±ci¡ wykonywania ró»nego typu zapyta« na partycjonowanych obiektach. Autorka zawarªa w pracy wst¦p teoretyczny zawieraj¡cy najwa»niejsze infromacje dotycz¡ce opisywanej opcji. Przedstawiªa mechanizmy wspomagaj¡ce efektywno±¢ partycjonowania oraz wskazaªa jego potencjalne zastosowania. W ramach bada« skon gurowane zostaªo ±rodowisko badawcze z uwzgl¦dnieniem napisania generatorów danych testowych oraz aplikacji sprawdzaj¡cej czas wykonania ka»dego z zapyta« testowych na ró»nych kombinacja partycjonowanych tabel oraz indeksów. Wyniki testów zostaªy przedstawione i nast¦pnie podsumowane. Potwierdziªy si¦ tezy stawiane przez rm¦ Oracle w dokumentacji opcji partycjonowania. Partycjonowanie jest relatywnie prostym mechanizmem mog¡cym znacznie poprawi¢ wydajno±c bazy danych na wielu obszarach. Zarz¡dzanie partycjonowanymi obiektami najcz¦±ciej jest bardzo wygodne, a korzy±ci jakie przynosi ta opcja mog¡ pozytywnie zaskakiwa¢. Testy wykonane w ramach tej pracy wykazaªy, »e zastosowanie partycjonowania w odniesieniu do konkretnych zapyta«, mo»e spowodowa¢ nawet kilkukrotne przyspieszenie ich wykonania. Wydaje si¦, »e partycjonowanie tabel i indeksów mo»e by¢ dobr¡ odpowiedzi¡ na wspóªczesne problemy z magazynowaniem i przetwarzaniem ogromnych zbiórów danych. Nale»y jednak pami¦ta¢, »e ¹le dobrane metody partycjonowania lub zastowanie tej techniki w stosunku do wzgl¦dnie maªych tabel mo»e mie¢ negatywny wpªyw na dziaªanie bazy. Wyniki opisanych w tym dokumencie bada« wskazuj¡, »e decyzj¦ o wyborze metody partycjonowania warto podejmowa¢ na bazie znajomo±ci typów zapyta« jakie b¦d¡ wykonywane w systemie oraz poprze¢ wcze±niej przeprowadzonymi testami. W czasie realizacji pracy in»ynierskiej dyplomantka miaªa okazj¦ znacznie poszerzy¢ swoja wiedz¦ nie tylko na temat opcji partycjonowania, ale równie» sposobu dziaªania systemu i wielu mechanizmów wyst¦puj¡cych w systemie Oracle Database. Szczególnie cenne byªo zapoznanie si¦ z metodami optymalizacji zapyta« i skutkami zastosowania ró»nych planów wykonania. Zdobyªa równie» podstawowe umiej¡tno±ci administrowania baz¡ danych Oracle oraz dowiedziaªa si¦, sk¡d czerpa¢ cenne i wiarygodne informacje dotycz¡ce tego systemu.
93
Literatura [1]
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-partitioning-11gr22010-10-189137.pdf
[2]
http://docs.oracle.com/cd/E11882_01/server.112/e25523/partition.htm
[3]
http://www.oracle.com/us/corporate/oracle-fact-sheet-079219.pdf
[4]
https://www.sec.gov/Archives/edgar/data/1341439/000119312513272832/0001193125-13272832-index.htm
[5]
https://www.ycharts.com/companies/ORCL/r_and_d_expense
[6]
Arup Nanda
[7]
https://blogs.oracle.com/datawarehousing/entry/compressing_individual_partiti
[8]
https://blogs.oracle.com/datawarehousing/entry/managing_optimizer_statistics
[9]
http://structureddata.org/2008/07/16/oracle-11g-incremental-global-statistics-on-
Nonstop Partition Operations,
Oracle Magazine, May/June 2014
partitioned-tables/
[10]
http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_avail.htm
[11]
http://docs.oracle.com/cd/B28359_01/server.111/b28313/usingpe.htm
[12]
http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin005.htm
[13]
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-theexplain-plan-052011-393674.pdf
[14]
http://docs.oracle.com/cd/B28359_01/server.111/b28313/usingpe.htm
[15]
http://psoug.org/reference/dbms_random.html
[16]
http://www.dba-oracle.com/t_11g_new_virtual_column.htm
[17]
Bob Bryla, Kevin Loney [tª. Piotr Pilch],
danych, [18]
Oracle database 11g: podr¦cznik administratora baz
Helion, 2010
Robert G. Freeman, Arup Nanda [tª. Kamila Primke],
Helion, 2009
94
Oracle database 11g: nowe mo»liwo±ci,
A
Zaª¡cznik A - kod ¹ródªowy aplikacji testowej Poni»ej znajduje si¦ kod ¹ródªowy aplikacji testowej Polaczenie.java. W tym
przykªadzie aplikacja nawi¡zuje poª¡czenie z baz¡ danych tbtest z maszyny orem1.ii.pw.edu.pl. Wykonywane s¡ zapytania na tabelach KLIENCI oraz ZAMOWIENIA.
1
import java . sql .*;
2
import java . util . Properties ;
3
import java . util . logging . Level ;
4
import java . util . logging . Logger ;
5
import java . io . PrintWriter ;
6 7 8 9 10
public class Polaczenie { public void polaczenie_z_baza () { String orem1 = " jdbc : oracle : thin : @orem1 . ii . pw . edu . pl :1521: dbtest " ; String orem2 = " jdbc : oracle : thin : @orem2 . ii . pw . edu . pl :1521: dbtest2 " ;
11 12
String buffer = " ALTER SYSTEM FLUSH BUFFER_CACHE " ;
13
String shared = " ALTER SYSTEM FLUSH SHARED_POOL " ;
14
String query1 = " SELECT COUNT (*) FROM klienci WHERE miasto = ' KATOWICE '" ;
15
String query2 = " SELECT COUNT (*) FROM zamowienia WHERE region = ' WEST '" ;
16
String query3 = " SELECT SUM ( kwota ) FROM zamowienia " +
17
" WHERE data_zam BETWEEN ( TO_DATE ( '01 -06 -2014 ' , 'DD - MM - YYYY ')) " +
18
" AND ( TO_DATE ( '01 -07 -2014 ' , 'DD - MM - YYYY ')) " ;
19
String query4 = " SELECT sum ( kwota ) FROM zamowienia " +
20
" WHERE id_klienta = 123456 AND " +
21
" z . data_zam BETWEEN ( TO_DATE ( '01 -02 -2014 ' , 'DD - MM - YYYY ')) " +
22
" AND ( TO_DATE ( '01 -03 -2014 ' , 'DD - MM - YYYY ')) " ;
23
String query5 = " SELECT AVG ( kwota ) FROM zamowienia
" +
24
" WHERE data_zam BETWEEN ( TO_DATE ( '01 -02 -2014 ' , 'DD - MM - YYYY ')) " +
25
" AND ( TO_DATE ( '01 -05 -2014 ' , 'DD - MM - YYYY ')) AND region = ' CENTER '" ;
26
String query6 = " SELECT k . nazwisko , sum ( z . kwota ) wartosc_zamowien " +
27
" FROM zamowienia z , klienci k
28
" z . data_zam BETWEEN ( TO_DATE ( '01 -06 -2014 ' , 'DD - MM - YYYY ')) " +
29
" AND ( TO_DATE ( '01 -07 -2014 ' , 'DD - MM - YYYY ')) GROUP by k . nazwisko " ;
30
WHERE z . id_klienta = k . id_klienta AND " +
String query7 = " SELECT k . nazwisko , COUNT (*) liczba_zamowien " +
31
" FROM zamowienia z , klienci k WHERE z . id_klienta = k . id_klienta AND " +
32
" k . miasto IN ( ' WARSZAWA ', ' BIALYSTOK ', ' LUBLIN ', ' RADOM ') " ;
95
33
try
34
{
35
DriverManager . registerDriver ( new oracle . jdbc . driver . OracleDriver ());
36
System . out . println ( " Sterowniki zaladowane " );
37
Connection conn = null ;
38
try {
39
Properties props = new Properties ();
40
props . put ( " user " , " SYS " );
41
props . put ( " password " , " Kwasia04 " );
42
props . put ( " internal_logon " , " SYSDBA " );
43
conn = DriverManager . getConnection ( orem1 , props );
44
}
45
catch ( SQLException ex ) { Logger . getLogger ( Polaczenie . class . getName ()). log ( Level . SEVERE ,
46
" nie udalo sie
47
polaczyc
z baza
danych " , ex );
System . exit ( -1);
48 49
}
50
System . out . println ( " Polaczenie nawiazane " );
51
Statement stm = null ;
52
PrintWriter zapis = new PrintWriter ( " dane . txt " );
53
try {
54
conn . setAutoCommit ( false );
55
stm = conn . createStatement ();
56
stm . executeUpdate ( buffer );
57
stm . executeUpdate ( shared );
58 59
test ( stm , query1 , zapis );
60
test ( stm , query2 , zapis );
61
test ( stm , query3 , zapis );
62
test ( stm , query4 , zapis );
63
test ( stm , query5 , zapis );
64
test ( stm , query6 , zapis );
65
test ( stm , query7 , zapis );
66
conn . commit ();
67
}
68
catch ( SQLException ex ) {
69
System . out . println ( " Blad wykonania polecenia " + ex . toString ());
70
conn . rollback ();
71
} 96
72
finally {
73
zapis . close (); if ( stm != null ) {
74
try {
75 76
stm . close ();
77
System . out . println ( " Statement zamkniety " ); } catch ( SQLException e )
78
{ System . out . println ( " Nie udalo sie zamknac res . " +
79
e . toString ());}
80 81
}
82
if ( conn != null ) { try {
83 84
conn . close ();
85
System . out . println ( " Polaczenie zakonczone " ); } catch ( SQLException e )
86
{ System . out . println ( " Nie udalo sie zamknac stm . "
87
+ e . toString ());}
88
}
89
}
90 91
}
92
catch ( Exception e ) {
93
System . out . println ( " Blad " );
94
e . printStackTrace (); }
95
}
96 97 98
private void test ( Statement stm , String query , PrintWriter zapis ) throws Exception
99 100
{
101
long start = System . currentTimeMillis ();
102
ResultSet res = stm . executeQuery ( query );
103
long time = System . currentTimeMillis () - start ;
104 105
res . close ();
106
System . out . println ( " Czas : " + time + " ms dla zapytania " + query + " ; " );
107
zapis . println ( " Czas : " + time + " ms dla zapytania " + query + " ; " + '\ n ' );
108
}
109 110
97
111
public static void main ( String [] args ){
112
Polaczenie oracle = new Polaczenie ();
113
oracle . polaczenie_z_baza ();
114
}
115
}
98
