Microsoft Word - 22 - wrembel2.doc

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.

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

, ZD

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))

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

--------- ------- ----------

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

wynik

) w następujący sposób:

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

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