11-Perspektywy.pdf

(258 KB) Pobierz

Perspektywy (Views)

Projektowanie i Zarządzanie Bazami Danych

09/05

Relacyjna baza danych udostępnia programiście poziom

abstrakcji który oddziela go od fizycznego świata

komputera. Nie wiemy gdzie na dysku RDBMS umieszcza

wiersze tabeli. Wiadomo, że wiersz może być podzielony

i rozrzucony na różnych dyskach. RDBMS pozwala na

dodanie wiersza do tabeli o milionach wierszy.

Nie jest jednak do końca jasne jak to zostanie

przeprowadzone – czy będzie ona jakoś dodana obok

uprzednio zdefiniowanych kolumn, czy też zostanie

zarezerwowany nowy blok na dysku w którym będzie

przechowywana informacja nowej kolumny? Nie wiemy i nie

powinno to być dla nas istotne.

Perspektywy - przykład

Ta informacja już istnieje ale jest rozmieszczona w czterech rożnych

tabelach. Bazując na informacji z części dotyczącej zapytań możemy

zapisać:

select u.user_id,

u.email,

count(ucm.page_id) as n_pages,

count(bb.msg_id) as n_msgs,

count(c.comment_id) as n_comments

from users u, user_content_map ucm, bboard bb, comments c

where u.user_id = ucm.user_id(+)

and u.user_id = bb.user_id(+)

and u.user_id = c.user_id(+)

group by u.user_id, u.email

order by upper(email)

Perspektywa jest w zasadzie budową jeszcze wyższego poziomu

abstrakcji.

Zakładamy, że pracownik działu marketingu oczekuje tabeli

zawierającej następującą informację:

•

user_id

adres email

liczba obejrzanych statycznych stron

liczba informacji przesłanych na bboard

liczba dodanych komentarzy

Perspektywy - przykład

Dodatkowe

wymagania to:

dane codziennie uzupełnienie o

najświeższe informacje,

dostępne na bieżąco,

dane

niezależne od ewentualnych zmian w modelu

danych.

create or replace view janes_marketing_view

select u.user_id,

u.email,

count(ucm.page_id) as n_pages,

count(bb.msg_id) as n_msgs,

count(c.comment_id) as n_comments

from users u, user_content_map ucm, bboard bb, comments c

where u.user_id = ucm.user_id(+)

and u.user_id = bb.user_id(+)

and u.user_id = c.user_id(+)

group by u.user_id, u.email

order by upper(u.email)

Perspektywy - przykład

Dla osoby z działu marketingu to

będzie się zachowywało jak tabela:

select * from janes_marketing_view;

Informacja nt.

źródła

została

ukryta.

Zmiany w strukturze bazy chociażby

polegające na rozrzuceniu informacji w 6 tabelach pociągną za sobą jedynie drobną

modyfikację struktury istniejącej perspektywy.

Zabezpieczanie prywatności przy użyciu

perspektyw

Często celem użycia perspektyw jest zabezpieczenie poufnych danych.

Przykładem może być szpital i dane dotyczące pacjentów. W tym przypadku

model danych może być następujący:

create table patients

patient_id

patient_name

hiv_positive_p

insurance_p

...

);

