#291
Rozdział 13.
Wydajność i integralność bazy danych

Projekt dobrej aplikacji bazy danych jest nierozerwalnie związany z zagadnieniami wydajności i integralności. W chwili przekazania aplikacji do użytku, stopień jej wydajności nabiera dużego znaczenia. Jeżeli aplikacja działa w sposób mało efektywny, szansę na to, że zostanie zaakceptowana przez użytkowników są niewielkie. Podobnie rzecz ma się w przypadku integralności danych. Jeżeli nie mamy pewności co do poprawności danych, na których pracujemy, nie możemy polegać na bazie danych pod żadnym istotnym względem.
Niestety, te dwie istotne cechy każdej aplikacji bazy danych są, pod pewnymi względami, sobie przeciwstawne. Wiele kroków podejmowanych w celu zapewnienia integralności bazy danych powoduje obniżenie jej wydajności. Dla większości aplikacji koszt ten jest nieistotny, ponieważ dodatkowe obciążenie nałożone na bazę danych nie jest na tyle duże, aby mogło wpłynąć na jej ogólną wydajność. Mimo to, warto jednak znać techniki umożliwiające zwiększanie wydajności bazy danych oraz ponoszone przy tym koszty. Dzięki tej wiedzy programista może podejmować świadome decyzje co do projektu przyszłej bazy danych, zapewniając jej odpowiednią wydajność bez utraty integralności.

Zwiększanie wydajności bazy danych

Pierwszą rzeczą dotyczącą wydajności bazy danych, o której trzeba wiedzieć, jest to, że może ona być mierzona na wiele różnych sposobów. Niektóre metody optymalizacji zwiększają ogólną wydajność bazy danych, podczas gdy inne poprawiają tylko niektóre jej aspekty, ale jednocześnie obniżają inne. Wydajność bazy danych może być mierzona w oparciu o następujące parametry:
* Czas odpowiedzi.
* Liczba obsługiwanych jednocześnie użytkowników.
* Liczba transakcji na dzień.
#292
Aplikacje mają minimalne wymagania w odniesieniu do każdego z tych parametrów, ale w niektórych przypadkach można postarać się o lepsze wyniki na tle jednego z nich kosztem pozostałych.

Projekt bazy danych a jej wydajność

Zanim zaczniesz zastanawiać się nad tym, jak konstruować zapytania, aby wykonywały się one w najbardziej wydajny sposób, powinieneś upewnić się, czy projekt bazy danych zapewnia optymalne warunki jej działania. Jak mówiliśmy w rozdziale 2., istnieje kilka filozofii projektowania baz danych. Konstruując bazę danych, należy kierować się schematem, w oparciu o który powstaje aplikacja (DES[DES (ang. decision support) - systemy wspomagania decyzji (przyp. tłum).] lub OLTP[OLTP (ang. online transaction proccessing) - przetwarzanie transakcji w trybie rzeczywistym (przyp. tłum.).]). Więcej informacji na temat obu schematów aplikacji znajdziesz w rozdziale drugim (podrozdział "W praktyce").
Podstawowa zasada zwiększania wydajności: im mniejszy typ danych użyty do realizacji zadania, tym lepiej. Wytłumaczenie tej zasady jest bardzo proste: im mniej miejsca zajmują dane, tym mniej czasu trzeba na odnalezienie ich w pamięci lub odczytanie z dysku. Trzeba pamiętać o tym, że z różnymi typami danych oraz ustawieniami kolumn związana jest pewna nadmiarowa porcja danych. Na przykład pola znakowe o zmiennej długości wymagają kilku dodatkowych bajtów do zapisania rozmiaru danych przechowywanych w każdym polu. Niemniej jednak, gdy długości danych przechowywanych w polach tego typu są zróżnicowane w znacznym stopniu, zysk w porównaniu z użyciem pól o stałej długości rekompensuje koszt informacji nadmiarowej.
Pewne oszczędności wiążą się również z użyciem pól typu NOT NULL zamiast NULL. Pola mogące przechowywać wartości NULL zużywają kilka bajtów pamięci do zapisania informacji o tym, czy wartość w polu jest pusta, czy też nie.
Niektóre bazy danych posiadaj ą typ danych BIT, wymagający jednego bitu pamięci, do przechowywania wartości typu logicznego (Boolean). Użycie tego typu niesie ze sobą dwie korzyści. Pierwsza to minimalna ilość zajmowanej pamięci. Druga korzyść, to eliminacja konieczności pamiętania o tym, jakie wartości reprezentują prawdę i fałsz w polu danych. Na przykład, jeżeli do zapisywania wartości typu logicznego użyjemy pola CHAR (1), nałożymy na siebie przykry obowiązek pamiętania, jaka wartość w tym polu reprezentuje prawdę (np. T, Y lub 1), a jaka fałsz (np. F, N lub 0).

Narzędzia pomiaru wydajności

Za każdym razem, kiedy uruchamiamy zapytanie, jest dla niego przygotowywany plan wykonania (ang. execution plan). Polecenia zawarte w tym planie są następnie kolejno wykonywane przez bazę danych.
#293
W pewnym sensie, zapytania można przyrównać do problemów matematycznych. Proste problemy matematyczne wymagają wykonania pojedynczej operacji, np. dodając dwa do dwóch, wykonujemy jedną dodatkową operację, w wyniku której otrzymujemy wynik cztery. Jeżeli mnożymy przez siebie dwie wielocyfrowe liczby w sposób ręczny, najpierw wykonujemy pojedyncze mnożenie dla każdej z cyfr jednej liczby, a następnie dodajemy do siebie otrzymane wyniki. Liczba operacji rośnie wraz ze stopniem skomplikowania wyrażeń. Podobnie wygląda plan wykonania - jest to lista wszystkich pojedynczych operacji wykonywanych w celu otrzymania rezultatów większego zapytania.
W matematyce oraz w zapytaniach baz danych istnieje na ogół wiele metod pozwalających na rozwiązanie złożonego problemu. Człowiek rozwiązujący zadanie wybiera metodę najłatwiejszą z jego punktu widzenia. W bazie danych za dobranie najbardziej efektywnego planu wykonania zapytania odpowiedzialny jest optymali-zator zapytań (ang. ąuery optimizer). Używając narzędzi do pomiaru wydajności bazy danych można obserwować operacje należące do ścieżki wykonania wybranej przez optymalizator dla zapytania. ...... -<��
Większość baz danych posiada narzędzia pozwalające na wgląd w plan wykonania zapytań oraz określenie, czy zastosowanie podpowiedzi dla optymalizatora przynosi poprawę wydajności zapytań. Można również napisać kilka różnych zapytań i sprawdzić, które z nich cechuje się najlepszą wydajnością.
Bazy danych wykorzystujące Transact-SQL (Microsoft SQL Server oraz wszystkie produkty Sybase) posiadają ustawienie SHOWPLAN udostępniające użytkownikom analizy wykonania ich zapytań. W Oracle'u do tego samego celu służy ustawienie auto-trace. Narzędzia te rozbijają zapytania na indywidualne operacje, które składają się na ich wykonanie. Obu tym narzędziom przyjrzymy się za chwilę z osobna.

AUTOTRACE (Oracle)

Istnieją dwie metody pozwalające na obejrzenie planu wykonania określonego zapytania. Jeżeli chcemy zobaczyć plan wykonania zapytania przed jego faktycznym uruchomieniem, możemy do tego celu użyć polecenia EXPLAIN PLAN. Jeśli interesuje nas wyłączenie planu wykonania zapytań po ich uruchomieniu, możemy użyć polecenia SET AUTOTRACE ON. Wyłączenie podglądu planu wykonania następuje po ustawieniu AUTORACE na wartość 0FF (SET AUTOTRACE 0FF).
==================
Rada
Ustawienie AUTOTRACE w zupełności wystarcza do przeglądania planów wykonania wyrażeń typu SELECT. Jeżeli jednak używasz wyrażeń manipulujących danymi, do ich przeglądania lepiej skorzystać z polecenia EKPLAIN PLAN, które wyświetla plany wykonań zapytań bez faktycznej modyfikacji danych w tablicach.
==================

Wybiegnijmy na chwilę w przód żeby pokazać, jak działa polecenie EXPLAIN PLAN. Aby użyć polecenia EXPLAIN PLAN, należy wpisać EKPLAIN PLAN FOR, a następnie zapytanie. Plan wykonania przykładowego zapytania pokazuje listing 13.1.
#294
--------------------------------
Listing 13.1. Plan wykonania prostego zapytania

EXPLAIN PLAN FOR
SELECT *
FROM Movies

Explained.
--------------------------------

Aby polecenie EXPLAIN PLAN mogło działać, musi istnieć specjalna tablica, przechowująca plany zapytań. Tablicę taką (pod nazwą PLAN_TABLE) tworzy specjalny skrypt, o nazwie utlxplan.sql, dostarczany razem z systemem Oracle. Jeżeli utworzymy własną tablicę dla planów (o strukturze identycznej ze strukturą tablicy PLAN_TABLE), jej nazwę musimy podać w wyrażeniu EXPLAIN PLAN. Do identyfikacji odrębnych planów w tablicy służy pole STATMENT_ID, któremu trzeba nadać odpowiednią wartość 1 w wyrażeniu EXPLAIN PLAN, tak jak widać to na przykładzie z listingu 13.2.
--------------------------------
Listing 13.2. Zapisywanie planu zapytania

EKPLAIN PLAN
SET STATEMENT_ID = 'foo'
FOR
SELECT movie_title
FROM Movies

Explained.
--------------------------------

Do wydobycia danych z tablicy planów, po wykonaniu polecenia EKPLAIN PLAN, wystarczy użyć zwykłej instrukcji SELECT, jak ta w listingu 13.3. W tym przypadku wynikiem zapytania są dwie kolumny, ale oprócz nich istnieje jeszcze wiele innych danych zapisanych w tablicy planów.
--------------------------------
Listing 13.3. Wydobywanie wyników polecenia EKPLAIN PLAN z tablicy planów:

SELECT operation, object_name
FROM PlanJTable
WHERE statment id = 'foo'

OPERATION OBJECT_NAME
--------------------------------
SELECT STATMENT
TABLE ACCESS MOVIES
--------------------------------

AUTOTRACE jest trochę łatwiejszy w użyciu niż EXPLAIN PLAN. Wyniki zapytania generowane przy włączonej opcji AUTOTRACE przedstawia listing 13.4.
--------------------------------
Listing 13.4. Plan wykonania tworzony przy włączonej opcji AUTOTRACE

SET AUTOTRACE ON;

SELECT stuio_name
FROM Studios;

STUIO NAME
-----------
Giant
MPM
FKG
#295
Delighted Artists
Metaversal Studios Some
Studio

6 rows selected.

Execution Plan
--------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) 0F 'STUDIOS'

Statistics
----------------
219 recursive calls
4 db błock gets
44 consistent gets
3 physical reads
0 redo size
662 bytes sent via SQL*Net to client
659 bytes receiyed via SQL*Net from client
4 SQL*Net roundtrips to/from client
4 sorts (memory)

6 rows processed
--------------------------------

Jak widać, ilość danych dostępnych po włączeniu opcji AUTOTRACE jest spora. Wygenerowane tabele zawierają informacje dotyczące użycia bazy danych i zasobów systemowych przez zapytanie. Nas interesuje bardziej cześć dotycząca planu wykonania (tabela zatytułowana Execution Plan).
Widać tam trzy wpisy. Pierwsza kolumna zawiera identyfikator operacji w planie wykonania. Identyfikatory są przydzielane każdemu poleceniu sekwencyjnie (poczynając od 0). Druga kolumna wyświetla operację nadrzędną względem bieżącej, przy założeniu, że taka istnieje (pierwsza operacja w planie wykonania nie posiada operacji nadrzędnej). W trzeciej kolumnie znajduje się faktyczne polecenie, które zostało wykonane.
Jeśli orientujesz się w relatywnych kosztach różnorodnych operacji systemu Oracle, na podstawie planu wykonania łatwo będziesz mógł stwierdzić, w których miejscach istnieje szansa na wyraźne obniżenie wydajności bazy danych. Na przykład za każdym razem, kiedy napotkasz wpis FULL TABLE SCAN w sytuacji, gdy w warunku WHERE użyta została kolumna indeksowana, powinieneś przyjrzeć się dokładniej tej konstrukcji i spróbować zastąpić ją na taką, która będzie w stanie skorzystać z indeksu.
Przyjrzyjmy się teraz planowi wykonania bardziej złożonego zapytania - łączącego ze sobą trzy tabele (listing 13.5).
--------------------------------
Listing 13.5. Plan wykonania zapytania łączącego trzy tabele

SELECT movie_title, role, person_fname, person_lname
FROM Movies, Cast_Movies, People
WHERE Movies .movie_id = Cast_Movies .movie id
AND People.person_id = Cast_Movies.person_id
Execution Plan
--------------------------------
1 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) 0F 'CAST_MOVIES'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'PEOPLE'
#296
5 4 INDEX (UNIQUE SCAN) 0F 'SYS_C00665' (UNIQUE)
6 1 TABLE ACCESS (BY INDEK ROWID) 0F 'MOVIES'
7 6 INDEX (UNIQUE SCAN) 0F 'SYS C006671 (UNIQUE)
--------------------------------

Operacje użyte w powyższym przykładzie oraz inne operacje używane przez bazę danych Oracle zostaną omówione w dalszej części tego rozdziału, w podrozdziale dotyczącym optymalizatorów zapytań.

SHOWPLAN (Transact-SQL)

Wyświetlenie planu wykonania zapytania jest możliwe w Transact-SQL po ustawieniu opcji SHOWPLAN. W tym celu należy wykonać następujące polecenie:

SET SHOWPLAN ON
===============
Rada
W Serwerze SQL firmy Microsoft, polecenie SET SHOWPLAN zostało zastąpione dwoma innymi: SET SHOWPLAN__ALL i SET SHOW-PLAN_TEXT. Po ustawieniu parametru SHOWPLAN_ALL wyświetlany jest niezwykle szczegółowy raport dotyczący planu wykonania zapytania. SHOWPLAN_ TEXT dostarcza natomiast skróconą wersję tego raportu.
===============

Efekt, taki jak użycie polecenia EKPLAIN PLAN bazy Oracle, jest możliwy do osiągnięcia w Transact-SQL po ustawieniu parametru NOEXEC w sposób następujący:

SET NOEXEC ON

Kiedy oba parametry (SHOWPLAN i NOEKEC) są ustawione, uruchomienie zapytania spowoduje wyświetlenie planu jego wykonania (bez faktycznego uruchomienia zapytania). Przykład takiej sekwencji poleceń, które prowadzą do wyświetlenia planu wykonania wyrażenia SELECT, bez jego faktycznego uruchomienia, pokazuje listing 13.6.
--------------------------------
Listing 13.6. Jednoczesne użycie przełączników SHOWPLAN i NOEXEC

SET SHOWPLAN ON
SET NOEXEC ON

SELECT movie_title
FROM Movies

StmtText
--------------
SELECT Movies.movie_title, Studios.studio_name
FROM Movies, Studios
WHERE Studios.studio_id = Movies-movie_id

(1 row(s) affected)

