[ Pobierz całość w formacie PDF ]

występowania częstych naruszeń ograniczeń w bazie będzie w takim
przypadku stopniowa degradacja jej wydajności.
Przyjrzyjmy się przykładowi opartemu na bazie Oracle. Załóżmy, że
pracujemy nad integracją systemów informatycznych dwóch połączonych firm.
Adres e-mail został ustandaryzowany w postaci wzorca
i ma zawierać co najwyżej dwanaście znaków, wszystkie spacje i znaki
specjalne są zastępowane znakami podkreślenia8.
8
Przykład nie uwzględnia obsługi polskich znaków diakrytycznych, niedozwolonych
w adresach e-mail  przyp.red.
82 ROZDZIAA
DRUGI
Załóżmy, że nową tabelę pracowników należy wypełnić trzema tysiącami
wierszy z tabeli employees_old. Chcemy też, żeby każdy pracownik
posiadał unikalny adres e-mail. Z tego powodu musimy zastosować
określoną zasadę nazewnictwa: Jan Kowalski będzie miał e-mail o postaci
jkowalski, a Józef Kowalski (żadnego pokrewieństwa) jkowalski2 itd.
W naszych danych testowych znajdziemy trzydzieści trzy potencjalne
pozycje konfliktowe, co przy próbie ładowania danych da następujący efekt:
SQL> insert into employees(emp_num, emp_name,
emp_firstname, emp_email)
2 select emp_num,
3 emp_name,
4 emp_firstname,
5 substr(substr(EMP_FIRSTNAME, 1, 1)
6 ||translate(EMP_NAME, ' ''', '_'), 1, 12)
7 from employees_old;
insert into employees(emp_num, emp_name, emp_firstname, emp_email)
*
ERROR at line 1:
ORA-0000l: unique constraint (EMP_EMAIL_UQ) violated
Elapsed: 00:00:00.85
Trzydzieści trzy duplikaty ze zbioru trzech tysięcy to trochę powyżej 1%,
być może zatem warto byłoby obsłużyć te 99%, a elementy problemowe
obsłużyć z użyciem wyjątków? W końcu 1% danych nie powinien
powodować znacznego obciążenia bazy w wyniku procedury obsługi
wyjątków. Poniżej kod realizujący ten optymistyczny scenariusz:
SQL> declare
2 v_counter varchar2(l2);
3 b_ok boolean;
4 n_counter number;
5 cursor c is select emp_num,
6 emp_name,
7 emp_firstname
8 from employees_old;
9 begin
10 for rec in c
11 loop
12 begin
13 insert into employees(emp_num, emp_name,
14 emp_firstname, emp_email)
PROWADZENIE WOJNY 83
15 values (rec.emp_num,
16 rec.emp_name,
17 rec.emp_firstname,
18 substr(substr(rec.emp_firstname, 1, 1)
19 ||translate(rec.emp_name, ' ''', ' '), 1, 12));
20 exception
21 when dup_val_on_index then
22 b_ok := FALSE;
23 n_counter := 1;
24 begin
25 v counter := ltrim(to_char(n_counter));
26 insert into employees(emp_num, emp_name,
27 emp_firstname, emp_email)
28 values (rec.emp_num,
29 rec.emp_name,
30 rec.emp_firstname,
31 substr(substr(rec.emp_firstname, 1, 1)
32 ||translate(rec.emp_name, ' ''', '__'), 1,
33 12 - length(v_counter)) || v_counter);
34 b_ok : = TRUE;
35 exception
36 when dup_val_on_index then
37 n_counter := n_counter + 1;
38 end;
39 end;
40 end loop;
41 end;
40 /
PL/SOL procedure successfully completed.
Elapsed: 00:00:18.41
Jaki jest jednak rzeczywisty koszt obsługi wyjątków? Gdyby ten sam test
przeprowadzić na danych pozbawionych duplikatów, okaże się, że koszt
rzeczywistej obsługi wyjątków (ich wystąpień) jest pomijalny. Procedura
wywołana na danych z duplikatami działa około osiemnaście sekund,
podobnie jak na danych bez duplikatów. Jednak gdy wykonamy ten test
(dane bez duplikatów) na naszej oryginalnej procedurze nieobsługującej
wyjątków (insert...select), zauważymy, że wykona się znacznie szybciej
od pętli. Przełączenie się w tryb  wiersz po wierszu powoduje około
50-procentowy narzut czasu przetwarzania. Czy w takim razie możliwe
jest uniknięcie tego trybu? To kwestia tego, czy zdecydujemy się na rezygnację
z mechanizmu obsługi wyjątków, który to właśnie zmusił nas do obsługi
danych w trybie wierszowym.
84 ROZDZIAA
DRUGI
Innym sposobem mogłoby być zidentyfikowanie wierszy powodujących
powstanie duplikatów i uzupełnienie w nich adresów e-mail kolejnymi
liczbami.
A
atwo określić liczbę problematycznych wierszy, wystarczy odpowiednio
je zgrupować w zapytaniu SQL. Jednakże uzupełnienie o unikalne liczby
może być trudne bez zastosowania funkcji analitycznych dostępnych
w niektórych zaawansowanych systemach baz danych. Określenie  funkcje
analityczne pochodzi z nomenklatury Oracle. W DB2 funkcje te znane sÄ…
jako funkcje OLAP (ang. online analytical processing), w Microsoft SQL
Server jako funkcje rankingu (ang. ranking functions). Warto przyjrzeć
się bliżej rozwiązaniom tego typu z punktu widzenia czystego SQL-a.
Każdy adres e-mail może mieć dopisany unikalny numer przy wykorzystaniu
do tego rankingu według wieku pracownika. Numer 1 otrzyma najstarszy
pracownik w danej grupie duplikatów, numer 2 kolejny pod względem
wieku z tej grupy itd. UmieszczajÄ…c tÄ™ liczbÄ™ w podzapytaniu, mamy
możliwość uniknięcia dopisania czegokolwiek do pierwszego znalezionego
adresu e-mail w każdej grupie, natomiast pozostałym przypisywane są
kolejne liczby sekwencji. Sposób realizacji tego zadania demonstruje
poniższy kod:
SQL> insert into employees(emp_num, emp_firstname,
2 emp_name, emp_email)
3 select emp_num,
4 emp_firstname,
5 emp_name,
6 decode(rn, 1, emp_email,
7 substr(emp_email,
8 1, 12 - length(ltrim(to_char(rn))))
9 || ltrim(to_char(rn)))
10 from (select emp_num,
11 emp_firstname,
12 emp_name,
13 substr(substr(emp_firstname, 1, 1)
14 ||translate(emp_name, ' ''', '_'), 1, 12)
15 emp_email,
16 row_number()
17 over (partition by
18 substr(substr(emp_firstname, 1, 1)
PROWADZENIE WOJNY 85
19 ||translate(emp_name,' ''','_'), 1, 12)
20 order by emp_num) rn
21 from employees_old)
22 /
3000 rows created.
Elapsed: 00:00:11.68
Unikamy kosztu przetwarzania w trybie wierszowym, dzięki czemu to
rozwiązanie zajmuje około 60% czasu w porównaniu z pętlą.
Obsługa wyjątków zmusza do zastosowania logiki proceduralnej.
Zawsze warto brać pod uwagę obsługę wyjątków, jednak w zakresie
niezmuszającym do rezygnacji z deklaratywnej specyfiki SQL-a. [ Pobierz całość w formacie PDF ]