(

integer primary key,

varchar(100),

char(1),

Zabezpieczanie prywatności – c.d.

Perspektywa od strony personelu lekarskiego, nie musi być zainteresowana

ubezpieczeniem pacjenta:

create view patients_clinical

select patient_id, patient_name, hiv_positive_p from patients;

Dla personelu odpowiedzialnego za finanse – ubezpieczenie pacjenta to

podstawa, a to czy jest on HIV pozytywny, nie ma lub nie powinno mieć

wpływu na ich pracę:

create view patients_accounting

select patient_id, patient_name, insurance_p from patients;

Zabezpieczanie prywatności – c.d.

Zabezpieczanie własnego oprogramowania

Zakładamy, że w naszym systemie

zamówienia są zapisywane w tabeli

niezależnie czy

zostały

odrzucone

w czasie weryfikacji karty

kredytowej, czy też zostały

odesłane

przez użytkownika lub też

sprzedawcę. To tak działa przy przetwarzaniu transakcji, jednak jeśli

będziemy płacić podatki za nie zrealizowane zamówienia,

doprowadzimy się do ruiny. Tak

więc księgowość powinna oglądać

tylko to co zakończyło się sukcesem:

create or replace view sh_orders_reportable

select * from sh_orders

where order_state not in

('confirmed','failed_authorization','void');

Każda pracownik w szpitalu ma unikalne ID. Prawa dostępu są

przypisywane perspektywom lub tabelom dla wybranych

użytkowników lub ich grup.

Dla aplikacji Web te prawa nie są, aż tak przydatne gdyż to serwer

Web komunikuje się z DB, a nie komputer użytkownika. Tak więc

Web serwer musi wiedzieć kto próbuje pobrać informacje i co

można pokazać jako odpowiedź.

Zabezpieczanie własnego oprogramowania

Należy zauważyć, że poprzedni przykład służył

do pominięcia

niechcianych kolumn

ten zaś do ukrycia

niepotrzebnych

(w tym

miejscu)

wierszy.

Jeśli

dodamy inne stany

zamówień to wystarczy

utrzymywać

perspektywę jako aktualną

– tak więc programy przygotowujące

raporty nie muszą być zmieniane.

Zauważ, że definicja jest przeprowadzana za pomocą "create or replace

view" zamiast "create view.

Jeśli

używałeś select * aby zdefiniować perspektywę

i często

zmieniasz

przypisane do niej tabele

powinieneś przedefiniować również

perspektywę,

bo w przeciwnym przypadku

nie będzie one zawierać

nowych

– dodanych

kolumn.

To nie jest błąd systemu bo zostało to

opisane w dokumentacji.

Views-on-the-fly i złączenia zewnętrzne

Wracamy do przykładu złączenia zewnętrznego:

select users.user_id, users.email, classified_ads.posted

from users, classified_ads

where users.user_id = classified_ads.user_id(+)

order by users.email, posted;

...

USER_ID

----------

52790

37461

52791

47177

37296

47178

36985

34283

52792

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

dbrager@mindspring.com

dbraun@scdt.intel.com

dbrenner@flash.net

dbronz@free.polbox.pl

dbrouse@enter.net

dbrown@cyberhighway.net

dbrown@uniden.com

dbs117@amaze.net

dbsikorski@yahoo.com

Znak plus po

classified_ads.user_id

POSTED

----------

mówi

aby dodawać wiersze z

pierwszej tabeli jeśli warunek

złączenia nie ma pary z prawej

strony.

2008-03-05

2008-03-10

Views-on-the-fly i złączenia zewnętrzne

Załóżmy, że wynik jest długi i chcemy tylko tych których email zaczyna się od

"db". Zmieniamy warunek WHERE:

select users.user_id, users.email, classified_ads.posted

from users, classified_ads

where users.user_id = classified_ads.user_id(+)

and users.email like 'db%'

order by users.email, posted;

USER_ID

----------

71668

112295

137640

35102

59279

95190

17474

248220

40134

144420

15020

...

Views-on-the-fly i złączenia zewnętrzne

Teraz

chcemy tych którzy byli aktywni od

1 stycznia 2009. Nieudana próba może

wyglądać następująco: (WHERE – warunek nakładamy na classified_ads)

select users.user_id, users.email, classified_ads.posted

from users, classified_ads

Tu złącznie zewnętrzne nie ma

where users.user_id = classified_ads.user_id(+)

sensu bo

wygeneruje dane

and users.email like 'db%'

and classified_ads.posted > ‘2009-01-01'

które nie będą zawierać

–

„par

order by users.email, posted;

bez pary”.

Powodem jest

USER_ID

----------

35102

40134

16979

235920

258161

39921

POSTED

classified_ads.posted > „2009-01-01„

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

db44@aol.com

2009-12-23

Ten

warunek przyjmuje

db@spindelvision.com

2009-02-04

wartość NULL

wszędzie tam

dbdors@ev1.net

2010-10-03

gdzie nie ma pary.

dbdors@ev1.net

2010-10-26

dbendo@mindspring.com

2010-08-03

dbouchar@bell.mma.edu

2010-10-26

dbp@agora.rdrop.com

2009-06-03

dbp@agora.rdrop.com

2009-11-05

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

db-designs@emeraldnet.net

db1@sisna.com

db25@umail.umd.edu

db44@aol.com

db4rs@aol.com

db@astro.com.au

db@hotmail.com

db@indianhospitality.com

db@spindelvision.com

db_chang@yahoo.com

dbaaru@mindspring.com

POSTED

----------

2009-12-23

2009-02-04

8 rows selected.

Views-on-the-fly i złączenia zewnętrzne

Jakie jest rozwiązanie? "View on the fly" – “Perspektywa w locie”. Czyli tworzymy

złączenie zewnętrzne users z perspektywą classified_ads spełniającą nasz warunek

limitu daty:

select users.user_id, users.email, ad_view.posted

from

users,

(select *

Zauważ, że nasze "view on the fly" zostało

from classified_ads

nazwane na czas złączenia ad_view

where posted > ‘2009-01-01') ad_view

where users.user_id = ad_view.user_id(+)

and users.email like 'db%'

order by users.email, ad_view.posted;

USER_ID EMAIL

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

71668 db-designs@emeraldnet.net

112295 db1@sisna.com

35102 db44@aol.com

95190 db@astro.com.au

17474 db@hotmail.com

248220 db@indianhospitality.com

40134 db@spindelvision.com

...

174 rows selected.

POSTED

----------

Jak działa perspektywa

Z punktu widzenia programisty nie powinno być to istotne.

Jednakże warto wiedzieć, że:

RDBMS zachowuje definicję perspektywy, a nie oddzielnie przynależące

do niej dane.

Przeglądanie perspektywy

nie wpływa na położenie danych. Jest

elementem upraszczającym i zabezpieczającym dostęp,

a nie

czyniącym dostęp bardziej efektywnym.

2009-12-23

2009-02-04

Zmaterializowane perspektywy

Od wprowadzenie Oracle 8.1.5, w marcu 1999,

można używać

tzw.

zmaterializowanych perspektyw.

Z punktu widzenia użytkownika

zachowują się podobnie. Z

punktu widzenia systemu tworzy się i

fizycznie przechowuje

związane z nimi

dane.

Tak więc mogą być

bardziej złożone (JOIN, GROUP BY z funkcjami agregującymi).

Dla

zwykłej perspektywy

operacja byłyby przeprowadzane

zawsze gdy

pojawiłoby się

zapytanie.

Zmaterializowane perspektywy zajmują przestrzeń dyskową

bo dane

będące wynikiem takiego zapytania są przechowywane, jednak należy

pamiętać, że

mogą nie zawierać bieżących danych.

Czyli

wynik

zapytania

zależy od twego

kiedy dane zostały utworzone lub

odświeżone.

Oracle pozwala na zdefiniowanie czasu odświeżania

informacji.

Zmaterializowane perspektywy - przykład

Działa podobnie jak CREATE SNAPSHOT jednak stosując opcję ENABLE QUERY

REWRITE

autoryzujemy SQL do użycia i przeszukiwania zmaterializowanych

perspektyw

gdy zadajemy zapytania o złożonych złączeniach lub kosztownych

funkcjach agregujących. Rozważmy przykład:

select

to_char(registration_date,'YYYYMM') as sort_key,

rtrim(to_char(registration_date,'Month')) as pretty_month,

to_char(registration_date,'YYYY') as pretty_year,

count(*) as n_new

from users

SORT_K PRETTY_MO

PRET

group by

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

----

to_char(registration_date,'YYYYMM'),

200805 May

2008

to_char(registration_date,'Month'),

200806 June

2008

to_char(registration_date,'YYYY')

200807 July

2008

order by 1;

200808 August

2008

200809

200810

200811

200812

200901

200902

September

October

November

December

January

February

2008

2009

N_NEW

----------

898

806

972

849

1023

1089

1005

1059

1488

2148

Zmaterializowane perspektywy - przykład

Dla każdego miesiąca sprawdzamy ilu użytkowników zarejestrowało się w

naszym systemie. Aby zrealizować to zapytanie system musi wielokrotnie

przechodzić przez tabele users. Jeśli jest ona duża, a odpowiedź ma być

natychmiast można zastosować:

create materialized view users_by_month

enable query rewrite

refresh complete

start with 2009-03-28

next sysdate + 1

select

to_char(registration_date,'YYYYMM') as sort_key,

rtrim(to_char(registration_date,'Month')) as pretty_month,

to_char(registration_date,'YYYY') as pretty_year,

count(*) as n_new

from users

group by

to_char(registration_date,'YYYYMM'),

to_char(registration_date,'Month'),

to_char(registration_date,'YYYY')

order by 1

Zmaterializowane perspektywy - przykład

Oracle zbuduje perspektywę po północy 2009-03-28. Perspektywa będzie odświeżana co 24

godziny. ponieważ enable użyliśmy query rewrite, Oracle będzie pobierał dane z

perspektywy również gdy zapytanie nie będzie się do niej bezpośrednio odnosić.

Przykładowo:

select count(*)

from users

where rtrim(to_char(registration_date,'Month')) = 'January'

and to_char(registration_date,'YYYY') = ‘2009'

Oracle zignoruje tablicę users i wydobędzie dane z users_by_month. To da ten sam wynik z

mniejszym nakładem obliczeniowym. Jeśli założymy, że bieżącym miesiącem jest

marzec 2009, to zapytanie

select count(*)

from users

where rtrim(to_char(registration_date,'Month')) = 'March'

and to_char(registration_date,'YYYY') = ‘2009'

również trafi do naszej zmaterializowanej perspektywy, a nie do tabeli users tak więc

pominie tych którzy zarejestrowali się po północy.

11-Perspektywy.pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: