X Konferencja PLOUG
Kościelisko
Październik 2004
Hurtownie danych oparte o Oracle9i/10g –
przegląd funkcjonalności
Robert Wrembel
Politechnika Poznańska, Instytut Informatyki
ul. Piotrowo 3A, 60-965 Poznań
Robert.Wrembel@cs.put.poznan.pl
Streszczenie
Hurtownie (magazyny) danych stają się obecnie niezbędnym komponentem systemów informatycznych w dużych fir-
mach i instytucjach. Zawartość hurtowni danych poddana złożonej analizie i eksploracji danych staje się bezcennym
źródłem wiedzy wykorzystywanej w procesach decyzyjnych. Wiodące firmy wytwarzające oprogramowanie, m.in.
Oracle dostarczają zaawansowanych narzędzi do budowy hurtowni danych, ich optymalizacji, zarządzania nimi, oraz do
budowy aplikacji analitycznych. Celem niniejszego artykułu jest skrótowe omówienie oprogramowania Oracle wyko-
rzystywanego w hurtowniach danych. Poruszone zostaną: (1) funkcjonalność serwera bazy danych Oracle9i/10g, (2)
narzędzia integracji danych, (3) narzędzia do budowania aplikacji.
192
Robert Wrembel
Hurtownie danych oparte o Oracle9i/10g – przegląd funkcjonalności
193
1. Wprowadzenie
Hurtownie danych, zwane również magazynami danych (ang. data warehouses) stają się obec-
nie niezbędnym komponentem systemów informatycznych w dużych firmach i instytucjach.
Z technologicznego punktu widzenia hurtownia danych jest ogromną bazą danych, do której wczy-
tuje się dane z tzw. produkcyjnych źródeł danych. Na hurtowni danych pracują tzw. aplikacje ana-
lityczne (ang. On-line Analytical Processing - OLAP), czy eksploracji danych (ang. data mining).
Aplikacje takie są zorientowane na przetwarzanie danych historycznych i zagregowanych. Więk-
szość operacji realizowanych przez tego typu aplikacje obejmuje złożone zapytania wykorzystują-
ce łączenie, filtrowanie, agregowanie, wymagające dostępu do milionów rekordów tabel bazy da-
nych. Przykładami takich zapytań mogą być: Jaki jest trend sprzedaży towarów z branży AGD w
ostatnich kilku tygodniach? Jaki jest rozkład sprzedaży lodówek w województwie wielkopolskim?
Tak więc, przetwarzanie w aplikacjach analitycznych charakteryzuje się operacjami odczytu du-
żych wolumenów danych, przetwarzanych następnie przez złożone funkcje analityczne.
Zawartość hurtowni danych poddana złożonej analizie i eksploracji danych staje się bezcennym
źródłem wiedzy wykorzystywanej w procesach decyzyjnych. Dzięki szybkiej analizie bazującej na
pełnej i aktualnej informacji o stanie firmy, kadra zarządzająca może podejmować właściwe decy-
zje o strategicznym znaczeniu dla rozwoju danego przedsiębiorstwa.
Wiodące firmy wytwarzające oprogramowanie, m.in. Oracle dostarczają zaawansowanych na-
rzędzi do budowy hurtowni danych, ich optymalizacji, zarządzania nimi, oraz do budowy aplikacji
analitycznych.
Budując hurtownię danych należy sprostać wielu problemom techniczno/technologicznym. Do
najważniejszych z nich należą: (1) zaprojektowanie właściwego schematu/struktury magazynu, (2)
sposób zasilania hurtowni danymi, (3) zbudowanie funkcjonalnych aplikacji analitycznych, (4)
zapewnienie efektywnego dostępu do dużych wolumenów danych. Niniejszy artykuł stanowi krót-
ki przegląd funkcjonalności oprogramowania Oracle w zakresie wspomnianych problemów.
2. Architektura systemu magazynu danych
Dane zasilające magazyn danych są często przechowywane w heterogenicznych systemach in-
formatycznych, czyli posiadających różne struktury, funkcjonalność i wykorzystujących różne
modele danych (np. hierarchiczne, relacyjne, obiektowe, semistrukturalne), w dokumentach tek-
stowych, czy arkuszach kalkulacyjnych. Często nawet w ramach tej samej instytucji wykorzystuje
się różne systemy informatyczne. Heterogeniczność źródeł danych utrudnia spójny dostęp do in-
formacji. Dodatkowym problemem jest geograficzne rozproszenie źródeł. Koniecznym jest więc
dostarczenie oprogramowania, które zapewni spójny i zintegrowany dostęp do takich źródeł. Dane
do magazynu wczytuje się w złożonym procesie, zwanym ETL (Extraction-Translation-Loading).
Proces ten składa się z trzech następujących faz: odczytu danych ze źródeł (Extraction), transfor-
macji ich do wspólnego modelu wykorzystywanego w magazynie wraz z usunięciem wszelkich
niespójności (Translation), wczytanie danych do magazynu (Loading). Architektura techniczna
systemu magazynu danych wspiera ten proces.
Na rysunku 1 przedstawiono w pełni funkcjonalną architekturę takiego systemu. Obiekty ozna-
czone jako ZD
1
, ZD
2
, ZD
n
reprezentują źródła danych. Z każdym z takich źródeł jest związana
warstwa oprogramowania – konwertera i monitora. Zadaniem pierwszego z nich jest transformo-
wanie danych z formatu wykorzystywanego w źródle, do formatu wykorzystywanego w hurtowni
danych. Zadaniem modułu monitora jest wykrywanie zmian w danych źródłowych i ich przekazy-
wanie do warstwy oprogramowania integratora. Moduł integratora jest odpowiedzialny za inte-
growanie danych w jeden spójny zbiór przed ich wczytaniem do hurtowni. Hurtownia danych za-
wiera zarówno dane elementarne, bieżące i historyczne, jak i dane przetworzone – zagregowane.
194
Robert Wrembel
Rys. 1. Podstawowa architektura systemu hurtowni danych
Centralna hurtownia danych zawiera dane dla wszystkich grup decydentów. Ze względu na
ilość przechowywanych w niej danych, wygodnie jest budować w oparciu o nią, małe tematyczne
hurtownie (ang. data marts), zawierające dane opisujące wąskie dziedziny funkcjonowania firmy.
Taka tematyczna hurtownia zawiera dane zwykle na wyższym poziomie agregacji niż w hurtowni
centralnej. Przykładowo, hurtownia danych nt. ruchu telefonicznego i opłat klientów operatora
sieci komórkowej może posłużyć do zbudowania dwóch data marts opisujących odpowiednio na-
tężenie ruchu telefonicznego w ciągu doby i ranking klientów ze względu na wysokość płaconych
rachunków.
3. Modele danych – reprezentacja danych w hurtowni
Dane w magazynie można reprezentować i przechowywać w oparciu o model relacyjny, zwany
również ROLAP (ang. Relational OLAP) i wielowymiarowy, zwanym również MOLAP lub MDO-
LAP (ang. Multidimensional OLAP). Często w tej samej bazie danych reprezentuje się informacje
częściowo w modelu ROLAP, a częściowo w MOLAP. Taki sposób reprezentacji nazywa się hy-
brydowym – HOLAP (ang. Hybrid OLAP). Baza danych Oracle9i/10g umożliwia reprezentowanie
i przechowywanie danych we wszystkich wspomnianych wyżej modelach.
3.1. ROLAP
Magazyn danych w technologii ROLAP jest implementowany w postaci tabel, których schemat
posiada najczęściej strukturę gwiazdy (ang. star schema) lub płatka śniegu (ang. snowflake sche-
ma) lub konstelacji faktów (ang. fact constellation) lub strukturę gwiazda–płatek śniegu (ang. star-
flake schema). W przypadku implementacji relacyjnej zalecane jest zdefiniowanie dodatkowych
logicznych obiektów bazy danych opisujących ww. schematy. Obiektami tymi są wymiar (ang.
dimension), hierarchia wymiaru (ang. dimension hierarchy) i zależności funkcyjne (ang. functional
dependencies).
3.1.1. Schemat gwiazdy, płatka śniegu i konstelacji faktów
Przykładowy schemat gwiazdy przedstawia rysunek 2. Centralna tabela Sprzedaż zawiera in-
formacje o sprzedaży pewnych produktów, w pewnych sklepach, w określonym czasie. Tabele
Sklepy, Produkty i Czas są nazywane tabelami wymiarów (ang. dimension tables), natomiast tabela
centralna jest nazywana tabelą faktów (ang. fact table). Atrybuty tabeli faktów przechowujące in-
formacje o sprzedaży są nazywane miarami (ang. measures), np. wartość, l_sztuk. Tabela faktów –
Hurtownie danych oparte o Oracle9i/10g – przegląd funkcjonalności
195
Sprzedaż zawiera również atrybuty produkt_id, sklep_id, data, których wartości wskazują na od-
powiednie wymiary. W takim schemacie tabele wymiarów są zdenormalizowane, por. tabele Skle-
py, Produkty, Czas.
Rys. 2. Schemat gwiazdy
Jeśli wymiary są znormalizowane, wówczas schemat magazynu danych ma postać płatka śnie-
gu (ang. snowflake schema). Przykładowy schemat o takiej strukturze został przedstawiony na
rysunku 3. W tym przypadku, wymiary Lokalizacja, Produkty i Czas mają postać hierarchii. Przy-
kładowo, wymiarze Lokalizacja każdy sklep (tabela Sklepy) znajduje się w mieście (tabela Mia-
sta), które z kolei znajduje się w województwie (tabela Województwa).
Rys. 3. Schemat płatka śniegu
Schemat gwiazdy lub płatka śniegu, w którym ten sam wymiar jest powiązany z wieloma tabe-
lami faktów nazywa się schematem konstelacji faktów (ang. fact constellation schema). Natomiast
196
Robert Wrembel
schemat, w którym część wymiarów ma postać znormalizowaną (tzn. posiadają strukturę hierar-
chiczną), a część ma postać zdenormalizowaną nazywa się schematem gwiazdy–płatka śniegu. W
praktyce, ze względów efektywnościowych najczęściej stosuje się schematy gwiazdy lub gwiaz-
dy–płatka śniegu.
3.1.2. Modelowanie wymiarów – logiczne obiekty bazy danych
Implementując hurtownię w oparciu o jeden z powyższych schematów, jest zalecane zdefinio-
wanie dodatkowych logicznych obiektów bazy danych opisujących ww. schematy. Obiektami
tymi są wymiar (ang. dimension), hierarchia wymiaru (ang. dimension hierarchy) i zależności
funkcyjne (ang. functional dependencies).
Jako przykład ilustrujący tworzenie ww. obiektów rozważmy tabelę wymiaru Produkty, której
schemat przedstawiono poniżej.
Name Null? Type
---------- -------- ------------
PRODUKT_ID NOT NULL NUMBER(6)
NAZWA NOT NULL VARCHAR2(30)
CENA_DET NOT NULL NUMBER(6,2)
CENA_HURT NOT NULL NUMBER(6,2)
PODGRUPA NOT NULL VARCHAR2(30)
PODGR_INF VARCHAR2(50)
GRUPA NOT NULL VARCHAR2(30)
GRUPA_INF VARCHAR2(50)
Rys. 4. Przykładowa hierarchia wymiaru Produkty
Poniższe polecenie create dimension definiuje dla tabeli Produkty hierarchię wymiaru
i zależności funkcyjne. Hierarchię tę przedstawiono na rysunku 4, natomiast zależności funkcyjne
są następujące: id_prod
{nazwa, cena_det, cena_hurt}, podgrupa {podgr_inf}, grupa
{grupa_inf}.
create dimension dim_produkty
level produkt is produkty.id_prod
level podgrupa is produkty.podgrupa
level grupa is produkty.grupa
hierarchy hier_produkty
(produkt child of
podgrupa child of
grupa)
attribute produkt determines
(nazwa, cena_det, cena_hurt)
attribute podgrupa determines
(podgr_inf)
attribute grupa determines
(grupa_inf);
Omawiane obiekty są wykorzystywane przez kosztowy optymalizator zapytań w procesie prze-
pisywania zapytań.
3.2. MDOLAP
Hurtownia danych zaprojektowana w technologii MOLAP do przechowywania danych wyko-
rzystuje wielowymiarowe tablice (ang. multidimensional arrays, datacubes). Tablice te zawierają
wstępnie przetworzone (m.in. zagregowane) dane pochodzące z wielu źródeł. Przykładowa trój-
wymiarowa tablica została przedstawiona na rysunku 5. Zawiera ona trzy wymiary: Lokalizacja,
Czas i Produkty oraz zagregowane informacje (poszczególne kostki) o sprzedaży wybranych pro-
duktów w poszczególnych latach, w wybranych miastach.
Hurtownie danych oparte o Oracle9i/10g – przegląd funkcjonalności
197
Rys. 5. Przykładowa trójwymiarowa tablica opisująca miarę sprzedaż w trzech wymiarach:
Lokalizacji, Czasu i Produktu
W systemie Oracle9i/10g dane wielowymiarowe są przechowywane w tzw. przestrzeni anali-
tycznej (ang. analytic workspace). Definiowanie tej przestrzeni i zarządzanie nią realizuje się albo
z wykorzystaniem oprogramowania Analytic Workspace Manager, albo Warehouse Builder,
albo z poziomu SQL wykorzystując do tego celu pakiety systemowe. Zbiór pakietów rodziny
CWM2 umożliwia zarządzanie informacjami słownikowymi (metadanymi) opisującymi przestrzeń
analityczną. DBMS_AWM zawiera procedury tworzenia przestrzeni analitycznej, natomiast
DBMS_AW zawiera procedury umożliwiające operowanie na danych wielowymiarowych, m.in.
wczytywanie danych z tabel i analizę danych. DBMS_AW_UTITLITIES udostępnia procedury
zarządzania miarami w przestrzeni analitycznej. OLAP_TABLE umożliwia prezentowanie danych
wielowymiarowych w postaci tabelarycznej (relacyjnej).
Poniżej przedstawiono wybrane przykładowe polecenia obsługi danych w przestrzeni anali-
tycznej. Polecenia te zostały wykonane z wykorzystaniem pakietu systemowego DBMS_AW. Po-
lecenie 1. tworzy nową pustą przestrzeń analityczną o nazwie test_ws. Polecenie 2. definiuje wy-
miary sklepy, produkty i czas. Polecenie 3. definiuje zmienną l_sztuk reprezentującą miarę. Wy-
miarami dla niej są sklepy, produkty i czas. Polecenie 4. wpisuje wartości do wymiaru sklepy.
1. exec DBMS_AW.EXECUTE('AW CREATE test_ws;')
2. exec DBMS_AW.EXECUTE('DEFINE sklepy DIMENSION TEXT W 15; -
DEFINE produkty DIMENSION TEXT W 20; -
DEFINE czas DIMENSION TEXT W 15;')
3. exec DBMS_AW.EXECUTE('DEFINE l_sztuk VARIABLE DECIMAL -
<sklepy produkty czas>;')
4. exec DBMS_AW.EXECUTE('MAINTAIN sklepy ADD ''SKLEP1'' ''SKLEP2'' ''SKLEP3''
-
''SKLEP4'' ''SKLEP5'' ''SKLEP6'';')
Kolejne trzy polecenia służą do prezentowania wartości zmiennej l_sztuk. Polecenie 5. zawęża
wartości w wymiarze czas do miesiąca styczeń. Natomiast polecenie 6 zawęża wartości w wymia-
rze sklepy do trzech sklepów. Polecenie 7. prezentuje dane wielowymiarowe reprezentowane
zmienną l_sztuk.
5. exec DBMS_AW.EXECUTE('LIMIT czas TO ''styczeń'';')
6. exec DBMS_AW.EXECUTE('LIMIT sklepy TO ''SKLEP1'' to ''SKLEP3'';')
7. exec DBMS_AW.EXECUTE('REPORT l_sztuk;')
CZAS: styczeń
------------L_SZTUK-------------
-------------SKLEPY-------------
PRODUKTY SKLEP1 SKLEP2 SKLEP3
---------------- ---------- ---------- ----------
Eternity for men 1,00 2,00 NA
Polo NA NA 2,00
Adidas sport 2,00 1,00 NA
STR8 NA NA NA
Rioja 5,00 6,00 2,00
198
Robert Wrembel
Bordeaux 4,00 NA NA
Żubrówka 1,00 NA NA
Dębowa NA NA NA
4. Zasilanie danymi
Głównym problemem w dostępie do źródeł zewnętrznych z hurtowni jest heterogeniczność
tych źródeł. Nawet jeśli źródła są bazami danych, to bardzo często są to bazy danych pochodzące
od różnych producentów, a co za tym idzie, posiadające różną funkcjonalność, reprezentację da-
nych i dialekt języka SQL. Z tego względu, dostęp z jednej bazy danych do innej musi być reali-
zowany za pomocą dedykowanego oprogramowania dla tych baz. Oprogramowanie to nosi nazwę
gateway'a. Jego zadaniem jest m.in. transformowanie dialektów SQL i reprezentacji (typów) da-
nych przesyłanych między bazami danych.
Z instalacją Oracle9i/10g jest dostarczane oprogramowanie Transparent Gateways, które
umożliwia dostęp i wymianę danych z większością głównych komercyjnych systemów relacyj-
nych, tj. wytwarzanych przez IBM, Sybase, Microsoft, NCR. Ponadto, z wykorzystaniem sterowni-
ków ODBC/JDBC można realizować dostęp do wspomnianych wyżej i wielu innych baz danych.
Natomiast definiowanie całego procesu ETL jest wspomagane oprogramowaniem Warehouse
Builder.
Dane do hurtowni wczytuje się często również z plików tekstowych. W tym zakresie rozwiąza-
nia Oracle9i/10g obejmują m.in.: pakiet systemowy UTL_FILE, oprogramowanie SQL*Loader,
tabele zewnętrzne i funkcje tablicowe. Pakiet UTL_FILE zawiera procedury i funkcje odczytu i
zapisu plików. SQL*Loader umożliwia wczytywanie informacji z plików tekstowych o różnym
formacie. Możliwe jest także weryfikowanie, transformowanie i filtrowanie danych przed ich
wczytaniem do bazy. Rozszerzeniem tej funkcjonalności jest możliwość definiowania w bazie
danych tzw. tabel zewnętrznych (ang. external tables), których źródłem danych są pliki tekstowe
systemu operacyjnego. Do tabel takich odwołuje się za pomocą standardowych poleceń SQL se-
lect, a dane są dynamicznie pobierane z plików skojarzonych z tabelą zewnętrzną. W Orac-
le10g za pomocą tabel zewnętrznych można dodatkowo kopiować dane z bazy danych do plików
zewnętrznych. Mechanizm tzw. funkcji tablicowych (ang. table functions) udostępnia dane, które
najpierw są przetwarzane, a następnie przekazywane przez funkcje napisane w języku PL/SQL.
Funkcje te są składowane w bazie danych. Źródłami danych dla tych funkcji mogą być dowolne
obiekty bazy danych (np. zwykłe tabele lub tabele zewnętrzne) lub pliki tekstowe. Dane mogą być
przetwarzane w bardzo złożony sposób, a algorytmy ich przetwarzania implementuje się w języku
proceduralnym. Funkcje tablicowe w zapytaniach wykorzystuje się w sposób identyczny jak tabe-
le.
Kolejny problem stanowi aktualność danych przechowywanych w hurtowni. Ponieważ zawar-
tość źródeł danych jest bezustannie modyfikowana, więc zawartość hurtowni po pewnym czasie
staje się nieaktualna. Wobec tego, w trakcie jej eksploatacji musi ona być odświeżania. Podczas
procesu odświeżania dąży się do tego, aby do hurtowni trafiały jedynie te dane ze źródeł, które
uległy zmianie od czasu poprzedniego odświeżania. Jest to tzw. odświeżanie przyrostowe (ang.
incremental refershing).
Baza danych Oracle9i/10g posiada dwa mechanizmy umożliwiające propagowanie zmian
z bazy produkcyjnej do hurtowni. Pierwszy z nich to wyzwalacze (ang. triggers), czyli procedury
wyzwalane na skutek operacji modyfikowania danych źródłowych. Procedury te mogą informo-
wać moduł monitora lub propagować dane do hurtowni. Projektant hurtowni musi w tym przypad-
ku sam zaimplementować właściwie działające wyzwalacze. Drugi mechanizm wykorzystuje tzw.
migawki (ang. snapshots) zwane również perspektywami zmaterializowanymi (ang. materialized
views).
Perspektywa zmaterializowana jest kopią tabeli lub jej fragmentu pochodzącego z innej bazy
danych. W definicji takiej perspektywy określa się najczęściej: częstotliwość jej odświeżania, spo-
sób odświeżania (przyrostowe, pełne), zbiór danych ze źródła który ma być dostępny w perspek-
Hurtownie danych oparte o Oracle9i/10g – przegląd funkcjonalności
199
tywie. System zarządzania hurtownią danych sam kontroluje proces odświeżania perspektywy.
Odświeżanie przyrostowe perspektyw zmaterializowanych jest możliwe dopiero po utworzeniu w
bazie produkcyjnej tzw. dziennika perspektywy zmaterializowanej (ang. materialized view log) dla
każdej tabeli, z której perspektywa pobiera dane. Zadaniem dziennika jest rejestrowanie zmian
zachodzących w danych źródłowych. Proces odświeżający perspektywy korzysta z informacji
zapisanych właśnie w dziennikach. Odświeżanie przyrostowe z wykorzystaniem perspektyw zma-
terializowanych i ich dzienników jest jednak możliwe jedynie w przypadku, gdy zarówno bazy
źródłowe, jak i sama hurtownia są oparte o Oracle9i/10g (lub wersje wcześniejsze). Perspektywy
zmaterializowane tworzy się albo z wykorzystaniem oprogramowania Enterprise Manager albo z
poziomu SQL.
Przykładowo, poniższe polecenie create materialized view tworzy perspektywę
zmaterializowaną, która jest odświeżana przyrostowo (klauzula refresh fast) i automatycz-
nie co 1 minutę (klauzula start with sysdate+(1/(24*60))) . Źródłem danych dla tej
perspektywy są dane z tabeli Sprzedaż w zdalnej bazie danych (klauzula select).
create materialized view mv_sprzedaz
build immediate
refresh fast
start with sysdate+(1/(24*60))
next sysdate+(1/(24*60*6))
as
select * from sprzedaz@lab92
where data like '%2003';
Odświeżanie przyrostowe powyższej perspektyw będzie możliwe dopiero wtedy, gdy w źró-
dłowej bazie danych dla tabeli Sprzedaż zostanie zdefiniowany tzw. dziennik perspektywy zmate-
rializowanej (ang. materialized view log) rejestrujący wszystkie zmiany zawartości Tabeli sprze-
daż, jak przedstawiono poniżej.
create materialized view log on sprzedaz;
5. Analiza danych
5.1. Funkcjonalność OLAP
Dane przechowywane w hurtowni podlegają zaawansowanym analizom. Dla potrzeb takich
analiz i złożonego przetwarzania danych, Oracle9i/10g udostępnia szereg klauzul i funkcji uła-
twiających konstruowanie zapytań OLAP. W zakresie wyznaczania agregatów rozszerzono język
SQL o klauzule group by cube, group by rollup, group by grouping sets umożliwiające wyliczanie
dodatkowych wartości zagregowanych.
Jako przykład rozważmy zapytanie obliczające sumy sprzedanych produktów w poszczegól-
nych sklepach, oparte o schemat z rysunku 2. W przykładowym poniższym wyniku, pogrubiono te
dodatkowe podsumowania, które zostały wyliczone z wykorzystaniem klauzuli cube.
select pr.prod_nazwa produkt, sk.nazwa,
sum(l_sztuk) sprzedano
from sprzedaz sp, sklepy sk, produkty pr
where sp.sklep_id=sk.sklep_id
and sp.produkt_id=pr.produkt_id
group by cube (pr.prod_nazwa, sk.nazwa);
PRODUKT NAZWA SPRZEDANO
--------- ------- ----------
18
SKLEP1 5
SKLEP2 6
SKLEP3 7
Rioja 12
Rioja SKLEP1 3
Rioja SKLEP2 4
Rioja SKLEP3 5
Bordeaux 6
Bordeaux SKLEP1 2
200
Robert Wrembel
Bordeaux SKLEP2 2
Bordeaux SKLEP3 2
W wersji Oracle10g do polecenia select dodano klauzulę model, która umożliwia za-
gnieżdżenie w zapytaniu kodu napisanego w specjalnie do tego celu dedykowanym języku proce-
duralnym. Możliwe jest m.in. wykorzystanie pętli i zmiennych. Cel stosowania tej klauzuli jest
dwojaki. Po pierwsze wyliczanie wartości hipotetycznych w celach predykcyjnych, np. predykcja
sprzedaży w roku następnym na podstawie lat poprzednich. Po drugie symulowanie w bazie da-
nych arkuszy kalkulacyjnych. Elementarną funkcjonalność klauzuli model zilustrowano poniż-
szymi przykładami. Więcej informacji na jej temat Czytelnik znajdzie w [O10DWG].
Przyjmijmy, że w bazie danych zdefiniowano perspektywę view_sprzedaż opartą o tabele Skle-
py, Produkty, Czas i Sprzedaż (por. rysunek 2). Perspektywa ta udostępnia sumy kwot sprzedaży w
poszczególnych sklepach, poszczególnych produktów, w poszczególnych miesiącach. Poniższe
zapytanie do tej perspektywy wykorzystuje klauzulę model w następujący sposób. Dane relacyj-
ne są transformowane do 2-wymiarowej kostki, której wymiarami są nazwa produktu i nazwa
miesiąca (atrybuty prod_nazwa i nazwa_miesiąca w klauzuli dimension by). Miarę stanowi
atrybut kwota (klauzula measures(kwota)). Klauzula partition by (miasto) umoż-
liwia podzielenie kostki na "podkostki" – po jednej dla każdego miasta. Do wartości miary w każ-
dej "podkostce" jest stosowana reguła specyfikowana w klauzuli rule. W poniższym przykładzie
kwota sprzedaży kosmetyku o nazwie Polo w kwietniu jest hipotetycznie wyliczana jako kwota
sprzedaży tego kosmetyku w marcu powiększona o 20%. W ogólności, w klauzuli model można
definiować wiele reguł.
select miasto, prod_nazwa, nazwa_miesiaca, kwota
from view_sprzedaz
where miasto = 'Kraków'
model return all rows
partition by (miasto)
dimension by (prod_nazwa, nazwa_miesiaca)
measures
(kwota)
rules( kwota['Polo', 'kwiecień'] = kwota['Polo', 'marzec'] * 1.2)
order by prod_nazwa, nazwa_miesiaca;
Przykładowy wynik omawianego zapytania przedstawiono poniżej. Rekord, który został wyli-
czony klauzulą model przedstawiono jako pogrubiony i pochylony. Pozostałe rekordy pochodzą z
bazy danych.
MIASTO PROD_NAZWA NAZWA_MIESIACA KWOTA
---------- ------------- --------------- ----------
Kraków Adidas sport marzec 60
Kraków Bordeaux styczeń 450
Kraków Polo kwiecień 264
Kraków Polo marzec 220
Kraków Polo styczeń 660
Kraków Żubrówka styczeń 54
Kolejny przykład prezentuje możliwość: definiowania zmiennych i sposób odwoływania się do
takich zmiennych oraz możliwość wykorzystania pętli w jednym poleceniu select. Idea poniż-
szego zapytania jest następująca. Klauzula reference definiuje zmienną typu zbiór rekordów
(analogia do zmiennej typu REF CURSOR) będący wynikiem podzapytania umieszczonego w tej
klauzuli. Podzapytanie udostępnia przeliczniki walut. Dla tego zbioru przeliczników określono
wymiar, który stanowi atrybut waluta (dimension by (waluta)) i miarę, którą jest wartość
przelicznika (measures (wspolczynnik)). Wymiary i miara głównej kostki zostały ustalo-
Hurtownie danych oparte o Oracle9i/10g – przegląd funkcjonalności
201
ne w sposób identyczny, jak w omawianym wcześniej zapytaniu. Reguła przelicza kwoty sprzeda-
ży na zadaną walutę. Wybór waluty jest dokonywany poprzez odwołanie się do zmiennej ze wska-
zaniem wartości wymiaru waluta (konw_ref. wspolczynnik ['EUR']).
select miasto, prod_nazwa, nazwa_miesiaca, kwota
from view_sprzedaz
where miasto = 'Kraków'
model
reference konw_ref on ( select waluta, pln from przelicznik)
dimension by (waluta) measures (wspolczynnik)
main
konwersja
partition by (miasto)
dimension by (prod_nazwa, nazwa_miesiaca)
measures
(kwota)
rules(UPSERT
kwota[
FOR prod_nazwa IN (select distinct prod_nazwa from produkty),
FOR nazwa_miesiaca IN (select distinct nazwa_miesiaca from czas)] =
kwota[CV(prod_nazwa), CV(nazwa_miesiaca)]/konw_ref.wspolczynnik['EUR'])
order by miasto, prod_nazwa, nazwa_miesiaca;
Należy zwrócić uwagę, że poszczególne wartości wymiarów są dynamicznie pobierane w pętli
FOR z wyników podzapytań.
Wszystkie omawiane rozszerzenia polecenia select mają na celu umożliwienie implemen-
towania bardzo złożonych analiz w jednym zapytaniu, którego wykonanie będzie mogło zostać
zoptymalizowane przez moduł optymalizatora kosztowego.
Zaawansowane analizy wymagają również stosowania specjalizowanych funkcji analitycznych
obliczających m.in.: rankingi, agregaty kumulacyjne, regresje. W tym zakresie, Oracle9i/10g
oferuje kilkadziesiąt takich funkcji.
5.2. Eksploracja danych
Eksploracja danych (ang. data mining) jest techniką analizy danych w celu odkrywania niejaw-
nych, a interesujących ze względów np. biznesowych powiązań między danymi. Eksploracja da-
nych ma zastosowanie w wielu dziedzinach działalności i przedsiębiorczości, m.in. handlu, marke-
tingu, medycynie, bankowości, ubezpieczeniach. Przykładowo, dzięki eksploracji danych o sprze-
daży produktów w supermarketach można wydobyć informację jakie zbiory produktów są najczę-
ściej kupowane razem. Wykorzystuje się do tego tzw. technikę znajdowania reguł asocjacyjnych
(ang. association rules). Informacja o zbiorze produktów często kupowanych w czasie jednej wizy-
ty w supermarkecie może być przydatna w projektowaniu fizycznego rozmieszczenia towarów na
półkach. Inny przykład może dotyczyć znajdowania grupy klientów, którzy z największym praw-
dopodobieństwem zakupią dany towar, np. w celu skierowania do nich kampanii reklamowej. Do
tego celu wykorzystuje się tzw. technikę klasyfikacji i predykcji (ang. classification and predic-
tion). Inne techniki eksploracji danych obejmują: analizę skupień (ang. clustering), odkrywanie
osobliwości (ang. outlier detection), odkrywanie wzorców sekwencji (ang. sequential pattern mi-
ning).
Baza danych Oracle10g udostępnia narzędzia programistyczne umożliwiające znajdowanie
reguł asocjacyjnych, analizę skupień, klasyfikację i predykcję. Są one dostępne za pomocą inter-
fejsu Java API lub pakietu systemowego DBMS_DATA_MINING, składowanych w bazie danych.
6. Efektywność systemu
Aby systemy relacyjnych baz danych mogły efektywnie przetwarzać zapytania typu OLAP
i zarządzać danymi rzędu terabajtów muszą posiadać nowe własności zwiększające ich efektyw-
ność. Nowymi cechami systemów relacyjnych w zastosowaniach hurtowni danych są m.in.: specy-
202
Robert Wrembel
ficzne techniki indeksowania, optymalizacja zapytań gwiaździstych, zastosowanie perspektyw
zmaterializowanych w procesie przepisywania zapytań, partycjonowanie danych i indeksów, kom-
presja danych, przetwarzanie równoległe.
6.1. Indeksowanie
Podstawowymi strukturami danych wykorzystywanymi do optymalizacji zapytań są indeksy.
Dla bardzo złożonych zapytań typu OLAP, operujących na ogromnej liczbie danych, standardowe
indeksy w postaci B–drzew okazują się nieefektywne ponieważ po pierwsze, nie zapewniają wy-
starczająco szybkiego dostępu do danych, po drugie, ich rozmiary są zbyt duże, przez co wzrastają
koszty ich przetwarzania, przechowywania i utrzymywania. Dla zastosowań OLAP, Oracle9i/10g
udostępnia indeksy bitmapowe i bitmapowe połączeniowe.
6.1.1. Indeks bitmapowy
Ideą indeksów bitmapowych jest wykorzystanie pojedynczych bitów do zapamiętania informa-
cji o tym, że dana wartość atrybutu występuje w określonym rekordzie tabeli. Dla każdej unikalnej
wartości atrybutu jest przechowywana tablica bitów, zwana mapą bitową. Każdy bit mapy odpo-
wiada jednemu rekordowy w tabeli R – bit pierwszy odpowiada pierwszemu rekordowi w tabeli R,
bit drugi – drugiemu rekordowi itp. Dla mapy A=’w’ bit n przyjmuje wartość jeden, jeśli atrybut A
rekordu o numerze n przyjmuje wartość ‘w’. W przeciwnym przypadku bit n przyjmuje wartość
zero. Liczba bitów mapy bitowej odpowiada liczbie rekordów tabeli R. Indeks bitmapowy jest
zbiorem map bitowych dla wszystkich unikalnych wartości danego atrybutu. Indeks tego typu (w
zależności od implementacji) może również posiadać strukturę B–drzewa, w którego liściach za-
miast adresów rekordów są przechowywane mapy bitowe. Przykład indeksu bitmapowego dla
atrybutu kolor tabeli Sprzedaż przedstawiono w tabeli 2.
Sprzedaż
kolor
ROWID
sklep produkt
...
kolor
ROWID tak nie
x00A1 SKLEP1 Omo ... tak x00A1 1 0
x00A2 SKLEP2 Omo ... nie x00A2 0 1
... ... ... ...
...
... ...
...
x00B6 SKLEP1 Omo ... nie x00B6 0 1
x00B7 SKLEP4 Persil ... tak x00B7 1 0
x00B8 SKLEP4 Persil ... nie x00B8 0 1
x00B9 SKLEP3 Dosia ... tak x00B9 1 0
Tabela 1. Przykładowa tabela Sprzedaż
Tabela 2. Indeks bitmapowy dla atrybutu
kolor
Polecenie tworzące taki indeks ma postać przedstawioną poniżej.
create bitmap index kolor_bitmap_indx on sprzedaz(kolor);
Indeksy bitmapowe są bardziej efektywne od indeksów w postaci B–drzewa tylko dla określo-
nej klasy zapytań kierowanych do bazy danych. Są to zapytania wykorzystujące dużą liczbę pre-
dykatów warunkowych z operatorami równości oraz zapytania wykorzystujące funkcję COUNT.
Większa efektywność tych indeksów wynika z:
•
dużej szybkości przetwarzania map bitowych za pomocą operatorów AND, OR i NOT.
•
małego rozmiaru indeksów – indeksy takie zdefiniowane na atrybutach o wąskiej dziedzinie
są znacznie mniejsze od indeksów w postaci B–drzewa.
•
możliwości wykonywania operacji logicznych i funkcji COUNT bezpośrednio na indeksach
bitmapowych (znajdujących się w pamięci operacyjnej), a nie na samych rekordach..
W przypadku stosowania operatora LIKE, indeksy bitmapowe są jednak bezużyteczne.
Hurtownie danych oparte o Oracle9i/10g – przegląd funkcjonalności
203
6.1.2 Bitmapowy indeks połączeniowy
Bitmapowy indeks połączeniowy (ang. join bitmap index) przyspiesza operacje łączenia tabel
powiązanych poprzez klucz podstawowy i obcy, jest więc strukturą zawierającą zmaterializowane
połączenie tabel. Jako przykład rozważmy tego typu indeks zdefiniowany na atrybucie Produk-
ty.kategoria połączenia tabeli Sprzedaż i Produkty. Indeks ten będzie zawierał tyle map bitowych
ile jest różnych kategorii produktów (w tabeli Produkty). Pojedyncza mapa tego indeksu, np. dla
grupy kosmetyki będzie wskazywała na te rekordy w tabeli Sprzedaż, które opisują sprzedaż ko-
smetyków. Polecenie tworzące omawiany indeks przedstawiono poniżej, natomiast poglądowy
rysunek jego struktury przedstawiono na rysunku 6.
create bitmap index sprz_join_bitmap_indx
on sprzedaz(produkty.kategoria)
from sprzedaz, produkty
where sprzedaz.produkt_id=produkty.produkt_id;
Rys.6. Struktura przykładowego bitmapowego indeksu połączeniowego
6.2. Optymalizacja zapytań gwiaździstych
W magazynach danych typowe zapytania łączą centralną tabelę faktów z tabelami wymiarów.
Są to tzw. zapytania gwiaździste (ang. star queries). Jako przykład, rozważmy zapytanie do tabel
z rysunku 2, wyznaczające sumy sprzedaży produktów kategorii kosmetyki w województwach
wielkopolskim i mazowieckim. W celu skrócenia czasu odpowiedzi systemu na to zapytanie, nale-
ży zdefiniować dwa bitmapowe indeksy połączeniowe. Jeden na atrybucie województwo, a drugi
na atrybucie kategoria (omówiony w punkcie 6.1.2). Indeks na atrybucie województwo będzie
zawierał tyle map bitowych ile jest różnych województw w tabeli Sklepy. Pojedyncza mapa tego
indeksu, np. dla Wielkopolski będzie opisywała te sprzedaże (rekordy w tabeli Sprzedaż), które
zrealizowano w Wielkopolsce.
Kosztowy optymalizator zapytań bazy danych Oracle9i/10g zastąpi oryginalne zapytanie
użytkownika zapytaniem alternatywnym, które wykorzysta bitmapowe indeksy połączeniowe,
w następujący sposób. W kroku pierwszym moduł wykonawczy SQL odczyta następujące mapy
bitowe:
•
opisującą sprzedaż produktów kategorii kosmetyki; niech mapa ta nazywa się MB
kosmetyki
;
•
opisującą sprzedaż w województwie wielkopolskim; niech mapa ta nazywa się MB
Wielkopolska
;
•
opisującą sprzedaż w województwie mazowieckim; niech mapa ta nazywa się MB
Mazowsze
;
204
Robert Wrembel
W korku drugim, zostanie wyznaczona wynikowa mapa bitowa (niech będzie oznaczona jako
MB
wynik
) w następujący sposób:
MB
kosmetyki
and (MB
Wielkopolska
or MB
Mazowsze
)
. Bę-
dzie ona wskazywała na sprzedaże kosmetyków zarówno w jednym, jak i drugim województwie.
W kroku trzecim, z wykorzystaniem mapy MB
wynik
zostaną odczytane odpowiednie rekordy tabeli
Sprzedaż.
Optymalizacja zapytań gwiaździstych umożliwia kilku/kilkunastokrotne skrócenie czasu od-
powiedzi systemu.
6.3. Perspektywy zmaterializowane i przepisywanie zapytań
Oprócz replikowania/odświeżania danych, inną bardzo ważną i często stosowaną dziedziną za-
stosowań perspektyw zmaterializowanych jest optymalizacja zapytań analitycznych. Dla tych za-
stosowań perspektywy zmaterializowane służą do przechowywania wyliczonych danych (najczę-
ściej zagregowanych), których wyznaczenie jest czasochłonne. Materializowanie danych ma sens
jeżeli w systemie często pojawiają się zapytania identyczne lub podobne do tego, którego wynik
zmaterializowano. Jeżeli w systemie pojawi się zapytanie, które może zostać wykonane z wyko-
rzystaniem zmaterializowanych perspektyw, zamiast korzystania ze źródłowych tabel i wyznacza-
nia wyników od początku, wówczas kosztowy optymalizator zapytań skonstruuje odpowiednie
zapytanie do tych perspektyw. Jest to tzw. przepisanie zapytania (ang. query rewriting). Proces ten
jest całkowicie niewidoczny dla użytkownika.
Przykładowo, rozważmy poniższą perspektywę zmaterializowaną mv_sprzedaż.
create materialized view mv_sprzedaz
build immediate
refresh force
next sysdate + (1/24)
enable query rewrite
as
select sk.miasto, pr.prod_nazwa, cz.nazwa_miesiaca,
sum(sp.l_sztuk) sprzedano, sum(sp.wartosc) wartosc
from sprzedaz sp, sklepy sk, produkty pr, czas cz
where sp.sklep_id=sk.sklep_id
and sp.produkt_id=pr.produkt_id
and sp.data=cz.data
group by sk.miasto, pr.prod_nazwa, cz.nazwa_miesiaca;
Po poprawnym skonfigurowaniu instancji bazy danych, nadaniu użytkownikowi odpowiednich
uprawnień (por. [Wre00, O10DWG]), zebraniu statystyk dla tabel bazowych perspektywy, zosta-
nie ona wykorzystana do przepisywania zapytań. Przykładowo, wyniki poniższego zapytania zo-
staną wyznaczone w oparciu o perspektywę mv_sprzedaż, jak pokazuje plan wykonania tego zapy-
tania.
select sk.miasto, pr.prod_nazwa, sum(sp.wartosc) wartosc
from sprzedaz sp, sklepy sk, produkty pr, czas cz
where sp.sklep_id=sk.sklep_id
and sp.produkt_id=pr.produkt_id
and sp.data=cz.data
and sk.miasto='Poznań'
having sum(sp.wartosc)>190
group by sk.miasto, pr.prod_nazwa, cz.nazwa_miesiaca;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=37)
1 0
TABLE ACCESS (FULL) OF 'MV_SPRZEDAZ' (Cost=2 Card=1 Bytes=37)
Hurtownie danych oparte o Oracle9i/10g – przegląd funkcjonalności
205
Praktycznie, w każdej hurtowni danych oprócz tabel zawierających dane elementarne znajdują
się perspektywy zmaterializowane, przechowujące dane zbiorcze/zagregowane na różnych pozio-
mach. Projektując hurtownię danych, należy zapewnić, aby zbiór tych perspektyw był optymalny,
tzn. każda z nich była jak najczęściej wykorzystywana w procesie przepisywania zapytań. Wyzna-
czenie takiego zbioru perspektyw jest trudne. W tym zakresie baza danych Oracle10g oferuje
narzędzie o nazwie Access Advisor dostępne w postaci pakietu DBMS_ADVISOR. Wykorzysta-
nie jego jest możliwe bezpośrednio z poziomu środowiska SQL*Plus lub z poziomu oprogramo-
wania graficznego Enterprise Manager. Access Advisor umożliwia m.in. określenie: (1) wła-
ściwego zbioru zmaterializowanych perspektyw przyspieszających wykonywanie wskazanego
zbioru zapytań, (2) właściwego zbioru indeksów typu B–drzewo i bitmapowych przyspieszających
wykonanie zapytań ze wskazanego zbioru.
6.4. Partycjonowanie
W hurtowni danych największe rozmiary osiągają tabele faktów. Przeszukiwanie dużych tabel
jest czasochłonne, nawet z wykorzystaniem indeksów. Często decydenci są zainteresowani analizą
tylko podzbioru rekordów tabeli, np. ilości sprzedanych produktów z grupy kosmetyki w Wielko-
polsce. Dla takiego zapytania, podział dużej tabeli Sprzedaż na mniejsze, np. ze względu na woje-
wództwa, w których dokonano sprzedaży znacznie skróciłoby czas dostępu do wybranego pod-
zbioru danych.
Fizyczny podział tabeli (lub indeksu) na części jest nazywany partycjonowaniem (ang. partitio-
ning). Każda z części nazywa się partycją (ang. partition). Często jest ona fizycznie umieszczana
na osobnym dysku, znajdującym się w tym samym lub wielu węzłach (komputerach) sieci. Roz-
mieszczanie danych w poszczególnych partycjach jest realizowane na podstawie wartości wskaza-
nego atrybutu tabeli (indeksu), tzw. atrybutu partycjonującego. Fizycznie podzielona tabela (lub
indeks) stanowi logiczną całość z punktu widzenia użytkownika. Podział dużej tabeli lub indeksu
na mniejsze fragmenty zapewnia, że:
• bardzo kosztowne operacje wejścia/wyjścia, tj. dostępu do dysków mogą być wykony-
wane równolegle;
• jest równoważone obciążenie dysków;
• polecenia SQL adresujące różne partycje mogą być wykonywane równolegle;
• polecenia SQL mogą adresować konkretną partycję eliminując w ten sposób koniecz-
ność przeszukiwania całej tabeli lub indeksu;
• wzrasta bezpieczeństwo danych w przypadku awarii sprzętu ponieważ awaria np. jed-
nego dysku uniemożliwia dostęp tylko do partycji na tym dysku, natomiast partycje
znajdujące się na nieuszkodzonych dyskach są nadal dostępne;
• wzrasta szybkość odtwarzania danych po awarii ponieważ odtwarzaniu podlegają tylko
uszkodzone partycje, a nie cała tabela.
Głównym celem partycjonowania indeksu jest zwiększenie stopnia współbieżności transakcji i
minimalizacja rywalizacji transakcji, poprzez rozproszenie operacji wejścia/wyjścia wykonywa-
nych na indeksie.
Baza danych Oracle9i/10g wspiera trzy następujące techniki partycjonowania rekordów tabeli:
partycjonowanie zakresowe, listowe i haszowe. W partycjonowaniu
zakresowym (ang. range parti-
tioning) dla każdej partycji określa się zakres wartości atrybutu partycjonującego. Do danej party-
cji trafiają więc rekordy ze ściśle określonego dla tej partycji zakresu określonego wartością atry-
butu partycjonującego. W partycjonowaniu
listowym (ang. list partitioning) dla każdej partycji
określa się zbiór wartości atrybutu partycjonującego. Natomiast w partycjonowaniu haszowym
(ang. hash partitioning) partycja, w której jest umieszczany rekord jest wyznaczana na podstawie
dobieranej przez system funkcji haszowej.
Jako przykład partycjonowania zakresowego bazującego na wartości rozważmy poniższe pole-
cenie create table tworzące tabelę Klienci z podziałem na trzy partycje. Kryterium podziału
206
Robert Wrembel
jest w tym przypadku wartość atrybutu kod_miasta. Klienci z miast o kodach rozpoczynających
się od liter 'A' do 'C' (mniejsze od 'D') zostaną umieszczeni w partycji o nazwie p_klienci_C, klien-
ci z miast o nazwach rozpoczynających się od liter 'D' do 'F' (mniejsze od 'G') zostaną umieszczeni
w partycji o nazwie p_klienci_F, natomiast wszyscy klienci z miast z zakresu 'G' do 'M' zostaną
umieszczeni w partycji p_klienci_M.
create table klienci
(klient_id number(10),
imie varchar2(25),
nazwisko varchar2(25),
kod_miasta varchar2(6))
partition by range(kod_miasta)
(partition p_klienci_C values less than ('D'),
partition p_klienci_F values less than ('G'),
partition p_klienci_M values less than ('N'));
W kolejnym przykładzie klienci_part_hash jest dzielona na pięć partycji umieszczonych
w jawnie wyspecyfikowanych przestrzeniach tabel dane1, dane2 i dane3. Ponieważ liczba partycji
jest większa niż liczba przestrzeni tabel, więc partycje są umieszczane w kolejnych przestrzeniach
tabel za pomocą algorytmu round-robin. Nazwy partycji są nadawane przez system.
create table klienci_part_hash
(klient_id number(10),
imie varchar2(25),
nazwisko varchar2(25),
kod_miasta varchar2(6))
partition by hash(kod_miasta)
partitions 5
store in (dane1, dane2, dane3);
Oracle9i/10g umożliwia tworzenie indeksów partycjonowanych zarówno dla tabel partycjo-
nowanych, jak i niepartycjonowanych. Tabela partycjonowana może natomiast posiadać zarówno
indeks partycjonowany, jak i niepartycjonowany. Jeżeli sposób partycjonowania indeksu jest iden-
tyczny ze sposobem partycjonowania indeksowanej tabeli, to indeks taki nazywamy lokalnym
(ang. local). Identyczność partycjonowania oznacza zgodność atrybutów partycjonujących i zakre-
sów partycji. Natomiast jeżeli sposób partycjonowania indeksu jest inny niż sposób partycjonowa-
nia tabeli indeksowanej (różne atrybuty partycjonujące tabeli i indeksu, różne zakresy partycji
tabeli i indeksu), to indeks taki nazywamy globalnym (ang. global). Ogólna struktura indeksu lo-
kalnego i globalnego została zilustrowana rysunkiem 7.
Rys. 7. Ogólna struktura indeksu lokalnego i globalnego
Poniższe przykładowe polecenie tworzy indeks lokalny na atrybucie partycjonującym
kod_miasta, tabeli klienci partycjonowanej zakresowo (omówionej wcześniej). Zastosowanie sło-
Hurtownie danych oparte o Oracle9i/10g – przegląd funkcjonalności
207
wa kluczowego local wskazuje, że ma być tworzony indeks lokalny. W rezultacie, zostaną
utworzone trzy odrębne indeksy - po jednym dla każdej z trzech partycji tabeli klienci. Zakresy
wartości indeksu dla danej partycji będą identyczne z zakresem tej partycji tabeli.
create index klienci2_local_indx
on klienci2(kod_miasta)
local
(partition p_klienci_C_indx tablespace dane1_indx,
partition p_klienci_F_indx tablespace dane2_indx,
partition p_klienci_M_indx tablespace dane3_indx);
6.5. Kompresja
W celu zmniejszenia rozmiarów danych przechowywanych w magazynie stosuje się ich kom-
presję. W Oracle kompresji mogą podlegać następujące obiekty: tabele, perspektywy zmateriali-
zowane, indeksy, tabele partycjonowane lub pojedyncze partycje. Technika kompresji danych
w blokach została zilustrowana rysunkiem 8. W bloku nieskompresowanym powtarzające się war-
tości atrybutów są przechowywane wielokrotnie. Natomiast w bloku skompresowanym, powtarza-
jące się wartości są umieszczane na początku bloku, w przeznaczonym do tego celu obszarze, na-
tomiast w rekordach są umieszczane wskaźniki do odpowiedniego obszaru wspólnego.
Rys. 8. Przykład kompresji bloków danych
Kompresję danych w blokach wymusza się na poziomie tabeli (polecenia create table,
create table as select) lub całej przestrzeni tabel. Przykładowo, poniższe polecenia
tworzą odpowiednio tabelę skompresowaną i przestrzeń tabel, zapewniającą kompresję tabel
w niej umieszczanych.
create table sprzedaz
(data date not null, produkt_id number(6) not null,
sklep_id number(6) not null, l_sztuk number(5) not null,
wartosc number(9,2) not null,
constraint sprz_pk primary key (data, produkt_id, sklep_id))
compress;
create tablespace dane 1
datafile 'C:\ORA91\ORADATA\DES4072\DANE1.DBF' size 10M autoextend on next 2M
default
compress
storage(initial 128K next 128K pctincrease 0 minextents 1);
Oprócz kompresji danych w blokach stosuje się kompresję indeksów B–drzewo i bitmapo-
wych. Kompresja indeksu B–drzewo dotyczy jego liści. W liściu nieskopmpresowanym są prze-
chowywane m.in. pary: wartość indeksowana W – adres rekordu posiadającego wartość W. Jeżeli
indeks założono na atrybucie, którego wartość nie jest unikalna, wówczas wartość indeksowana W,
208
Robert Wrembel
pojawia się w liściach wielokrotnie – tyle razy ile jest rekordów z tą wartością. W przypadku liści
skompresowanych jest budowana lista zawierająca: wartość indeksowaną W i adresy wszystkich
rekordów posiadających wartość W. W ten sposób wartość indeksowana pojawia się w liściu jeden
raz.
Indeksy bitmapowe ulegają kompresji wtedy, gdy liczba zer w mapach bitowych staje się zbyt
duża w porównaniu do liczby jedynek. Jak pokazują eksperymenty (por. [Wre97]), kompresja taka
znacznie zmniejsza rozmiar indeksu bitmapowego. Kompresja map bitowych jest wykonywana
automatycznie przez Oracle i jest niewidoczna dla użytkownika. Kompresję indeksów wymusza
się za pomocą słowa kluczowego compress.
Warto wspomnieć, że kompresji podlegają dane wczytywane do tabeli wyłącznie za pomocą:
(1) polecenia create table as select, (2) równoległego insert ścieżką bezpośrednią
(polecenie insert /* +append*/), (3) programu SQL*Loader z wykorzystaniem ścieżki
bezpośredniej [O10Con]. Dodatkowo, przy przenoszeniu danych do przestrzeni tabel z kompresją
bądź do skompresowanej partycji (polecenie alter table move), dane te są kompresowane.
6.6. Przetwarzanie równoległe
Przetwarzanie równoległe (ang. parallel processing) polega na rozbiciu złożonych operacji na
mniejsze, które następnie są wykonywane równolegle, np. na wielu procesorach lub komputerach.
W efekcie, czas wykonania całej operacji jest krótszy. W przypadku hurtowni danych, najczęściej
równolegle przetwarza się zapytania, buduje tablice i indeksy.
Przykładowo, poniższe polecenie odczytuje zawartość tabeli Sprzedaż z wykorzystaniem 5
równoczesnych procesów.
select /*+ PARALLEL(sp, 5) */ sklep_id, sum(ilosc)
from sprzedaz sp
group by sklep_id;
Kolejne polecenie tworzy tabelę sklepy_kopia z wykorzystaniem 3 równocześnie działających
procesów.
create table sklepy_kopia parallel 3 as select * from sklepy;
Przetwarzanie równoległe można również stosować w czasie wczytywania danych z plików
zewnętrznych za pomocą SQL*Loader, tabel zewnętrznych, datapump export/import oraz w cza-
sie archiwizowania i odtwarzania bazy danych po awarii.
7. Podsumowanie
W zakresie magazynów danych firma Oracle Corp. dostarcza oprogramowania niezbędnego
w każdym etapie projektowania i wykorzystywania takich systemów. Główne aspekty technolo-
giczne obejmują: (1) projektowanie magazynu danych, (2) jego zasilanie danymi, (3) wykorzysta-
nie jego zawartości w przetwarzaniu analitycznym, (4) efektywność dostępu do danych. Poniższa
tabela koreluje ww. aspekty technologiczne z narzędziami programistycznymi/mechanizmami
udostępnianymi przez Oracle.
Funkcjonalność Cechy
Narzędzia
programistyczne/mechanizmy
Projektowanie
ROLAP
SQL, Designer, Enterprise Manager
MOLAP
SQL, Enterprise Manager, Analytic Worskpace Manager,
pakiety: DBMS_AWM, DBMS_AW, OLAP_TABLE,
DBMS_AW_UTITLITIES, rodzina CWM2
Zasilanie (ETL)
Hurtownie danych oparte o Oracle9i/10g – przegląd funkcjonalności
209
dostęp do źródeł nie-
bazodanowych
SQL*Loader, pakiet UTL_FILE, tabele zewnętrzne,
funkcje tablicowe
dostęp do źródeł bazodano-
wych innych producentów
Oracle Transparent Gateways do: IBM, Sybase, Microso-
ft, NCR, sterowniki ODBC/JDBC, wielotabelowy insert,
polecenie merge, insert ścieżką bezpośrednią
dostęp do innych baz da-
nych Oracle
perspektywy zmaterializowane, eksport/import, data-
pump eksport/import, wielotabelowy insert, polecenie
merge, insert ścieżką bezpośrednią
Analiza danych
Funkcje analityczne SQL
kilkadziesiąt specjalizowanych funkcji SQL, rozszerze-
nie polecenia select o klauzule: group by cube, group by
rollup, group by grouping sets, case, with i model
Funkcje eksploracyjne
interfejs PL/SQL - pakiet DBMS_DATA_MINING i
Java
Aplikacje graficzne
Discoverer, pakiet Business Intelligence Beans, JDevelo-
per, Reports9i/10g, Sales Analyzer, Financial Analyzer
Efektywność systemu
Indeksowanie
indeksy bitmapowe i bitmapowe połączeniowe
Optymalizacja zapytań
kosztowy optymalizator zapytań - optymalizowanie za-
pytań gwiaździstych
Perspektywy
zmaterializo-
wane
kosztowy optymalizator zapytań - przepisywanie zapytań
Partycjonowanie
SQL, Enterprise Manager - partycjonowanie tabel i in-
deksów
Kompresja
SQL, Enterprise Manager - kompresja tabel, przestrzeni
tabel i indeksów
Przetwarzanie równoległe
SQL, Enterprise Manager
Bibliografia
[HHL03]
Hobbs L., Hillson S., Lawande S.: Oracle 9iR2 Data Warehousing. Digital Press, 2003,
ISBN 1-55558-287-7
[O10Con]
Oracle Database. Concepts. 10g Release 1
[O10DWG] Oracle Database. Data Warehousing Guide. 10g Release 1
[O10OLAP] Oracle OLAP. Application Developer's Guide. 10g Release 1
[olap]
http://www.oracle.com/olap/
[Tom]
http://asktom.oracle.com/
[Wre97]
Wrembel R.: Nowe struktury indeksów dla magazynów danych. Materiały III Konferencji
Użytkowników i Developerów Oracle – PLOUG’97, Zakopane, 1997
[Wre00]
Wrembel R.: Perspektywy (views) w systemach baz danych: aktualny stan technologii. Ma-
teriały VI Konferencji Użytkowników i Developerów Oracle – PLOUG'2000, Zakopane, 2000