StmtText
---------
l-Hash Matchdnner Join,
HASH:([Studios].[studio_id])-([Movies].[movie_id]),
RESIDUAL:([Movies].[movie_id]=[Studios].[studio_id]))
|-Table Scan(OBJECT: ( [rafeco] . [dbo] . [Studios] )))
|-Table ScanfOBJECT: ( [rafeco] . [dbo] . [Movies] ))
(3 row(s) affected)
SET NOEKEC 0FF
SET SHOWPLAN 0FF
#297
=====================
Uwaga
Wyrażenie typu SET SHOWPLAN_TEXT musi być wykonywane niezależnie od pozostałych zapytań w listingu.
==================
===================
Rada
Z użyciem parametru NOEKEC (lub EKPLAIN PLAN, w przypadku Oracle'a) wiążą się dwie korzyści. Pierwsza to możliwość obejrzenia planu wykonania złożonego zapytania, bez potrzeby czekania na jego wykonanie lub wyświetlenie danych. Nie interesuje nas tutaj plan wykonania prostego zapytania zwracającego wszystkie wiersze z 10-wierszowej tabeli, lecz np. plan wykonania zapytania łączącego cztery różne tabele i zwracającego 17000 wierszy. Możliwość obejrzenia planu bez konieczności przedzierania się przez 17000 rekordów lub oczekiwania na zakończenie zapytania staje się w takim przypadku niezwykle pomocnym udogodnieniem. Druga zaleta, to możliwość przeglądania planów wykonania poleceń modyfikujących, bez faktycznego modyfikowania danych w bazie. Jeżeli, dla sprawdzenia planu wykonania instrukcji DELETE, trzeba byłoby faktycznie usunąć wybrane rekordy, korzyść z posiadania narzędzi mierzących wydajność byłaby znikoma.
=======================

Indeksy

Indeksy są najbardziej znaczącym narzędziem poprawiającym wydajność aplikacji baz danych. Prawidłowo poindeksowana baza danych jest w stanie przetwarzać szybciej skomplikowane zapytania, niż baza danych nie posiadająca indeksów. Mechanizmy tworzenia indeksów zostały opisane w rozdziale trzecim. W tym miejscu zastanowimy się nad wykorzystaniem indeksów do maksymalizacji wydajności bazy danych.
To, jak optymalnie dobrać indeksy w tabelach, zależy wyłącznie od sposobu ich użytkowania. Jak wyjaśniliśmy w rozdziale trzecim, indeksy poprawiają szybkość operacji typu SELECT, ale jednocześnie obniżają sprawność wyrażeń UPDATE, INSEKT i DELETE. Kiedy wartości w indeksowanej kolumnie ulegają zmianie, uaktualniana jest nie tylko kolumna, ale również jej indeks. Użycie indeksów w systemach pracujących w trybie przetwarzania transakcji, w których liczba zapytań wydobywających informacje zbliżona jest do liczby zapytań modyfikujących, może obniżyć jej wydajność.
Niektóre zapytania korzystają z indeksów w większym stopniu, niż inne (są również takie zapytania, które w ogóle nie używają indeksu). Tworzenie indeksów w tabelach ma sens wyłączenie wtedy, gdy konstruowane przez nas zapytania są w stanie z nich skorzystać.
Najlepszy sposób wykorzystania indeksu opiera się na użyciu poindeksowanych kolumn w klauzulach WHERE, ORDER BY i GROUP BY. Pamiętać należy jednak o tym, że w przypadku modyfikacji poindeksowanej kolumny, występującej w klauzuli WHERE, jej indeks jest ignorowany.
#298
Indeksy klastrowe

Transact-SQL oferuje szczególny rodzaj indeksu, indeks klastrowy (ang. clustered index), który porządkuje rekordy fizycznie w oparciu o zawartość określonej kolumny tabeli.
Jak wiadomo, standardowy indeks jest uporządkowaną strukturą danych, reprezentującą zawartość kolumny (lub grupy kolumn). Korzyści, pod względem wzrostu wydajności osiągane przy użyciu indeksu klastrowego, są jeszcze większe. Ponieważ tabela sama w sobie uporządkowana jest według wartości występujących w kolumnie, zapytania skonstruowane w sposób umożliwiający korzystanie im z indeksu klastrowego są : w stanie osiągać ogromne zyski pod względem wydajności.
Zyski wynikające z użycia indeksów klastrowych ujawniają się w zapytaniach, które zawierają polecenia porządkowania wierszy według wartości kolumny. Przykładem może być zapytanie używające kolumny z indeksem klastrowym w klauzuli ORDER BY lub GROUP BY.

Zapytania wykorzystujące indeksy

Niektóre zapytania ignorują istnienie indeksów i wykonują swoje operacje na kolumnie tabeli, zamiast na skojarzonym z nią indeksie. Z oczywistych względów należy unikać stosowania tego typu zapytań, ponieważ ich efektywność jest mniejsza od zapytań używających indeksu. Dokładne zbadanie bazy umożliwia stwierdzenie, które zapytania korzystają z indeksu, a które nie. Przedstawimy teraz niektóre z warunków decydujących o tym, czy indeksy są używane w bazie danych Oracle (z dużym prawdopodobieństwem warunki te znajdują również zastosowanie w większości innych typów baz danych).
Porównanie LIKE, w którym porównywany jest początek łańcucha, korzysta z indeksów. Wyjątkiem są porównania LIKE zaczynające się od znaków wieloznacznych (ang. wildcards). Przyjrzyj się dwóm poniższym przykładom. Pierwsze zapytanie korzysta z indeksu kolumny movie_title:

SELECT raovie_title FROM Movies
WHERE movie_title LIKE 'The %'

Drugie zapytanie już nie:

SELECT movie_title
FROM Movies
WHERE movie_title LIKE '% Code %'

Indeksy są również pomijane, jeżeli w klauzuli WHERE umieszczone zostanie wywołanie funkcji. Zamiast szukać wartości w indeksie, zapytanie musi wydobyć wartość z tablicy, wywołać funkcję, a następnie porównać otrzymany łańcuch z łańcuchem w klauzuli WHERE. Oto przykład takiego zapytania:

SELECT movie_title
FROM Movies
WHERE TRIM(movie title) = 'Minerał House'
#299
===================
Rada
Fakt, iż użycie funkcji w odniesieniu do kolumn w klauzuli WHERE wyklucza możliwość użycia indeksu oznacza, że w niektórych przypadkach należy wyzbyć się lenistwa, przejawiającego się np. tak:
SELECT movie_title
FROM Movies
WHERE UPPER(movie) = 'THE CODE WARRIOR'
Jeżeli wiadomo, z jakich znaków zbudowana jest szukana wartość, należy skorzystać z tej wiedzy, zamiast opierać się na funkcjach LOWER i UPPER.
====================

Taka sama zasada obowiązuje w przypadku konkatenacji łańcuchów w klauzuli WHERE. Jeżeli kolumna występująca w klauzuli WHERE zostanie połączona z jakimkolwiek łańcuchem, indeks tej kolumny ponownie zostanie zignorowany. Np. to zapytanie nie użyje indeksu:

SELECT person_fname, person_lname
FROM People
WHERE person_fname ||' ' || person_lname = 'Rafę Colburn'

Lepsze efekty przyniesie użycie zapytania w postaci:

SELECT person_fname, person_lnarae
FROM People
WHERE person_fname = 'Rafę' AND person_lname = 'Colburn'

Indeksy nie są używane, jeżeli klauzula WHERE zawiera porównanie typu is NULL lub i s NOT NULL. Wartości NULL nie są dołączane do indeksu, nie istnieje zatem sposób pozwalający stwierdzić na jego podstawie, które rekordy zawierają wartość pustą. Indeksy nie są stosowne w połączeniu z warunkiem l s NOT NULL, ponieważ łatwiej jest po prostu przeszukać całą tabelę, odrzucając te rekordy, które w przeszukiwanej kolumnie zawierają wartość pustą.
Faktycznie rzecz biorąc, indeksy kolumn są ignorowane za każdym razem, kiedy w warunku zapytania znajduje się nierówność. Rozważmy następujące zapytanie:

SELECT movle_title
FROM Movies
WHERE movie title <>'The Cose Warrior'

Każdy rekord musi zostać przetworzony indywidualnie w celu sprawdzenia, czy jego pole movie_title pasuje do łańcucha 'The Code Warrior'. Jeśli tak jest, rekord jest odrzucany. Ponieważ nie ma tutaj warunku równości, nie istnieje możliwość odszukania wartości w indeksie. Problem ten dotyczy wszelkich testów nierówności, łącznie z NOT IN.
Indeksy mogą być tworzone dla pojedynczej kolumny lub dla wielu kolumn. Indeks wielokolumnowy jest wykorzystywany w zapytaniu, jeżeli w klauzuli WHERE użyta zostanie jego pierwsza kolumna. Sposób organizacji indeksu narzuca sposób czytania jego wartości od lewej do prawej strony. Jeżeli początkowa kolumna indeksu nie zostanie umieszczona w klauzuli WHERE, baza danych nie ma możliwości odszukania wartości w tym indeksie.
#300
Optymalizator zapytań

Aby móc rzeczywiście kontrolować wydajność zapytań, trzeba zrozumieć sposób działania optymalizatora zapytań w używanej przez nas bazie danych. Optymalizator przed uruchomieniem zapytania rozkłada go na części składowe i określa najlepszy sposób ich wykonania. Zrozumienie zasad działania optymalizatora pozwala programiście napisanie zapytań, które będą wykorzystywać jego możliwości.
Na przykład weźmy złączenia i podzapytania (omawiane w rozdziałach 8. i 9.). Opty-malizatory obsługują tego typu operacje w różny sposób, może zaistnieć sytuacja, w której dwa zapytania generujące ten sam rezultat wykonują się w różnym czasie, w zależności od tego, w jaki sposób zostały zinterpretowane przez optymalizator.
Kolejną korzyścią, płynącą ze znajomości zasady działania optymalizatora kodu, jest możliwość osadzania podpowiedzi dla optymalizatora wewnątrz własnych zapytań, co powoduje zmianę jego zachowania. Jeżeli optymalizator domyślnie nie wykona zapytania w najefektywniejszy sposób, do poprawienia jego wydajności można użyć "podpowiedzi".

Zasada działania

Nie można zbytnio generalizować zasady działania optymalizatorów zapytań, ponieważ narzędzia te są silnie uzależnione od konkretnej implementacji bazy danych. Nic dziwnego, w końcu wydajność bazy (wynikająca w głównej mierze z możliwości oferowanych przez optymalizator) jest jednym z elementów konkurencji między producentami baz danych. Każda firma działająca na rynku baz danych posiada własną filozofię odnośnie najlepszych metod optymalizowania zapytań. Osoby, szczególnie zainteresowane optymalizowaniem swoich zapytań w celu podniesienia ich wydajności, powinny dogłębnie przeanalizować działanie optymalizatora w bazie danych, której używają.
W tym rozdziale wyjaśnimy działanie optymalizatora zapytań w systemie Oracle. Mówiąc dokładniej, omówimy jeden z trzech optymalizatorów zapytań tej bazy. Oracle umożliwia administratorom baz danych skonfigurowanie instalacji w taki sposób, aby używała jednego z trzech dostępnych optymalizatorów zapytań. Ma to na celu umożliwienie optymalnego sposobu działania bazy danych w możliwie szerokim zakresie aplikacji.
Trzy wspomniane optymalizatory noszą nazwy: RULE, COST i CHOOSE. Optymalizator RULE wyznacza plany wykonań dla określonego zapytania, a następnie na podstawie zestawu reguł ustala, który z nich jest najbardziej wydajny. Optymalizator COST działa w połączeniu z poleceniem analyze. Polecenie analyze wylicza dane statystyczne odnośnie wszystkich obiektów w bazie danych, statystki te są wykorzystywane przez COST do wyznaczenia kosztów każdego z planów wykonań. ' Wybierany jest plan o najniższym koszcie.
#301
Ostatni optymalizator, CHOOSE, korzysta z usług optymalizatora RULE w odniesieniu do tablic nie przeanalizowanych i z optymalizatora COST, w przypadku tablic przeanalizowanych. Optymalizator ten może czasami obniżyć szybkość pracy bazy danych, jeżeli w zapytaniu pojawią się jednocześnie tablice przeanalizowane i nie-przeanalizowane.
Bazy danych Oracle realizują dostęp do tabeli na dwa różne sposoby: wykonują pełny przegląd tabeli lub korzystają z identyfikatorów ROWID.
======================
Uwaga
RowID jest ukrytą kolumną (występującą w każdej tabeli Oracle), która zawiera fizyczne lokalizacje rekordów. Indeksy wiążą dane z identyfikatorami RowID przechowujących je rekordów i w ten właśnie sposób przyspieszają działanie zapytań.
=======================

Zapytania pozbawione klauzul WHERE wymuszają pełny przegląd tabel. Im większa tabela użyta w zapytaniu, tym więcej czasu zajmuje przejrzenie wszystkich jej elementów. Pozostałe rodzaje zapytań wyszukują rekordy na podstawie RowID. Podczas przeszukiwania tabeli posiadającej indeks, szukany fragment danych odnajdywany jest w indeksie, kiedy to nastąpi, wystarczy już tylko odczytać dane z rekordu o identyfikatorze RowID wskazanym przez indeks.

Wskazówki dla optymalizatora

Kiedy zapytanie wydobywa dane z tabeli, używa do tego celu jednej z dwóch metod - pełnego przeglądu tabeli lub wyszukiwania na podstawie RowID. Jest to tylko jeden z przykładów decyzji, podejmowanych przez optymalizator zapytań w Oracle'u, na ścieżce prowadzącej do wyznaczenia optymalnej metody wykonania zapytania. Decyzje podejmowane przez optymalizator są na ogół trafne. Niemniej jednak, zdarzają się sytuacje, w których można poprawić działanie zapytania przez dostarczenie kompilatorowi "podpowiedzi" co do wyboru najlepszej metody działania spośród kilku możliwych.
W Oracle'u wskazówki umieszczane są wewnątrz komentarza, z dodatkowym znakiem plus (+), bezpośrednio za poleceniem SQL do którego się odnoszą. Na przykład, aby wskazać poleceniu SELECT konieczność użycia pełnego przeszukania tabeli, należy użyć następującej składni:

SELECT /*+ FULL(Movies)*/ movie title
FROM Movies

Znak + bezpośrednio za znacznikiem otwierającym komentarz wskazuje, iż w jego wnętrzu znajduje się podpowiedz. Podpowiedz, FULL(Movies), nakazuje optymaliza-torowi wygenerowanie wyników zapytania poprzez pełne przeszukanie tabeli Movies.
Wskazówki należy stosować wyłącznie wtedy, kiedy jesteśmy całkowicie pewni, że zapytanie wykona się lepiej z dostarczoną podpowiedzią, niż według planu wykonania obranego przez optymalizator. W miarę wyjaśniania działania optymalizatora zapytań, pokażemy inne podpowiedzi, które można użyć w bazie danych Oracle do kontrolowania egzekucji zapytań.
#302
Optymalizator i indeksy

Kiedy optymalizator przygotowuje plan wykonania zapytania, bierze pod uwagę dostępne indeksy. Dostępne są dwie operacje na indeksach: INDEX UNIQUE SCAN i INDEX RANGĘ SCAN. Odwołanie do kolumny indeksu następuje w klauzuli WHERE zapytania, w zależności od rodzaju użytego porównania.
Polecenie INDEX UNIQUE SCAN jest używane, gdy porównanie w klauzuli WHERE dotyczy pojedynczej wartości, a porównywana kolumna posiada unikalny indeks. Przykład zapytania oraz planu wykonania, z widoczną operacją INDEX UNIQUE SCAN, przedstawia listing 13.7.
---------------------------
Listing 13.7. Instrukcja SELECT używająca operacji INDEX UNIQUE SCAN oraz jej plan -wykonania

SELECT movie_title
FROM Movies
WHERE movie_title = 'The Code Warrior'

Execution Plan
---------------------------
0 SELECT STATEMENT Optimizer-CHOOSE
1 0 INDEX (UNIOUE SCAN) OF 'MOVIE_TITLE_INDEX' (UNIQUE)
---------------------------

Optymalizator używa operacji INDEK UNIQUE SCAN do odnalezienia wartości w indeksie równej łańcuchowi w klauzuli WHERE, a następnie, ponieważ kolumna movie_title jest jedynym elementem na liście instrukcji SELECT, zwraca tę wartość bezpośrednio z indeksu. Jeżeli na liście instrukcji wyboru znalazłaby się inna kolumna lub kolumny, identyfikator RowiD zwrócony z indeksu, zostałby przekazany operacji wyszukującej na podstawie RowiD, w celu odnalezienia pasującego rekordu.
Najbardziej wydajnym zapytaniem jest instrukcja SELECT, w której na liście argumentów oraz w klauzuli WHERE występuje klucz główny, porównywany z pojedynczą wartością. Ponieważ wartość zwracana przez zapytanie znajduje się w samym indeksie, nie zachodzi potrzeba wydobywania jakichkolwiek rekordów z tablicy, natomiast samo wyszukiwanie opiera się na niezwykle wydajnej operacji INDEX UNIQUE SCAN.
Drugi rodzaj operacji na indeksie nosi nazwę INDEX RANGĘ SCAN i jest używany, kiedy kolumna w klauzuli WHERE posiada indeks nieunikalny lub porównanie odnosi się do wielu wartości. INDEX RANGĘ SCAN czyta wiele wartości z indeksu, w przeciwieństwie do operacji INDEX UNIQUE SCAN, która czyta jedynie wartość szukaną. Dla przykładu zapytanie w listingu 13.8 używa w klauzuli WHERE kolumny indeksowanej, ale ponieważ indeks nie jest unikalny, stosowana jest operacja
INDEX RANGE SCAN.
---------------------------
Listing 13.8. Zapytanie oraz plan wykonania z poleceniem INDEX RANGĘ SCAN

SELECT person_fname, person_lname
FROM People
WHERE person_lname = 'Jong';

Execution Plan
---------------------------
0 SELECT STATEMENT Optimizer-CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PEOPLE'
2 l INDEK (RANGĘ SCAN) OF 'LNAME INDEX' (NON-UNIOUE)
---------------------------
#303
Wiele indeksów w jednym zapytaniu

Optymalizator jest w stanie wykorzystać wiele indeksów w ramach pojedynczej klauzuli WHERE, przy założeniu, że istnieje kilka indeksowanych kolumn (lub ta sama po-indeksowana kolumna została użyta więcej niż jeden raz) oraz warunki zawierające poindeksowane kolumny są w stanie wykorzystać ich indeksy. Nawet, jeśli tylko niektóre wyrażenia warunkowe w klauzuli WHERE mogą skorzystać z indeksów, widoczne będzie pewne polepszenie wydajności zapytań, w porównaniu z klauzulami WHERE pozbawionymi jakichkolwiek poindeksowanych kolumn lub warunków będących w stanie wykorzystać istnienie indeksu.
Tabela People posiada dwa indeksy: fname_index i lname_index. (Posiada ona ponadto unikalny indeks nałożony na kolumnę person_id, ponieważ jest ona kluczem głównym). Listing 13.9 zawiera przykład użyty już wcześniej w książce.
---------------------------
Listing 13.9. Zapytanie -wykorzystujące testy równości oraz operator AND

SELECT person_fname, person_lname
FROM People
WHERE person_fname = 'Rafę'
AND person_lname = 'Colburn'

Execution Plan
---------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 AND-EQUAL
2 1 INDEX (RANGĘ SCAN) OF ' FNAME_INDEX' (NON-UNIO.UE)
3 2 INDEX (RANGĘ SCAN) OF 'LNAME_INDEX' (NON-UNIQUE)
---------------------------

Do odnalezienia identyfikatorów RowlD ludzi o imieniu Rafę oraz identyfikatorów Ro-wio ludzi o nazwisku Colburn używane są dwie operacje INDEK RANGĘ SCAN. Powstałe w ten sposób dwa zbiory identyfikatorów są ze sobą porównywane - jeżeli w obu zbiorach znajdą się identyfikatory pasujące do siebie, zostają dołączone do wyników zapytania. Operacja tego typu określana jest mianem AND-EQUAL.
Przyjrzyjmy się teraz zapytaniu z listingu 13.10.
---------------------------
Listing 13.10. Zapytanie -wykorzystujące testy równości oraz operator OR
SELECT person_fname, person_lname
FROM People
WHERE person_fname = 'Rafę' OR person_lanme = 'Colburn'

Execution Plan
---------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PEOPLE'
3 2 INDEX (RANGĘ SCAN)OF 'LNAME_INDEX' (NON-UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID OF 'PEOPLE'
5 4 INDEX (RANGE SCAN) OF 'FNAME_INDEX' (NON-UNIQUE)
---------------------------

Z punktu widzenia optymalizacji, porównanie typu OR nie przebiega już tak gładko, jak operacja AND, korzystająca z operacji AND-EQUAL oraz dwukrotnego procesu przeglądania indeksu (typu RANGĘ SCAN).
#304
OR używa operacji CONCATENTAION, która pobiera wszystkie wiersze dopasowane przez jeden lub drugi warunek, usuwa duplikaty i zwraca pozostały zbiór rekordów. CONCATENATION jest również operacją względnie wydajną, chociaż nie aż w takim
stopniu jak AND-EQUAL.
Dla indeksów istnieje specjalna podpowiedz, pozwalająca w sposób jawny zalecić optymalizatorowi użycie indeksów dla określonej tabeli. Przykład użycia tego polecenia, o nazwie INDEK, przedstawia listing 13.11.
---------------------------
Listing 13.11. Użycie podpowiedzi INDEX

SELECT /* + INDEX(Movies) */ movie_title
FROM Movies
WHERE movie_title = 'The Code Warrior'

Execution Plan
---------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost-1 Card-1 Bytes-24)
1 0 INDEX (UNIQUE SCAN) OF 'MOVIE_TITLE_INDEX' (UNIQUE) (Cost=1 Card=l Bytes-24)
---------------------------

Warunek w klauzuli WHERE jest standardowym porównaniem bazującym na równości, co oznacza, że indeks zostałby użyty nawet bez naszej ingerencji. Niemniej jednak, czasem mogą zaistnieć takie warunki, w których optymalizator COST uzna zignorowanie indeksu za rozwiązanie bardziej korzystne. Jeżeli masz pewność, że tak nie jest, możesz użyć podpowiedzi INDEX, aby wymusić na bazie danych wzięcie pod uwagę indeksu w czasie przetwarzania zapytania.
Operacje na zbiorach danych
Operacje przeznaczone do operowania na zbiorach danych różnią się od tych przetwarzających wiersze pojedynczo. Wszystkie omawiane do tej pory operacje przetwarzały dane wiersz po wierszu. Kiedy użyta została jedna z operacji dostępu do tablicy (TABLE ACCESS) lub przeglądania indeksu (INDEX SCAN), jej zadanie polegało na kolejnym porównywaniu wydobywanych wartości z warunkiem w klauzuli WHERE i zwracaniu ich, gdy wynikiem tego porównania była prawda (bez względu na to, w jakim związku pozostawał bieżący wiersz w odniesieniu do reszty rekordów w tablicy).
Z drugiej jednak strony, istnieje wiele konstrukcji SQL, które muszą mieć możliwość przetestowania wszystkich wierszy zwróconych przez zapytanie, zanim będą mogły ostatecznie zwrócić jakąkolwiek odpowiedź. Przykładem niech będzie klauzula ORDER BY. Odpowiedni porządek wierszy zwracanych przez zapytanie nie może być ustalony do momentu, kiedy wszystkie z nich zostaną przeanalizowane. Pierwsze dwie operacje działające na zbiorach, o których powinieneś wiedzieć, to SORT ORDER BY i SORT UNiQUE. Jak łatwo można sobie wyobrazić, operacja SORT ORDER, BY służy do zmiany uporządkowania zbioru wierszy, tak aby ich kolejność opierała się na wartościach pól wyspecyfikowanych w klauzuli ORDER BY. SORT UNIQUE jest używany w z klauzulą DISTINCT do analizy zbioru wierszy, w celu usunięcia z niego wszelkich duplikatów.
#305
Jak wiadomo, użycie jednego z operatorów MINUS, UNION lub INTERSECT powoduje, że wiersze powtarzające się w odpowiedziach zapytań są odrzucane (o ile baza danych nie zostanie jawnie poinstruowana, aby tak nie robić). W zapytaniach tego typu również używany jest operator SORT UNIQUE.
Do pozostałych operacji na zbiorach danych zaliczają się SORT AGGREGATE i SORT GROUP BY. Nazwy wyraźnie sugerują ich przeznaczenie. Operator SORT AGGREGATE występuje za operacją TABLE ACCESS FULL i ma za zadanie wykonać funkcje agregujące wyspecyfikowane w zapytaniu, w stosunku do danych zwróconych przez TABLE ACCESS FULL. Kiedy w zapytaniu użyta zostanie klauzula GROUP BY, baza danych używa polecenia TABLE ACCESS FULL do wydobycia pasujących wierszy, które następnie dzielone są na grupy przy użyciu SORT GROUP, po czym każda grupa jest indywidualnie przetwarzana przez SORT AGGREGATE.

Optymalizator zapytań i widoki

Widok jest po prostu zapamiętanym zapytaniem. Przykładowo, gdybyśmy chcieli stworzyć widok zawierający filmy o dochodach przekraczających 50 milionów dolarów, mógłby on wyglądać tak:

CREATE VIEW Movies_0ver_50
AS
SELECT *
FROM Movies
WHERE gross > 50

Po stworzeniu widoku, można użyć go w instrukcji SELECT. Optymalizator nie stosuje warunków zawartych w nowym zapytaniu do danych zwróconych przez widok, lecz zamiast tego próbuje połączyć ze sobą oba zapytania. Weźmy pod uwagę następujący przykład:

SELECT movie_title, gross
FROM Movies_0ver_50
WHERE budget > 0

Optymalizator połączy oba powyższe zapytania, zamieniając je w jedną większą instrukcję SELECT:

SELECT movie_title, gross
FROM Movies
WHERE budget > 0
AND gross > 50

Jeżeli zapytanie w widoku jest skonstruowane w taki sposób, że nie może być połączone z instrukcją SELECT, wtedy instrukcja ta wykonywana jest dla wyników zwróconych przez widok.

Optymalizator zapytań i podzapytania

Jeśli jest to tylko możliwe, Optymalizator zapytań Oracle'a stara się zamieniać zapytania zawierające podzapytania na złączenia. Jeżeli zapytanie może być przetworzone w mniejszej ilości kroków przy użyciu operacji złączenia, niż poprzez wykonanie najpierw podzapytania, a następnie zapytania nadrzędnego, zapytanie jest transformowane na postać złączenia.
#306
Jeżeli podzapytanie nie może zostać przetworzone na złączenie, jest ono wykonywane w normalny sposób, a wygenerowane w ten sposób rezultaty są dołączane w odpowiednim miejscu do zapytania zewnętrznego. Kiedy to nastąpi, wykonywane jest zapytanie zewnętrzne.

Operacje złączenia

Oracle używa trzech operacji złączenia: MERGE JOIN, NESTED LOOPS i HASH JO-IN. Optymalizator wybiera typ operacji złączenia w oparciu o szereg czynników, włączając w to obecność indeksów, warunki występujące w zapytaniu oraz dane statystyczne operacji (pod warunkiem, że użyta została funkcja ANALYZE). Jeżeli łączone są więcej niż dwie tabele, optymalizator rozbija zapytanie na kilka niezależnych operacji join. Np. w zapytaniu łączącym cztery tabele, najpierw łączone są ze sobą dwie z nich, wynik tej operacji łączony jest z trzecią tabelą i w końcu wynik złączenia dwóch pierwszych tabel oraz trzeciej z nich zostanie ponownie złączony z tabelą czwartą, generując w ten sposób ostateczny wynik. Właśnie z tego powodu wielokrotne operacje złączenia zajmują czasami bardzo dużo czasu - każda dodatkowa tabela powoduje konieczność wykonania całkowicie nowej operacji join.
Operacja MERGE JOIN pobiera kolumny użyte w warunku złączenia, sortuje je i łączy. Jest to najprostsza forma operacji złączenia, wykonywana na ogół w sytuacji, kiedy nie są dostępne indeksy mogące przyspieszyć przetwarzanie zapytania. MERGE JOIN jest poprzedzany kilkoma innymi operacjami. Wszystkie wartości użyte w zapytaniu są wydobywane poleceniem TABLE ACCESS FULL i sortowane operacją SORT JOIN. Powstałe w ten sposób dwie listy wartości są łączone przez MERGE JOIN.
Obie operacje SORT JOIN działają na zbiorach, zatem żaden wiersz nie może zostać zwrócony użytkownikowi, zanim nie zostaną one zakończone. Wyniki generowane są od momentu, kiedy wyniki obu operacji SORT JOIN zostaną przekazane operacji MERGE JOIN. ,
Operacja NESTED LOOPS pobiera listę wartości z jednej kolumny, czyta każdą z nich pojedynczo i próbuje za każdym razem znaleźć wartość pasującą w drugiej kolumnie. Działanie takie wydaje się być bardzo powolne i jest takie w rzeczywistości, o ile nie są dostępne indeksy. W operacji NESTED LOOPS występują dwie tablice. Pierwsza z nich jest tablicą sterującą (ang. driving table) - jej wartości są przetwarzane tylko raz. Druga tabela czytana jest wielokrotnie dla sprawdzenia, czy element z tablicy sterującej pasuje do któregoś z jej elementów.
Wykonanie operacji NESTED LOOPS wymaga pełnego przeglądu (FULL TABLE SCAN) tablicy sterującej. Jeśli okaże się, że kolumną użytą w porównaniu jest klucz główny drugiej tabeli, sprawdzenie, czy zawiera ona wartość pasującą do bieżącego elementu tablicy sterującej, odbywać będzie się poprzez operację INDEX UNIQUE SCAN. Problemy z szybkością działania zapytania stają się tym większe, im większy jest rozmiar tablicy sterującej. Wniosek jest następujący: zapytania powinny być pisane w taki sposób, aby rozmiar tablicy sterującej był jak najmniejszy, co będzie miało bezpośredni wpływ na liczbę operacji przeprowadzanych na drugiej tablicy.
#307
Trzecim typem operacji złączenia jest HASH JOIN. W operacji tej baza danych wydobywa wartości obu kolumn, użytych w warunku złączenia, poleceniem TABLE ACCESS FULL i umieszcza je w pamięci. Następnie wykonuje pewne operacje rozpraszania danych w celu zwiększenia szybkości porównań między dwoma zbiorami wartości i dopasowuje odpowiadające wartości z obu tabel. Operacja HASH JOIN nie korzysta z indeksów i nie wykonuje operacji w sposób zbiorowy (wiersze zwracane są natychmiast po odnalezieniu pasujących wartości).

Podpowiedzi dotyczące złączeń

Ponieważ złączenia mogą być przetwarzane w różny sposób, dostępne są podpowiedzi pozwalające określić, która metoda złączenia powinna być użyta. Podpowiedzi ALL_ROWS i FIRST_ROWS określają, czy optymalizator powinien faworyzować plany wykonania zwracające, odpowiednio, wszystkie wiersze zapytania, czy też zwracające pierwsze częściowe wyniki zapytania w najszybszym możliwym czasie.
Można również skorzystać z podpowiedzi nakazujących użycie konkretnej operacji do stworzenia złączenia. Do tego celu służą trzy podpowiedzi, których nazwy sugerują rodzaj operacji: USE_NL, USE_MERGE i USE_HASH. Jeżeli zapytanie zawiera więcej niż dwie tabele w klauzuli FROM, nazwa tej, która ma zostać skojarzona z operacją, powinna zostać umieszczona w nawiasach bezpośrednio za samą podpowiedzią. Przykładowo, jeżeli łączymy tabele Movies, Cast_Movies i People i chcemy użyć tabeli Movies jako tablicy sterującej w operacji NESTED LOOPS, nasze zapytanie powinno wyglądać, tak jak przedstawia je listing 13.12.
---------------------------
Listing 13.12. Użycie podpowiedzi specyfikującej rodzaj operacji złączenia wraz z tablicą sterującą

SELECT /*+ USE_NL{Movies) */
movie_title, role, person_fname, person_lname
FROM Movies, Cast_Movies, People
WHERE Movies.movie_id = Cast_Movies.movie_id
AND People.person_id = Cast_Movles.person_id
---------------------------

Integralność danych

Integralność danych jest zagadnieniem zupełnie odrębnym od wydajności baz danych, ale nie mniej ważnym. Wiele baz danych przechowuje informacje mające fundamentalne znaczenie dla pomyślnego działania firmy, strony WWW lub innego celu ich istnienia. Aby móc polegać na bazie danych, trzeba być niemal całkowicie pewnym, że jej zawartość nie zostanie zmodyfikowana w sposób nieautoryzowany i dane nie ulegną uszkodzeniu.
Być może, jeszcze ważniejszym wymogiem jest obowiązek zachowania przez dane organizacji narzuconej im przez właścicieli bazy danych. Istnieje wiele środków, dzięki którym możliwe jest zapewnienie danym integralności, wynikającej z relacyjnego modelu danych, jak i spełnienie reguł biznesowych obowiązujących aplikacje korzystające z bazy danych. Do najpowszechniej stosowanych środków wymuszających integralność danych zaliczają się: unikalne indeksy, sprawdzanie więzów integralności, stosowanie powiązań z kluczami obcymi oraz wyzwalacze.
#308
Wszystkie wymienione koncepcje zostały omówione już wcześniej lub zostaną przedstawione w najbliższym czasie (wyzwalaczom poświęcone są fragmenty rozdziałów 15., 16. i 17.). Tym, czym interesujemy się w tym rozdziale, jest efekt, jaki metody wymuszające integralność wywierają na wydajność bazy danych. Jeżeli wydajność bazy danych nie jest istotnym problemem, tabele mogą zostać obłożone licznymi więzami integralności, dzięki czemu będziemy mieli większą pewność co do poprawności danych. Niestety, sytuacje, w których szybkość pracy bazy danych nie stanowi naszego zmartwienia, są marginalne. Wcześniej czy później wydajność bazy spadnie do poziomu, który nie będzie już możliwy do zaakceptowania i wtedy trzeba będzie podjąć decyzję, które więzy integralności mogą zostać usunięte dla poprawy sytuacji.
W niektórych przypadkach reguły zachowania integralności mogą zostać przeniesione z bazy danych do współpracujących z nią aplikacji. Postępowanie takie jest nieoptymalne z kilku powodów, jednak w sytuacji, kiedy trzeba odciążyć bazę danych, przesunięcie zasad integralności tabel na stronę aplikacji jest rozwiązaniem możliwym do zaakceptowania. Często dobrym rozwiązaniem jest wymuszanie integralności zarówno po stronie aplikacji, jak i po stronie bazy danych, ponieważ w ten sposób oszczędza się jej wydajność. Znacznie gorszym rozwiązaniem jest natomiast próba przerzucenia całej odpowiedzialności za integralność danych na aplikację.

Wymuszanie integralności w aplikacjach baz danych

Pisząc aplikację, która modyfikuje dane bazy danych, należy zadbać o to, aby zachowywała się ona zgodnie z zasadami integralności obowiązującymi w samej bazie danych. Np. jeśli kolumna w bazie danych została wyspecyfikowana jako NOT NULL (nie akceptuje wartości pustych), aplikacja powinna sprawdzać, czy użytkownik faktycznie wprowadził dla tego pola jakąś wartość. Podobnie rzecz ma się z zakresem wartości - aplikacja powinna sprawdzać, czy wartości wprowadzane przez użytkownika mieszczą się w zakresach akceptowanych przez poszczególne pola bazy danych.
Mimo że baza danych i tak sprawdza wszelkie wartości przed ich wstawieniem lub uaktualnieniem, można usprawnić jej działanie przez niedopuszczanie do transakcji, o których z góry wiadomo, że nie dojdą do skutku. Przy odpowiednim zaprogramowaniu aplikacji zatrzymywane będą wszystkie transakcje bazujące na błędnych danych, dzięki czemu zaoszczędzimy bazie wiele zbędnej pracy.
Czasami jednak sprawdzanie więzów integralności przez aplikację, przed wydaniem właściwego polecenia, jest po prostu nieopłacalne. Za przykład weźmy więzy integralności wynikające z obecności klucza obcego. Aplikacja może sprawdzić poprawność takiego powiązania wydając polecenie SELECT, które określi, czy modyfikowanej wartości odpowiada jakakolwiek wartość w kolumnie, z którą związany jest klucz obcy.
Działanie takie nie będzie jednak żadnym ulepszeniem w porównaniu z próbą wstawienia lub uaktualnienia rekordu i sprawdzenia, czy wartości przekazane bazie danych zostały zaakceptowane.
#309
Po dodaniu więzów integralności do tabel, należy zastanowić się nad tym, w jaki sposób więzy te można odwzorować w aplikacji współpracującej z bazą. Dzięki temu od razu unikniemy błędnych transakcji, a jeśli zajdzie potrzeba zwiększenia wydajności bazy danych, możemy usunąć więzy integralności z tabel i oprzeć się jedynie na regułach zaimplementowanych w aplikacji.
Próby zastąpienia więzów integralności w bazie poprzez odpowiednie mechanizmy weryfikujące aplikacji wiążą się nierozerwalnie z kilkoma istotnymi kwestiami. Po pierwsze, jeżeli użytkownikowi przez przypadek (lub celowo) uda się obejść ograniczenia narzucone przez aplikację, będzie w stanie bezkarnie wprowadzać błędne dane do bazy. Po drugie i ważniejsze, w przypadku większości baz danych nie istnieje gwarancja, że dane będą wprowadzane przy użyciu aplikacji sprawdzającej ich poprawność. Nasze wysiłki włożone w budowę bezpiecznej aplikacji przy użyciu PowerBuildera zdadzą się na nic, jeśli użytkownik skorzysta dla przykładu z klienta SQL*Plus. Dlatego najlepszym rozwiązaniem jest zachowanie powiązań na poziomie bazy danych, dzięki czemu użytkownik nie będzie w stanie wprowadzić błędnych danych bez ręcznego wyłączenia nałożonych ograniczeń.

Integralność kontra wydajność

W tym podrozdziale zajmiemy się kilkoma specyficznymi mechanizmami ochrony integralności danych, a także ich wpływem na wydajność bazy danych. Każdy rodzaj więzów integralności oddziałuje na wydajność bazy danych. Wynika to z faktu, iż każde ograniczenie nałożone na bazę wymaga dodatkowego czasu przetwarzania.
Na przykład weźmy wyzwalacz. Kiedy w stosunku do określonej tabeli wywołane zostanie wyrażenie modyfikujące dane, baza danych musi najpierw określić, czy dla tabeli tej utworzone zostały jakiekolwiek wyzwalacze. Jeżeli dla tabeli zostały zdefiniowane wyzwalacze, baza sprawdza, czy któreś z nich mają zastosowanie w przypadku wykonywanego wyrażenia. Przykładowo, jeżeli z daną tabelą związany jest wyzwalacz uruchamiany przy uaktualnieniu, a wykonywanym wyrażeniem jest INSEKT, wyzwalacz zostanie zignorowany. Jeśli jednak wyzwalacz pasuje do wyrażenia, musi zastać uruchomiony. Wszystkie powyższe kroki wymagają skończonego czasu przetwarzania. Łatwo zatem wywnioskować, że wyrażenia modyfikujące dane będą wykonywać się szybciej w przypadku tabel, w których nie ma wyzwalaczy. (Wyzwalacze omawiane są w rozdziale 15.).
Podobna zasada obowiązuje w innych typach więzów integralności. Nieważne, czy ograniczeniem jest zwykły NOT NULL, czy też skomplikowany wyzwalacz testujący inne tabele, aby sprawdzić poprawność danych - wszystkie ograniczenia wpływają na wydajność bazy danych. Jako programista musisz decydować, czy zyski w zakresie zachowania integralności, wynikające z nałożenia dodatkowych ograniczeń, przewyższają koszty ich użycia (odzwierciedlane przez spadek szybkości pracy bazy danych). Za dobre rozwiązanie uznaje się nałożenie na bazę wszelkich niezbędnych ograniczeń zapewniających integralność danych, a później zdejmowanie ich w według stopnia ważności, gdy zajdzie potrzeba poprawienia wydajności.
#310
W praktyce

Więzy integralności założone na bazie danych są w stanie zapewnić wysoki stopień bezpieczeństwa przechowywanych w niej informacji. Mimo to, warto również zadbać o odpowiednie mechanizmy zabezpieczające na poziomie aplikacji. Poniżej opisujemy techniki, które można wykorzystać we własnych aplikacjach dla zapewnienia weryfikacji danych wprowadzanych przez użytkowników, a tym samym poprawienia jakości ich działania, przez zredukowanie niepotrzebnych operacji na bazie danych.
Zapewnienie weryfikacji danych na wysokim poziomie aplikacji wiąże się z dwoma korzyściami. Pierwsza, to rozłożenie ciężaru zadań związanych z weryfikacją na kilka różnych komponentów aplikacji i tym samym poprawienie jej ogólnej szybkości działania. Druga korzyść, to zapewnienie pewnego sprzężenia zwrotnego między użytkownikiem a aplikacją, które ułatwia mu poprawianie własnych błędów.
Przyjrzyjmy się składnikom typowej aplikacji. Jeżeli jest to aplikacja typu klient-serwer, składa się ona z aplikacji klienta, uruchamianej na komputerze użytkownika, być może serwera tej aplikacji oraz samego serwera bazy danych. Oprócz więzów integralności i wyzwalaczy umieszczonych na poziomie bazy danych, weryfikacja danych może być przeprowadzana na obu pozostałych poziomach, tzn. w serwerze aplikacji i w aplikacji klienta. Podobnie rzecz ma się w przypadku aplikacji sieci WWW. Dane mogą być sprawdzane przy użyciu języka JavaScript po stronie przeglądarki WWW użytkownika (poziom klienta), na poziomie serwera aplikacji i na poziomie bazy danych.
Dwoma podstawowymi metodami weryfikacji poprawności danych na poziomie klienta są: sprawdzanie danych wprowadzanych przez użytkownika z klawiatury oraz ograniczenie danych, jakie użytkownik może wprowadzić do z góry ustalonego zbioru wartości. Zacznijmy najpierw od drugiej metody. Większość narzędzi do tworzenia aplikacji oferuje szeroki zakres różnego typu obiektów interfejsu użytkownika. Podstawowym obiektem w tej grupie jest pole tekstowe, umożliwiające wpisywanie dowolnych treści. Ze względu jednak na tak dużą elastyczność tego typu pola jesteśmy zmuszeni do sprawdzania, czy dane wprowadzone przez użytkownika z klawiatury pasuj ą do wymaganego formatu.
Dla wielu pól można zastosować inne rodzaje obiektów kontrolnych, w tym pola wyboru, listy rozwijalne lub przyciski opcji. Np., jeśli chcemy umożliwić użytkownikowi wprowadzenie nazwy kraju, w którym mieszka, możemy do tego celu użyć pola tekstowego o długości 20 znaków (w które można wpisać cokolwiek), pola o długości 2 znaków (w które można wpisać dwuliterowy skrót nazwy, niekoniecznie poprawny) lub listy rozwijalnej, która pozwala na wybór jednej z ustalonych wcześniej nazw.
Jeżeli użyjemy listy rozwijalnej, nie musimy się w ogóle martwić o to, czy wpisana przez użytkownika nazwa kraju lub jej skrót jest poprawny. Kiedy tylko liczba możliwych opcji jest skończona, warto rozważyć użycie obiektu w formie listy, zamiast zwykłego pola tekstowego, które daje użytkownikowi zbyt dużą swobodę.
#311
Żeby móc skorzystać z listy, trzeba ją wcześniej utworzyć. Istnieje kilka możliwości w tym zakresie, a wybór jednej z nich jest zazwyczaj podyktowany względami szybkości działania aplikacji. Najprostsza metoda opiera się na sztywnym zakodowaniu wartości listy w aplikacji. Niepodważalną zaletą tego rozwiązania jest jego szybkość, wadą z kolei jest konieczność wprowadzania zmian do aplikacji, jeśli zbiór opcji którejkolwiek z list ulegnie zmianie. W przypadku aplikacji klienta bazy danych stosowanej przez wielu użytkowników, wprowadzenie zmian do programu będzie związane z koniecznością jego redystrybucji, co wydaje się być podejściem zupełnie nieefektywnym.
Drugie rozwiązanie sprowadza się do zapisania odpowiednich danych w bazie, a następnie użycia zapytania do wypełnienia listy, za każdym razem, kiedy użytkownik wyświetla formularz. Wygoda tego podejścia polega na tym, że zmiana opcji na liście wymaga jedynie modyfikacji odpowiedniej tabeli w bazie danych. Ujemną stroną rozwiązania jest wprowadzanie dodatkowego (stałego) obciążenia bazy, przez stosowanie zapytań wewnątrz formularzy.
Trzeci sposób, to przechowywanie danych pól formularza w taki sposób, aby nie były one ani częścią aplikacji, ani też częścią bazy danych. Jest to pewnego rodzaju złoty środek, kiedy zależy nam na elastyczności rozwiązania, przy jednoczesnej oszczędności wydajności bazy danych.
Rozważając metodę wypełniania pól formularza, należy wziąć pod uwagę dwie rzeczy: częstość zmian opcji pól formularza oraz wydajność bazy danych. O wydajności mówiliśmy już wcześniej, skupmy się zatem na "niestabilności" danych. Jeżeli pole formularza umożliwia jedynie wybór typu Tak lub Nie, wartości te można zakodować na stałe. Szansa, że ulegną one zmianie jest bardzo mała. Innym przykładem jest lista nazw dni tygodnia lub miesięcy - raczej trudno sobie wyobrazić, że wartości te kiedykolwiek ulegną zmianie.
Z drugiej strony, istniej ą sytuacje, w których zbiór elementów listy jest przyporządkowywany każdemu użytkownikowi indywidualnie lub ulega bardzo częstym zmianom. Wtedy zapisanie potencjalnych opcji w bazie danych wydaje się być jedynym polecanym rozwiązaniem.
Druga metoda sprawdzania poprawności danych sprowadza się do ich weryfikacji na różnych poziomach aplikacji. Oczywiście, najlepszą metodą weryfikacji danych jest sprawdzanie wszelkich możliwych rzeczy na poziomie aplikacji klienta. Spowolnienia w pracy związane z walidacją danych na poziomie klienta dotyczą jedynie lokalnego użytkownika i nie mają wpływu na pracę innych użytkowników korzystających z tego samego systemu. Sytuacja taka nie występuje na poziomie serwera aplikacji oraz na poziomie bazy danych. Ponadto użytkownikom łatwiej jest poprawiać własne błędy, kiedy są o tym natychmiast informowani przez aplikację, niż w przypadku, kiedy zmuszeni są do wysłania całego formularza, a następnie otrzymują listę błędów wynikających z pomyłek, jakie popełnili.
Nawet jeśli weryfikacja danych po stronie klienta nie jest podyktowana względami wydajności, warto zadbać o umieszczenie odpowiednich mechanizmów sprawdzających na tym poziomie. W ten sposób poprawia się ogólna wydajność systemu (nawet jeśli walidacją przeprowadzana jest na wyższych poziomach), ponieważ odpowiednio wcześnie wyłapane błędy oszczędzają bazie danych zbędnych transakcji.