Rozdział 24
Zaawansowane programowanie
w SQL
Rozdziału niniejszego nie kontynuujemy od tego miejsca, w którym
zakończyliśmy rozdział 5. Spora luka dzieli podstawową i zaawansowaną
znajomość języka SQL, stąd też brak ciągłości w tematyce obu rozdziałów.
W naszych rozważaniach przyjęliśmy, że połączenie z bazą danych, a także
formułowanie i wydawanie komend języka SQL nie sprawia nam większych
problemów. W przykładach zamieszczonych w tym rozdziale korzystamy
z platformy InterBase i dostępnego w jej ramach edytora WISQL (oczywiście
może to być dowolny, odpowiadający nam edytor SQL).
W rozdziale niniejszym stale korzystamy z bazy danych i tabel, utworzonych
w rozdziale 5.
Niektóre z występujących poniżej tabel utworzono w drugiej części książki.
Chociaż nie są one niezbędne do zrozumienia przedstawianych pojęć,
proponujemy zapoznać się z informacjami (o ich tworzeniu), zamieszczonymi
w rozdziale 8.
W rozdziale tym zestawiono dwie główne rodziny dialektów (i składni) SQL:
ANSI i Sybase. Ponieważ dialekt InterBase jest w dużym stopniu zgodny z normą
ANSI, większość przykładów - w których używa się składni InterBase - będzie
działało na innych, zgodnych z ANSI platformach (takich jak Oracle). Z drugiej
strony, produkt Sybase SQL Server odchodzi od normy ANSI pod wieloma
istotnymi względami, podobnie jak jego licencjonowany kuzyn, Microsoft SQL
Server.
UWAGA:
Chociaż w rozdziale niniejszym często zestawiamy ze sobą różne dialekty SQL,
więcej informacji o każdej z platform DBMS można znalez
ć w rozdziałach od 15
do 18. Rozdziały te, z których każdy omawia jedną z platform: Oracle, InterBase,
Sybase lub Microsoft SQL Server, opisują szereg dostarczanych przez danego
producenta udogodnień oraz specyfikę składni dialektu SQL każdej z platform.
Celowo unikaliśmy powtarzania informacji, które można znalez
ć w dokumentacji
posiadanej platformy SQL. Omówiliśmy szereg zagadnień, związanych z językiem
670 Część IV
SQL - niektóre z nich są ,,zaawansowane , ponieważ nie uczy się ich na poziomie
podstawowym, a inne dlatego, że są naprawdę skomplikowane i trudne do
opanowania.
Wiele wysiłku poświęciliśmy  wysławianiu się w języku SQL. Zamiast objaśniać
SQL w języku potocznym, używamy do jego opisu również języka SQL. Takie
podejście można porównać do nauczania języka francuskiego po francusku.
Języki DDL i DML
Poruszamy tu dwie szerokie kategorie zaawansowanych wyrażeń w SQL:
komendy DDL (od Data Definition Language ) oraz komendy DML (od Data
Manipulation Language). Komend DDL używamy do tworzenia obiektów bazy
danych i zarządzania nimi. Na przykład komenda CREATE TABLE stanowi
wyrażenie języka DDL. Za pomocą komend DML kierujemy zapytania do tych
obiektów i modyfikujemy dane w nich zawarte. Przykładem komendy języka DML
jest wyrażenie SQL UPDATE. W celu podkreślenia różnicy między DDL a DML,
poniższy opis podzielono na część opisującą zaawansowaną składnię DDL i część
opisującą zaawansowaną składnię DML. Jakkolwiek SQL obejmuje, w ogólnym
sensie tego słowa, zarówno język DDL, jak i DML, często dobrze jest wiedzieć, do
której z tych dwóch klas języka SQL należy dana komenda. Komendy DDL są
przeważnie podobne do innych komend DDL, a komendy DML - do innych
komend DML.
Zaawansowane konstrukcje języka DDL
Przedstawione w tym podrozdziale fragmenty kodu nie dotyczą aktualizowania ani
modyfikowania danych, ale tworzenia i modyfikacji obiektów bazy danych - tzn.
sposobów, w jaki dane są pamiętane oraz w jaki użytkownicy i inne obiekty mogą
uzyskiwać do nich dostęp.
Bazy danych
Zgodnie z rozdziałem 5, komendy CREATE DATABASE używamy do tworzenia
nowych baz danych. Ponieważ obszar zajmowany przez dane zmienia się wraz
z upływem czasu, nasze bazy danych prawdopodobnie będą wymagały
rozszerzenia. Przez rozproszenie bazy danych na wielu dyskach możemy
zwiększyć jej wydajność. Do rozszerzania bazy używamy komendy ALTER
DATABASE - zgodnie z poniższym przykładem w dialekcie InterBase:
ALTER DATABASE
ADD  C:\DATA\IB\ORDENT2
Rozdział 24 Zaawansowane programowanie w SQL 671
Dodaliśmy właśnie nowy plik do bazy danych.
Podobnej konstrukcji składniowej używamy w dialektach Sybase i Microsoft, jak
niżej:
ALTER DATABASE ORDENT
ON ORDENT2=100
W przypadku platform Sybase i Microsoft, komenda ALTER DATABASE
rzeczywiście powoduje zwiększenie fizycznej wielkości oraz pojemności bazy
danych, która nie tylko zostaje rozproszona na dodatkowych urządzeniach
dyskowych, ale może też pomieścić więcej danych.
Segmenty i obszary tabel
Wydajność dużej bazy danych można zwiększyć przez rozproszenie jej na kilku
różnych napędach dyskowych i przypisanie wyznaczonym napędom określonych
obiektów bazy. W serwerach SQL firm Microsoft i Sybase używamy do tego celu
tzw. segmentów (segments) bazy danych. Umieszczenie tabel na jednym napędzie,
a jej indeksu na innym może przyczynić się do znacznego zwiększenia wydajności,
zwłaszcza jeśli napędy są sterowane przez oddzielne kontrolery dyskowe.
A oto, w jaki sposób możemy rozdzielić indeksy i tabele w serwerze Sybase
(począwszy od wersji System 10):
1. Utworzyć nowe dowiązanie do napędu fizycznego (w serwerze SQL określa się
je jako urządzenie - device) za pomocą komendy DISK INIT:
DISK INIT
name= INDDEV
physname= SYS:DATA\INDDEV.DAT ,
vdevno=25,
size=51200
2. Rozszerzyć bazę danych na powyższy napęd za pomocą komendy ALTER
DATABASE:
ALTER DATABASE ORDENT
ON INDDEV=100
3. Utworzyć nowy segment na nowo przydzielonym urządzeniu, za pomocą
komendy sp_addsegment:
sp_addsegment  indexeseg , ORDENT , INDDEV
gdzie indexseg jest nazwą nowego segmentu, ORDENT jest nazwą bazy
danych, a INDDEV - nazwą nowo dodanego urządzenia.
672 Część IV
4. Utworzyć obiekt bazy danych w nowo utworzonym segmencie, np. wpisując
wyrażenie:
CREATE INDEX INVOICES02 ON INVOICES (CustomerNumber) ON
Ą' indexeseg
Tabela INVOICES i indeks INVOICES02 znajdują się teraz w różnych
segmentach i, mamy nadzieję, na różnych napędach, minimalizując tym samym
zakres ruchów głowicy dysku przy dostępie do tabeli za pośrednictwem indeksu
INVOICES02.
W systemie Oracle to samo osiąga się przez założenie tzw. obszarów tabel
(tablespaces) na różnych napędach i utworzenie w ramach tych obszarów
obiektów bazy danych. Nowy obszar tabel tworzymy używając komendy CREATE
TABLESPACE, a nowe obiekty przypisujemy mu za pomocą komend CREATE
TABLE i CREATE INDEX, na przykład wyrażenia:
CREATE TABLESPACE indexspace
DATAFILE  c:\oracle\indexspace.dat SIZE 500K REUSE
AUTOEXTEND ON NEXT 500K MAXSIZX 10m.;
tworzą obszar tabel, a wyrażenie:
CREATE INDEX emp02 ON emp (ename, job) TABLESPACE indexspace;
tworzy znajdujący się w nim indeks. Ponieważ obszarom tabel przydziela się
konkretny plik, indeks zostaje w efekcie przypisany temu właśnie plikowi. Jeżeli
plik ten znajduje się na innym napędzie, niż sama tabela, oznacza to, że została ona
oddzielona od indeksu. Powinno to zlikwidować wąskie gardła spowodowane
dużym obciążeniem urządzeń wejścia/wyjścia.
Właściwa praktyka projektowania baz danych wymaga rozważenia celowości
segmentacji, zanim przystąpimy do konstruowania obiektów bazy, ponieważ
póz
niej nie będzie można ich łatwo przemieścić. A oto trzy reguły, które należy
wziąć pod uwagę przy obmyślaniu sposobu segmentacji bazy danych:

protokoły transakcji/wycofań (transaction/redo logs) przechowywać na innych
urządzeniach, niż dane. Przechowywanie protokołów na tym samym urządzeniu
fizycznym jest nie tylko niebezpieczne w przypadku jego awarii, ale znacznie
pogarsza wydajność - ze względu na ,,konkurencję przy próbach dostępu do
protokołu z jednej, a do danych z drugiej strony.

Oddzielać tabele od ich zewnętrznych indeksów (non-clustered indexes)
wszędzie, gdzie to możliwe. Najprostszym sposobem jest umieszczenie na
jednym dysku wszystkich tabel bazy danych, a wszystkich indeksów - na
drugim. Zauważmy, że indeksu wewnętrznego (clustered index) tabel nie
możemy umieścić na innym urządzeniu, niż to, na którym znajduje się sama
Rozdział 24 Zaawansowane programowanie w SQL 673
tabela. Jeżeli spróbujemy to zrobić, to tabela ,,pójdzie śladami swojego
indeksu wewnętrznego do segmentu, który dla niego podano (indeksy
wewnętrzne omawiamy dalej w podrozdziale  Indeksy wewnętrzne
a zewnętrzne ).
tabele umieszczać na różnych napędach, zwłaszcza jeżeli mają być one
Duże
jednocześnie używane. Jeśli baza danych ma trzy duże tabele i wiele małych,
prawidłowa konfiguracja urządzeń obejmuje przynajmniej osiem napędów: po
jednym dla każdej dużej tabeli, jeden dla małych tabel i po jednym napędzie
z indeksem dla każdego napędu z tabelą/tabelami.
Platforma InterBase nie obsługuje segmentacji jako takiej, ale i w niej bazę danych
możemy rozproszyć pomiędzy kilka napędów dyskowych. Niemożliwe jest
natomiast przypisanie konkretnym napędom konkretnych obiektów bazy danych.
Indeksy
Czytelnik na pewno już domyślił się, że indeksy tworzymy w języku SQL
używając komendy CREATE INDEX. Niżej podajemy podstawową składnię tej
komendy:
CREATE INDEX INVOICES02 ON INVOICES (CustomerNumber)
gdzie INVOICES02 oznacza nazwę nowego indeksu, INVOICES jest nazwą
tabeli, dla której tworzymy indeks, a CustomerNumber oznacza klucz indeksu.
W systemie InterBase nazwy indeksów muszą być unikalne w całej bazie danych.
W systemach Sybase i Microsoft SQL Server muszą one być unikalne tylko
w ramach każdej tabeli z osobna.
Indeksy unikalne
Unikalne indeksy tworzymy używając komendy w wersji CREATE UNIQUE
INDEX, na przykład:
CREATE UNIQUE INDEX INVOICES01 ON INVOICES (InvoiceNo)
Indeksy w porządku malejącym
Klucze indeksu są domyślnie uporządkowane rosnąco. Indeksy uporządkowane
malejąco możemy utworzyć w dialekcie InterBase z użyciem komendy
DESCENDING, na przykład:
CREATE DESCENDING INDEX INVOICES03 ON INVOICES (Amount)
Dzięki temu szybciej będą przetwarzane zapytania w rodzaju:
674 Część IV
SELECT * FROM INVOICES
ORDER BY Amount DESCENDING
Włączanie i wyłączanie indeksu
W InterBase można czasowo wyłączyć (deactivate) indeks, a potem znów go
włączyć (activate) i przebudować. W czasie, gdy indeks jest wyłączony, do tabeli
można dodać wiele wierszy bez uaktualniania go za każdym razem, gdy dodawany
jest nowy wiersz. A oto odpowiednie wyrażenie:
ALTER INDEX INVOICES03 INACTIVE
Indeks włącza się bardzo podobnie, z tym że słowo INACTIVATE trzeba zastąpić
przez ACTIVATE:
ALTER INDEX INVOICES03 ACTIVE
Wyłączenie i włączenie indeksu powoduje jego przebudowę. Jeśli chcemy
wyłączyć indeks, a jest on właśnie używany, to wyłączenie opóz
ni się do chwili
jego zwolnienia.
Generatory i sekwencje
Większość platform DBMS oferuje środki automatycznej inkrementacji kolumny.
Pracując z relacyjnymi bazami danych często napotykamy konieczność
przetwarzania ciągów pól w kolumnie, stąd też większość producentów DBMS
realizuje automatyzację inkrementacji już na poziomie systemowym.
W platformach Sybase and Microsoft autoinkrementację kolumny uzyskuje się
poprzez atrybut identity. Kolumny typu identity (czyli liczniki) nie różnią się
od zwykłych, z tym wszakże wyjątkiem, że system wpisuje do nich wartości
w porządku rosnącym. Następujący przykład ilustruje definiowanie kolumn typu
identity:
CREATE TABLE CUSTOMER
(
CustomerNumber numeric(18) NOT NULL identity,
LastName char(30) NOT NULL,
FirstName char(30) NOT NULL,
StreetAddress char(30) NOT NULL,
City char(20) NOT NULL,
State char(2) NOT NULL,
Zip char(10) NOT NULL
)
Rozdział 24 Zaawansowane programowanie w SQL 675
Chociaż nie gwarantuje się, że wartości te będą następowały ściśle jedna po
drugiej, to na pewno będą w porządku rosnącym.
OSTRZEŻENIE:
Tworzenie indeksów (zwłaszcza wewnętrznych) za pomocą pól autoinkremen-
towanych może spowodować problemy ze współbieżnością, zarówno w platformie
Sybase, jak i Microsoft SQL Server. Jest tak dlatego, że w obu platformach
wykorzystuje się model blokowania stronami (firma Microsoft wprowadziła
ostatnio blokowanie wierszami, ale sprawia ono jeszcze problemy). Implementacje
pesymistyczne, oparte na blokowaniu stronami, mają swoje zalety, ale jedną z ich
głównych wad jest możliwość wzajemnego blokowania niezwiązanych ze sobą
transakcji, w których modyfikuje się wiersze znajdujące się na tej samej stronie.
Oznacza to, że np. uaktualnienie pod adresem CustomerNumber 1000 może
zablokować uaktualnienie pod adresem CustomerNumber 1001 - tylko dlatego,
że oba wiersze znajdują się na tej samej stronie bazy danych. Jeżeli indeks
wewnętrzny tworzy się według bardziej przypadkowego kryterium, np. pola
StreetAddress z naszego przykładu, to prawdopodobieństwo wystąpienia
zatoru (deadlock) jest mniejsze. Problem ten nie występuje w platformach DBMS
z blokowaniem niskopoziomowym (takich jak Oracle).
Autoinkrementację kolumny w InterBase umożliwiają generatory. Żeby utworzyć
pole autoinkrementowane, trzeba najpierw utworzyć generator, tak jak
w poniższym przykładzie:
CREATE GENERATOR CustomerNumberGen
Po utworzeniu generatora możemy go wykorzystać jako procedurę zdarzenia
(trigger), wstawiającą wartości do kolumny:
SET TERM!!;
CREATE TRIGGER CUSTOMERInsert FOR CUSTOMER
BEFORE INSERT
POSITION 0
AS BEGIN
NEW.CustomerNumber = GEN_ID(CustomerNumberGEN, 1);
END
SET TERM ; !!
W systemie Oracle przyjęto podobne podejście, z tym że używa się nim sekwencji.
Tworzy się je w sposób następujący:
CREATE SEQUENCE CustomerSEQ;
676 Część IV
Podobnie jak w InterBase, utworzony obiekt sekwencji można wykorzystać przy
wpisywaniu wartości w wyrażeniach dialektu PL/SQL, używanego w systemie
Oracle.
Perspektywy
Perspektywy (views) składają się ze skompilowanych wyrażeń SELECT, do
których można kierować zapytania, tak jakby były tabelami. Perspektywa nie
przechowuje w rzeczywistości żadnych danych; składa się tylko ze
skompilowanych wyrażeń języka SQL. Perspektywy podobne są do procedur
pamiętanych (stored procedures), zwłaszcza procedur wyboru (select procedures).
Kiedy do perspektywy kieruje się zapytanie, serwer łączy wyrażenie SELECT,
użyte do utworzenia perspektywy, z wyrażeniem opisującym zapytanie i realizuje
połączone zapytanie dla tabel, których dotyczy perspektywa.
Perspektywy budujemy za pomocą komendy CREATE VIEW, zgodnie
z przykładowym listingiem 24.1.
Listing 24.1 Przykład zastosowania komendy CREATE VIEW
CREATE VIEW NEWLEASES AS
SELECT *
FROM LEASE
WHERE MovedInDate > 12/01/96
Po utworzeniu perspektywy można do niej kierować zapytania, tak jakby była
tabelą, co zilustrowano za pomocą poniższego wyrażenia:
SELECT * FROM NEWLEASES
Wyrażenia SELECT możemy używać w perspektywach z jednym ograniczeniem -
mianowicie takim, że nie może w nim wystąpić klauzula ORDER BY.
Ograniczenie to dotyczy większości platform DBMS typu klient/serwer,
w szczególności produktów InterBase, Oracle, Sybase oraz Microsoft SQL Server.
Dodatkowe ograniczenia dotyczą tzw. perspektyw aktualizowanych (updatable
views). W systemie InterBase perspektywa aktualizowana musi spełniać
następujące wymagania:

Wyrażenie SELECT musi odnosić się do jednej tabeli lub innej perspektywy
aktualizowanej.
ma być obsługiwana komenda INSERT, kolumny, które nie mają być
Jeśli
uwzględniane, muszą dopuszczać wartości NULL.
są obsługiwane zapytania podrzędne (subqueries), predykat DISTINCT,
Nie
klauzula HAVING, funkcje sumaryczne (aggregate functions), tabele dołączone
Rozdział 24 Zaawansowane programowanie w SQL 677
(joined tables), funkcje zdefiniowane przez użytkownika i wyrażenia SELECT
z procedur pamiętanych.
Żeby wiersze wstawiane lub uaktualniane poprzez perspektywę aktualizowaną na
pewno spełniły narzucane w niej kryteria selekcji, należy - w komendzie CREATE
VIEW - użyć klauzuli WITH CHECK OPTION. Klauzula ta sprawia, że
perspektywa odmówi realizacji uaktualnień lub wstawień, w których wyniku
powstałby wiersz nie spełniający kryteriów selekcji. A oto przykładowy kod:
CREATE VIEW OKPROPERTY AS
SELECT *
FROM PROPERTY
WHERE State= OK
WITH CHECK OPTION
W systemach Microsoft i Sybase perspektywy aktualizowane są nieco
elastyczniejsze, gdyż można się w nich odwoływać do wielu tabel naraz. Jednak
i tu mamy wiele ograniczeń. Niżej podajemy te ograniczenia, które muszą spełniać
perspektywy w systemie SQL Server:
wyrażeniu SELECT perspektywy nie dopuszcza się klauzul ORDER BY
W
i COMPUTE, słowa kluczowego INTO oraz operatora UNION.

Perspektyw nie można tworzyć dla tabel tymczasowych (temporary tables).

Procedur zdarzeń (trigger) i indeksów nie można tworzyć w oparciu
o perspektywy.
perspektywach nie można używać procedur pisania (writetext) i czytania
W
(readtrext) w odniesieniu do kolumn z tekstami i obrazami.
Perspektywy aktualizowane muszą spełniać dodatkowe ograniczenia:

Nie obsługuje się komendy DELETE w perspektywie dotyczącej wielu tabel.
perspektywa obsługiwała komendę INSERT, nieuwzględniane kolumny
Żeby
tabeli muszą dopuszczać wartość NULL.

Komenda INSERT nie jest obsługiwana w perspektywach zawierających
kolumny wyliczane (calculated columns).

Komendy INSERT nie obsługuje się w perspektywach dotyczących wielu
tabel, które utworzono z użyciem przełączników DISTINCT lub WITH
CHECK OPTION.

Komendy UPDATE nie obsługuje się w perspektywach utworzonych z użyciem
przełącznika DISTINCT.

Podczas aktualizowania perspektywy dotyczącej wielu tabel wszystkie
uaktualniane kolumny muszą należeć do tej samej tabeli.
678 Część IV

Nie można uaktualniać kolumn wyliczanych.

Perspektywy z funkcjami sumarycznymi nie mogą być aktualizowane.
Perspektywy dynamiczne
Po wywołaniu perspektywy serwer wykonuje tworzący ją program w języku SQL.
Chociaż dane zwracane w wyniku zapytania mogą się bardzo istotnie zmieniać,
kryteria w wyrażeniu SELECT przetwarzanym przez serwer nie ulegają zmianie.
Perspektywa dynamiczna (dynamic view) realizuje różniące się w kolejnych
wywołaniach wyrażenia SELECT, w zależności od warunków w chwili
wywołania. Powróćmy na chwilę do perspektywy z listingu 24.1. Ograniczono
w niej zwracane posiadłości do znajdujących się w Oklahomie. Kryteria
przekazywane do serwera przez tę perspektywę są zawsze takie same (chociaż
w wyniku kolejnych wywołań może ona zwracać różne wiersze) - jest to bowiem
perspektywa statyczna (static view). Kryteria przekazywane do serwera przez
perspektywę dynamiczną mogą się zmieniać w zależności od zewnętrznych
warunków istniejących w chwili jej wywołania. Ilustruje to poniższy przykład
napisany w dialekcie Transact-SQL platform Sybase/Microsoft:
CREATE VIEW CONTACTLISTV
SELECT * FROM CONTACTLIST
WHERE EnteredBy=suser_name()
Kolumna EnteredBy tabeli CONTACTLIST zawiera nazwy użytkowników
wprowadzających nowy rekord. Za każdym razem, gdy dodaje się nowy rekord,
pole to jest automatycznie wypełniane dzięki wykorzystaniu parametru DEFAULT.
Za każdym razem, gdy użytkownik aplikacji dodaje rekord, wartość
suser_name() zostaje zapamiętana w odpowiednim polu rekordu. Dzięki temu
można ograniczyć widzianą przez użytkownika perspektywę całej tabeli
CONTACTLIST do rekordów, które wprowadził. Ponieważ kryteria
przeszukiwania dla serwera zmieniają się w zależności od tego, kto jest aktualnym
użytkownikiem aplikacji, jest to zachowanie dynamiczne.
Perspektywy a prawa dostępu
Użytkownik nie musi mieć praw do tabel uwzględnianych w perspektywie, żeby
z niej skorzystać. Perspektyw bowiem i ich tabel dotyczą oddzielne zezwolenia.
Wyjątkiem od tej reguły jest konieczność posiadania odpowiednich praw dostępu
do tabel uwzględnianych w perspektywach przez użytkowników, którzy tworzą
perspektywy.
Jeśli użytkownik ma prawo dostępu, umożliwiające mu wykonanie jakiejś operacji
na perspektywie, uzyskuje on poprzez nią dostęp do jej tabel - nawet jeśli nie ma
on identycznych praw do tych tabel !. Znamienne, że nawet sama firma Sybase
Rozdział 24 Zaawansowane programowanie w SQL 679
zaleca taki sposób używania perspektyw, jako zapewniający dobrą ochronę
danych.
Z kilku powodów nie jest to jednak dobrą praktyką. Umieszczając
w perspektywach część zezwoleń, uniemożliwiamy zarządzanie naszym systemem
ochrony z punktu centralnego - nie widzimy wówczas z jednego miejsca
wszystkich praw dostępu. Całościowy obraz praw dostępu w systemie uzyskamy
dopiero po sprawdzeniu uprawnień za pomocą komend GRANT i REVOKE oraz
przejrzeniu naszych tabel systemowych. Co więcej, narzędzia do administrowania
przeważnie nie pozwalają stwierdzić, że dany użytkownik nie ma uprawnień do
tabeli, do której uzyskał dostęp poprzez perspektywę. W związku z tym zaleca się
ograniczać uprawnienia użytkownika, nadając mu zezwolenia do tabel, a nie
perspektyw, wszędzie gdzie to możliwe.
WSKAZÓWKA:
Perspektywy umożliwiają wygodne przygotowanie danych do eksportu. Większość
platform DBMS typu klient/serwer obsługuje zbiorczy import/eksport danych.
W systemie Oracle służą do tego celu programy EXPORT i IMPORT, podczas gdy
w systemach Sybase i Microsoft SQL Server - program BCP (Bulk Copy
Program). Jeżeli z platformy SQL Server eksportujemy w formacie ASCII dane
z datami (datetime fields), możemy natknąć się na problem polegający na omijaniu
przez program BCP części pól daty w eksportowanych tabelach. Rozwiązanie
polega na utworzeniu perspektyw dla tych tabel i takim przeformatowaniu pól
daty, żeby zawierały one kompletną informacje o dacie/godzinie. Perspektywy te
można następnie wyeksportować, dzięki czemu żadne informacje nie zostaną
utracone przez program BCP w trakcie przekształcania na format ASCII danych
z serwera.
Procedury pamiętane
Procedury pamiętane (stored procedures) są to skompilowane programy w języku
SQL. Przeważnie składają się z wielu wyrażeń tego języka, a przechowuje się je
w bazie danych razem z innymi jej obiektami. Wyróżniamy dwa typy procedur
pamiętanych: procedury wyboru (select procedures) i procedury wykonywalne
(executable procedures). Procedura wyboru może wystąpić zamiast tabeli
w wyrażeniu SELECT. Procedury wykonywalne natomiast można uruchamiać;
mogą one (ale nie muszą) zwracać dane.
Procedury pamiętane tworzymy za pomocą komendy CREATE PROCEDURE.
Poniżej przykład w dialekcie InterBase:
CREATE PROCEDURE CLEARCALLS
AS
680 Część IV
BEGIN
DELETE FROM CALLS WHERE CallDateTime < 01/01/96 ;
END
A oto ta sama procedura w dialekcie Transact-SQL (SQL Server):
CREATE PROCEDURE CLEARCALLS
AS
BEGIN
DELETE FROM CALLS WHERE CallDateTime < 01/01/96
Jeżeli do procedury przekazuje się parametry, to jej konstrukcja jest trochę inna.
W InterBase definiujemy ją następująco:
CREATE PROCEDURE CLEARCALLS (BeginningDate DATE)
AS
BEGIN
DELETE FROM CALLS WHERE CallDateTime <:BeginningDate;
END
... natomiast w SQL Server:
CREATE PROCEDURE CLEARCALLS (@BeginningDate datetime)
AS
DELETE FROM CALLS WHERE CallDateTime < @BeginningDate
Procedury wyboru
W procedurach wyboru dane, które mają zostać zwrócone do wywołującego,
definiuje się z użyciem słowa kluczowego RETURNS, np:
CREATE PROCEDURE LISTPROP (State char(2))
RETURNS (PROPERTYNO INTEGER,
ADDRESS VARCHAR(30))
AS
BEGIN
FOR SELECT PropertyNo, Address
FROM PROPERTY
WHERE State = :State
INTO :PropertyNo, :Address
DO
SUSPEND;
END
Konstrukcja FOR SELECT...DO służy do zwracania wyników działania
procedury, a SUSPEND - do wstrzymywania działania procedury, do chwili
zażądania następnego wiersza przez wywołującego. Parametry wyjściowe zwraca
się przed wstrzymaniem działania.
Rozdział 24 Zaawansowane programowanie w SQL 681
Dobrą praktyką jest przechowywanie kodu z
ródłowego procedur pamiętanych
w plikach ze skryptami SQL. Skrypt można utworzyć za pomocą praktycznie
dowolnego edytora tekstów. Nie zapomnijmy wpisać do niego wszystkich
koniecznych wyrażeń CONNECT i SET TERM - potem będzie je można wykonać,
używając opcji Run an SQL script edytora WISQL. Przykład skryptu
przedstawia listing 24.2.
Listing 24.2 Skrypt SQL z procedurą pamiętaną
CONNECT  C:\DATA\ RENTMAN\RENTMAN.GDB ESER SYSDBA PASSWORD
Ą' masterkey;
SET TERM^;
/* Stored procedures*/
CREATE PROCEDURE LISTPROP
RETURNS (PROPERTYNO INTEGER, ADDRESS VARCHAR (30))
AS
BEGIN
FOR SELECT PropertyNo, Address
FROM PROPERTY
INTO :PropertyNo, :Address
DO
SUSPEND;
END
^
SET TERM ;^
Wyrażenie CONNECT u góry skryptu nawiązuje połączenie z bazą danych.
Komenda SET TERM czasowo zmienia separator wyrażeń SQL - z domyślnego
znaku średnika (;) na znak ^. Dzięki temu komendy zawarte w definicji procedury
pamiętanej nie wykonują się w trakcie działania komendy CREATE PROCEDURE.
Po realizacji tej ostatniej komendy, SET TERM zostanie znowu wywołana w celu
przywrócenia domyślnego separatora komend SQL.
Procedury wykonywalne
Procedury wykonywalne różnią się trochę od procedur wyboru, gdyż wyrażenie
RETURNS nie jest w nich obligatoryjne. A oto przykład procedury wyboru
w dialekcie platformy InterBase:
CREATE PROCEDURE insertWORKTYPE (WorkTypeCode smallint,
Ą' Description char (30), TaskDuration float)
AS
BEGIN
INSERT INTO WORKTYPE VALUES (:WorkTypeCode,
:Description, :TaskDuration);
END
Ta sama procedura w dialekcie Transact-SQL wygląda następująco:
682 Część IV
CREATE PROCEDURE insertWORKTYPE (@WorkTypeCode smallint,
Ą' @Description char (30), @TaskDuration float)
AS
INSERT INTO WORKTYPE VALUES (@WorkTypeCode,
Ą' @Description, @TaskDuration)
Funkcje pamiętane
Zarówno SQL Server, jak i Oracle udostępniają możliwość tworzenia
podprogramów, które zwracają wartości i które są podobne do funkcji
w tradycyjnych językach programowania. W języku PL/SQL systemu Oracle
procedury takie nazywamy właśnie funkcjami. Niżej podajemy przykładową
definicję funkcji:
CREATE FUNCTION get_bal(CustomerNo IN NUMBER)
RETURN NUMBER
IS
custbal NUMBER(11,2);
BEGIN
SELECT balance
INTO custbal
FROM CUSTOMER
WHERE CustomerNo = get_bal.CustomerNo;
RETURN(custbal);
END
SQL Server jest trochę bardziej rygorystyczny pod względem formalnym, ale też
i składnia jego funkcji bardziej przypomina tradycyjne języki programowania.
Poniżej przedstawiono przykładowy kod w systemie Sybase:
CREATE PROCEDURE get_bal @CustomerNo int, @Balance money=NULL
OUTPUT
AS
SELECT @Balance = Balance
FROM CUSTOMER
WHERE CustomerNo = @CustomerNo
RETURN 100 * @Balance  Convert to integer
Powyższa procedura funkcyjna zwraca bilans klienta zarówno za pomocą
wyrażenia RETURN, jak i poprzez zmienną wyjściową @Balance. Wartości
zwracane poprzez RETURN muszą być typu całkowitego (integer), stąd też
zmienną Balance mnoży się przez 100 - aby zapewnić, że część ułamkowa
bilansu nie zostanie obcięta.
Podprogram taki wywołuje się na kilka sposobów. Pierwszy z nich jest bardzo
podobny do tradycyjnego wywołania funkcji:
declare @mybal money
select @mybal = null
Rozdział 24 Zaawansowane programowanie w SQL 683
exec @mybal = get_bal @CustomerNo=3 - Supply a customer
number,  3 in this case
select @mybal / 100
Zauważmy, że zmienną, która przechowuje zwróconą wartość, dzieli się przez 100.
Drugi sposób wywoływania procedur funkcyjnych w systemie SQL Server
przypomina wywołanie zwykłej procedury pamiętanej, na przykład:
declare @mybal money
select @mybal = null
exec get_bal @CustomerNo=3, @mybal OUTPUT
select @mybal
Zwróćmy uwagę na słowo kluczowe OUTPUT, które wskazuje, że do jego
parametru kieruje się wartość zwracaną przez procedurę. Zauważmy również, że
dzielenie przez 100 nie jest tu konieczne, gdyż SQL Server sam zamienia wartość
całkowitą na daną, reprezentującą walutę.
Pakiety
Platforma Oracle umożliwia grupowanie w bazie danych procedur pamiętanych,
funkcji i innych, powiązanych obiektów pod postacią tzw. pakietów (packages).
Dzięki pakietom można dodawać, usuwać i modyfikować wiele procedur za
jednym razem, co bardzo upraszcza aktualizacje całych ich grup. Poniżej składnia
definicji pakietu w systemie Oracle:
CREATE OR REPLACE PACKAGE CustomerPKG AS
FUNCTION addcust(CustomerName VARCHAR2, Address
VARCHAR2,
City VARCHAR2, State CHAR(2), Zip CHAR(10))
RETURN NUMBER;
PROCEDURE deletecust(CustomerNumber NUMBER);
Invalid_State EXEPCION;
Invalid_City EXEPCION;
END CustomerPKG
Podobnie jak w Delphi, gdzie sekcję Interface modułu oddziela się od sekcji
Implementation, nagłówek pakietu (czyli jego interfejs) definiuje się oddzielnie od
jego części implementacyjnej (body). Wyrażenie CREATE PACKAGE opisuje
publiczny interfejs pakietu. Interfejs publiczny jest widziany przez podprogramy
zewnętrzne i udostępnia im wewnętrzne obiekty pakietu. Z kolei wyrażenia
PACKAGE BODY używamy przy specyfikacji części implementacyjnej pakietu,
tak jak w poniższym przykładzie:
CREATE OR REPLACE PACKAGE BODY CustomerPKG AS
tot_custs NUMBER;
684 Część IV
FUNCTION addcust(CustomerName VARCCHAR2, Address
VARCHAR2,
City VARCHAR2, State CHAR(2), Zip CHAR(10))
RETURN NUMBER is NewCustomerNo NUMBER(4);
BEGIN
IF State IS NULL THEN RAISE Invalid_State;
IF City is NULL THEN RAISE Invalid_City;
SELECT CustomerSEQ.NEXTVAL
INTO NewCustomerNo
FROM DUAL;
ISERT INTO CUSTOMER (CustomerNo, CustomerName, Address,
Ą' City, State, Zip)
VALUES (NewCustomerNo, CustomerName, Address, City,
Ą' State, Zip);
tot_custs := tot_custs +1;
RETURN(NewCustomerNo);
END;
PROCEDURE deletecust(CustomerNumber NUMBER) IS
BEGIN
DELETE FROM CUSTOMER
WHERE CUSTOMER.CustomerNumber = deletecust.
Ą' CustomerNumber;
tot_custs := tot_custs - 1;
END;
END CustomerPKG
Przewagą pakietów nad tradycyjnymi skryptami (obsługiwanymi przez pozostałe
platformy) jest możliwość ich ciągłego rozwijania, nawet jeśli korzystają z nich
inni programiści. Przy założeniu, że nie zmieniamy w sposób istotny interfejsu
naszego pakietu, inni mogą wywoływać w swoich programach procedury
publiczne, podczas gdy my możemy modyfikować część implementacyjną. Tak
więc nawet przy częstych zmianach części implementacyjnej nagłówek
praktycznie pozostaje taki sam, co bardzo ułatwia projektowanie dużych aplikacji
w języku SQL.
Wyjątki
Wyjątki są obsługiwane w platformach InterBase, Oracle i Sybase. W InterBase
nowy wyjątek definiuje się za pomocą komendy CREATE EXCEPTION, a zgłasza
(throw, raise) go - za pomocą komendy EXCEPTION. W systemie Oracle wyjątek
użytkownika definiuje się z użyciem konstrukcji DECLARE ... EXCEPTION,
a do zgłoszenia go używa komendy RAISE. Sybase nie oferuje możliwości
definiowania wyjątków użytkownika, ale komenda RAISERROR umożliwia
skorzystanie z wyjątków systemowych. Można tu również tworzyć komunikaty do
póz
niejszego wykorzystania z komendą RAISERROR i skojarzyć je ze zdarzeniami
Rozdział 24 Zaawansowane programowanie w SQL 685
związanymi z błędami systemowymi. Poniżej przedstawiliśmy wyjątek w systemie
InterBase, wraz z procedurą, która go zgłasza.
/* The exception*/
CREATE EXCEPTION CREDIT_TOO_HIGH
 The fequested credit is too high. All Casino advances must
be less than $5000 ;
/*The SQL script for a procedure that uses the exception*/
CONNECT  C:\DATA\IB\CASINO ;
SET TERM^;
CREATE PROCEDURE insertCREDIT (VoucherNumber smallint,
Ą' VoucherDate date,
CustomerNumber int, Amount float)
AS
BEGIN
IF (:Amount>5000) THEN
EXCEPTION CREDIT_TOO_HIGH;
ELSE
INSERT INTO CREDIT
VALUES (:VoucherNumber, :VoucherDate,
Ą' :CustomerNumber, :Amount);
END^
SET TERM ;^
EXIT;
Przedstawiona wyżej procedura ogranicza realizacje kredytu w kasynie do 5000
dolarów. Za pomocą konstrukcji IF...THEN sprawdzamy, czy wartość pola
Amount w wierszu, który właśnie ma zostać wstawiony, jest większa niż 5000.
Jeśli tak, to komenda EXCEPTION zgłasza wyjątek CREDIT_TOO_HIGH
(kredyt za wysoki).
Teraz z kolei analogiczny przykład w dialekcie systemu SQL Server (zgodnie
z wcześniejszymi zaleceniami napisano go jako oddzielny skrypt):
sp_addmessage 20001,
 The requested credit is too high. All Casino advances must
be less than $5000.
GO
use CASINO
GO
CREATE PROCEDURE insertCREDIT (@VoucherNumber smallint,
Ą' @VoucherDate date, @CustomerNumber int, @Amount float)
AS
IF @Amount > 5000
RAISERROR 20001
ELSE
INSERT INTO CREDIT
VALUES (@VoucherNumber, @VoucherDate, CustomerNumber,
Ą' @Amount)
GO
686 Część IV
SQL Server pozwala na umieszczanie parametrów w tekstach komunikatów (por.
kody konwersji i funkcje printf w języku C oraz format w Delphi). Tak więc
komunikat i procedurę można skonstruować w sposób następujący:
sp_addmessage 50010,
 The requested credit of %1 is too high. All Casino advances
must be less than $5000.
GO
use ORDENT
GO
CREATE PROCEDURE insertCREDIT (@VoucherNumber smallint,
Ą' @VoucherDate date, @CustomerNumber int, @Amount float)
AS
IF @Amount > 5000
RAISERROR 50010, @Amount
ELSE
INSERT INTO CREDIT
VALUES (@VoucherNumber, @VoucherDate, CustomerNumber,
Ą' @Amount)
GO
Zwróćmy uwagę na parametr formalny %1. W Sybase dopuszcza się maksymalnie
20 ponumerowanych parametrów komunikatu. Przyjrzyjmy się również
zmodyfikowanemu wywołaniu komendy RAISERROR. Zmienna @Amount jest
tutaj jej drugim parametrem. Pomimo, iż łańcuch opcjonalny komunikatu
występuje zwykle jako jej drugi parametr, wykrywa ona, że przekazywany
komunikat zdefiniowany jest przez użytkownika, w związku z czym używa
pozostałych argumentów jako parametrów komunikatu.
Sybase daje również możliwość powiązania (bind) komunikatu zdefiniowanego
przez użytkownika ze zdarzeniem systemowym, takim jak naruszenie więzów,
czyli ograniczeń (constraint violation), zgodnie z poniższym przykładem:
ALTER TABLE INVOICES
ADD CONSTRAINT INVALID_CUSTOMER
FOREIGN KEY (CustomerNumber)
REFERENCES CUSTOMER(CustomerNumber)
GO
exec sp_addmessage 50010,  The customer number you ve
Ą' specified is not on file. Please specify a valid customer
Ą' number.
GO
exec sp_bindmsg INVALID_CUSTOMER,50010
GO
Zauważmy sposób użycia nazwy ograniczeń w wywołaniu sp_bindmsg. Dzięki
temu ustanowiony zostaje związek między więzami, utworzonymi z użyciem
Rozdział 24 Zaawansowane programowanie w SQL 687
komendy ALTER TABLE a komunikatem, utworzonym przez wywołanie
sp_addmessage.
Procedury zdarzeń
Procedura zdarzenia (trigger) to program wywoływany wówczas, gdy zajdzie
określone zdarzenie, związane z tabelami. Jest ona bardzo podobna do procedury
pamiętanej - do tego stopnia, że specjalne rozszerzenia języka SQL w InterBase
mają zastosowanie zarówno do procedur pamiętanych, jak i procedur zdarzeń.
Procedury zdarzeń wiążą się z takimi zdarzeniami jak wstawianie, aktualizacja
i usuwanie. A oto przykład takiej procedury w systemie InterBase:
CREATE TRIGGER DELETE_DETAIL FOR WORDERS
ACTIVE BEFORE DELETE
AS BEGIN DELETE FROM WODETAIL WHERE
WODETAIL.Work_Order_Number=OLD.Work_Order_Number;
END
Usuwa on rekordy związane z zamówieniem (work order) za każdym razem, gdy
zostaje usunięty ich rekord główny (master record). Określa się to nazwą
usuwania kaskadowego (cascading delete) - usunięcie z jednej tabeli uruchamia
kaskadę usunięć z innych, z użyciem wspólnego klucza.
Zwróćmy uwagę na zmienną kontekstową OLD - używamy jej do wyłuskania
wartości pola przed wykonaniem operacji UPDATE (uaktualnienie) lub DELETE
(usunięcie). Z kolei zmienna kontekstowa NEW wskazuje na nową wartości
kolumny w operacji INSERT (wstawienie) lub UPDATE.
Ważne jest słowo kluczowe BEFORE (zanim, przed); procedury zdarzeń mogą być
wyzwalane przed (before) operacjami INSERT, UPDATE i DELETE lub po
(after) nich.
W InterBase z danym zdarzeniem można powiązać maksymalnie 32 768 procedur
zdarzeń. Słowo kluczowe POSITION określa kolejność procedury. Kolejność ta
jest nieokreślona dla procedur zdarzeń oznaczonych tą samą wartością
POSITION. Przykład ze słowem kluczowym POSITION zamieszczono poniżej:
CREATE TRIGGER GENERATE_WORKTYPECODE FOR WORTYPE
ACTIVE BEFORE INSERT
POSITION 0
AS BEGIN
NEW.Work_Type_Code=GEN_ID(Work_Type_CodeGEN, 1);
END
Nieco inna jest składnia definicji procedur zdarzeń w systemach Sybase
i Microsoft SQL Server:
CREATE TRIGGER INVOICESDelete ON CUSTOMER
688 Część IV
FOR DELETE AS
DELETE FROM INVOICES WHERE INVOICES.CustomerNumber=(SELECT
Ą' CustomerNumber FROM deleted)
Tabela logiczna deleted służy do identyfikacji wiersza, który ma zostać
usunięty z tabeli procedur zdarzenia. W systemie SQL Server definiuje się również
tabele uaktualnień, które służą do analogicznego celu przy aktualizacji
i wstawianiu.
SQL Server udostępnia pożyteczny mechanizm ustalania, które pola uaktualnia się
w zapytaniach aktualizacji (update queries). Korzystamy przy tym z konstrukcji
IF UPDATE(columnname), wstawiając w miejsce columnname nazwę
kolumny w tabeli. Można więc ograniczyć działania procedury zdarzenia
w zależności od tego, które kolumny zostały zmodyfikowane.
Są dwa podstawowe rodzaje procedur zdarzeń: jednokrotne (statement trigger)
i wielokrotne (row trigger). Procedury zdarzeń jednokrotne wykonują się tylko
jeden raz dla danego wyrażenia DML - niezależnie od liczby wierszy, których
wyrażenie to dotyczy. Ten rodzaj procedur obsługiwany jest przez SQL Server.
Z drugiej strony Oracle umożliwia określenie, czy procedura jest jednokrotna, czy
też wielokrotna; służy do tego opcja FOR EACH ROW wyrażenia CREATE
TRIGGER. Aby ustrzec się błędów podczas projektowania, musimy upewnić się,
że dobrze rozumiemy obsługiwany przez naszą platformę typ (lub typy) procedur
zdarzeń.
Tabele
Aby dodać lub usunąć kolumny do tabel, korzystamy z komendy SQL ALTER
TABLE. Niektóre platformy, takie jak Microsoft SQL Server, nie umożliwiają
usuwania kolumn po fakcie; jest ono jednak obsługiwane w InterBase oraz Sybase.
A oto jak dodaje się kolumnę do tabeli:
ALTER TABLE CUSTOMER
ADD PhoneNumber char(10)
a usuwa się ją następująco:
ALTER TABLE CUSTOMER
DROP PhoneNumber
Do tabeli, która zawiera już wiersze, nie możemy dodać kolumny o atrybucie NOT
NULL, ponieważ wpisuje się do niej wartości NULL zaraz po jej dodaniu do
tabeli.
Rozdział 24 Zaawansowane programowanie w SQL 689
UWAGA:
W platformie Sybase SQL Server istnieje nieudokumentowana możliwość
usunięcia (drop) kolumny. Z możliwości tej należy korzystać bardzo ostrożnie,
pamiętając, że firma Sybase może ją w każdej chwili wycofać, jako
nieudokumentowaną oficjalnie w języku Transact-SQL.
Zaawansowane konstrukcje języka DML
Niniejszy podrozdział przedstawia zaawansowane konstrukcje języka
manipulowania danymi w SQL. Opisujemy tu składnię wyrażeń udostępnianych
przez różnych producentów na potrzeby dopasowania zapytań SQL do wymagań
użytkownika, jak również wiele innych zagadnień związanych z językiem DML.
Inaczej niż w przypadku języka DDL, budując wyrażenia DML musimy brać pod
uwagę kwestie wydajności. Nieprawidłowo skonstruowane wyrażenie DML może
w efekcie wygenerować zapytanie o rząd wielkości, wolniejsze od prawidłowego.
Komenda SELECT
Podstawowym narzędziem pracy w języku SQL jest komenda SELECT (wybierz).
Za pomocą komend SELECT możemy dołączać (join) tabele, zwracać (return)
dane, a nawet określać kryteria selekcji dla innych komend SELECT. Poniżej
wymieniliśmy niektóre z licznych zastosowań tej komendy.
Złączenia wielopoziomowe (Multi-Tier Joins)
Są to złączenia działające na więcej niż dwóch tabelach. Np. tabelę Table1 dołącza
się do Table2, a tabelę Table2 - do tabeli Table3. Złączenie wielopoziomowe
ilustruje następujący przykład:
SELECT w.Work_Order_Number, t.Name
FROM WORDER w, LEASE 1, TENANT t
WHERE w.Property_Number=1.Property_Number
and 1.Tenant_Number=t.Tenant_Number
W zapytaniu tym tabele WORDER i LEASE łączy się według kolumn
Property_Number, a tabele LEASE i TENANT - według kolumn
Tenant_Number. W efekcie wszystkie tabele zostają połączone i tworzą jeden
zbiór wynikowy.
690 Część IV
Złączenia zwrotne (Self-Joins)
Tabelę można również dołączyć do samej siebie. Taki typ złączenia nazywamy
złączeniem zwrotnym (self-join lub reflexive join). Oto przykładowe zapytanie ze
złączeniem zwrotnym:
SELECT I.CustomerNumber, I.Amount, (I.Amount /
SUM(i2.Amount))*100 Percentage
FROM INVOICES I
INVOICES I2
WHERE I.CustomerNumber=I2.CustomerNumber
GROUP BY I.CustomerNumber, I.Amount
Zapytanie to wyświetla faktury każdego klienta oraz udział procentowy,
reprezentowany przez daną fakturę względem wszystkich jego faktur. Złączenie
zwrotne to jedyny sposób wygenerowania tego rodzaju informacji w jednym
przebiegu. Dane każdego klienta są zbierane i grupowane, zgodnie
z oczekiwaniami, po czym tabela INVOICES zostaje zwrotnie dołączona do samej
siebie, aby umożliwić uzyskanie sumy dla faktur każdego klienta. Wartość dla
każdej faktury zostaje następnie podzielona przez tę sumę i przekształcona na
wartość procentową do umieszczenia w zbiorze wynikowym.
Złączenia z użyciem innych operatorów (theta joins)
Złączenie theta (theta joins) łączy dwie tabele przy użyciu operatorów nierówności
(takich jak operator <>). Oto przykład:
SELECT C.CustomerNumber, O.Amount, Sum(02.Amount)OTHERS
FROM CUSTOMER C,
INVOICES 0,
INVOICES 02
WHERE C.CustomerNumber=0.CustomerNumber
AND C.CustomerNumber<>02.CustomerNumber
GROUP BY C.CustomerNumber, 0.Amount
Zapytanie powyższe składa się z dwóch złączeń. Pierwsze łączy tabele
CUSTOMER i INVOICES i zwraca faktury każdego klienta. Drugie - to złączenie
theta, które zwraca sumę dla wszystkich faktur nie należących do danego klienta.
Ponieważ występuje tu łączenie z użyciem dwóch różnych typów złączeń,
musieliśmy skorzystać z dwóch różnych nazw zastępczych (aliasów, aliases) tabeli
INVOICES.
Złączenia kartezjańskie - iloczyn kartezjański
Złączenia kartezjańskiego (Cartesian join) używamy do budowania zbiorów
wynikowych, które zawierają wszystkie wiersze z jednej tabeli połączone ze
wszystkimi wierszami innej. Poniżej przykład takiego złączenia:
Rozdział 24 Zaawansowane programowanie w SQL 691
SELECT t.Name, w.Description
FROM TENANT t, WORKTYPE w
Iloczyn kartezjański jest przeważnie efektem błędu w klauzuli złączenia. Czasem
jednak może on okazać się pożyteczny przy uzyskiwaniu złożonego obrazu danych
dwóch różnych tabel. Np. poprzednie zapytanie mogło by służyć do
wygenerowania tabeli roboczej, zawierającej prace już wykonane dla każdego
najemcy (tenant).
Konstrukcja SELECT INTO
Sybase i Microsoft SQL Server udostępniają odmianę komendy SELECT, która
potrafi skierować swój zbiór wynikowy do innej tabeli. Dzięki temu w jednym
kroku możemy utworzyć nową tabelę i wypełnić ją danymi. A oto odpowiednia
konstrukcja składniowa:
SELECT w.Work_Order_Number, t.Name
INTO #MYWORKTABLE
FROM WORDER w, LEASE 1, TENANT t
WHERE w.Property_Number=1.Property_Number
and 1.Tenant_Number=t.Tenant_Number
Zauważmy, że klauzula INTO występuje zaraz za listą kryteriów wyboru. Właśnie
ona kieruje dane wyjściowe komendy SELECT do drugiej tabeli. W tym
przypadku będzie to tabela tymczasowa (na co wskazuje symbol #); zostanie ona
usunięta (drop) po zamknięciu połączenia z serwerem. Nie mylmy jednak klauzuli
INTO w systemie SQL Server z tak samo nazywającą się klauzulą w systemach
Oracle i InterBase! Służy ona do nadawania zmiennym wartości i nie pozwala na
tworzenie tabel.
UWAGA:
Równoważna konstrukcja w systemie Oracle jest następująca:
CREATE TABLE MYWORKTABLE
(Work_Order_Number,
Name)
AS
SELECT w.Work_Oredr_Number, t.Name
FROM WORDER w, LEASE 1, TENANT t
WHERE w.Property_Number=1.Property_Number
and 1.Tenant_Number=t.Tenant_Number
Taka specjalna postać komendy CREATE TABLE umożliwia wypełnienie tabel
danymi po jej utworzeniu. Zauważmy, że nie można określać typów danych
i rozmiarów kolumn dla tak tworzonych tabel. Oracle otrzymuje te informacje
wprost z zapytania podrzędnego.
692 Część IV
Komenda INSERT
Poza wstawianiem wartości statycznych do tabeli, komenda INSERT może
również wstawić wiele wierszy poprzez zapytania podrzędne. Oto przykład:
INSERT INTO WORDERBACKUP
SELECT * FROM WORDER
Podobnie jak w przypadku zwykłej postaci tej komendy, tabela docelowa musi już
istnieć. Kolumny podane w zapytaniu podrzędnym muszą odpowiadać kolumnom
podanym w samej komendzie INSERT.
UWAGA:
W systemie Microsoft SQL Server istotnie rozszerzono możliwości komendy
INSERT -Ź można bowiem w jej ramach wykonać procedurę pamiętaną zamiast
zapytania podrzędnego. Jest więc ona znacznie mocniejsza od standardowej
i pozwala na wstawianie danych, które pochodzą z serwerów zdalnych. To samo
można uzyskać w systemie InterBase, przez wybranie wierszy dla komendy
INSERT, będącej fragmentem pamiętanej procedury wyboru (innymi słowy,
procedury pamiętanej typu SELECT).
Komenda DELETE
Niektóre platformy zawierają rozszerzenie składni komendy DELETE,
umożliwiające kwalifikowanie wyrażeń z tą komendą poprzez wykonywanie (w
ich ramach) złączeń i zapytań podrzędnych. Rozważmy przypadek, w którym
chcemy usunąć tylko tych klientów, dla których nie istnieją żadne zlecenia.
Możemy w tym celu użyć złączenia lub zapytania podrzędnego - w celu ustalenia
klientów, dla których zlecenia już istnieją i usunięcia tylko tych, dla których nie
określono żadnych zleceń.
Użytkownicy platform Sybase lub Microsoft SQL Server mogą usuwać wszystkie
dane z tabeli, używając komendy TRUNCATE TABLE (zamiast DELETE) - do
usuwania wierszy bez ograniczeń. Takie rozwiązanie jest szybsze i w minimalnym
tylko stopniu obciąża protokół transakcji.
Komenda UPDATE
Niektóre platformy umożliwiają odczytywanie w tej komendzie wartości z innych
tabel, tak że może ona wykonać złączenie w trakcie swego działania. Oto służąca
do tego konstrukcja składniowa:
UPDATE CUSTOMER
Rozdział 24 Zaawansowane programowanie w SQL 693
FROM CUSTOMER C,
ZIP Z
SET C.City=Z.City, C.State=Z.State
WHERE C.ZipCode = Z.ZipCode
Zwalnia nas to z konieczności stosowania kursorów lub innych złożonych
mechanizmów do uaktualniania jednej tabeli wartościami pochodzącymi z innej.
Kursory (Cursors)
Kursor (cursor) udostępnia wiersze w tabeli po jednym na raz. Z kursorami wiążą
się cztery podstawowe komendy: DECLARE, OPEN, FETCH i CLOSE. Komendy
UPDATE i DELETE można też wykorzystać do pracy z kursorami aktualizowanymi
(updatable cursors).
Kursor definiujemy za pomocą komendy SELECT. Jeżeli ma być to kursor
aktualizowany, to trzeba dołączyć listę kolumn do aktualizacji. Poniżej konstrukcja
składniowa:
DECLARE c_PROPERTY CURSOR
FOR SELECT * FROM PROPERTY
Zanim kursor będzie mógł zwrócić jakieś wiersze, musi zostać otwarty. Komenda
OPEN inicjuje zapytanie, które deklaruje kursor. Przykład poniżej:
OPEN c_PROPERTY
Zauważmy, że komenda OPEN nie otrzymuje wierszy z kursora; do tego celu
używamy komendy FETCH:
FETCH c_PROPERTY
Komenda FETCH zwraca po jednym wierszu na raz, z tabeli związanej z kursorem.
Za każdym wywołaniem FETCH zwracany jest nowy wiersz. Platformy InterBase,
Oracle i SQL Server udostępniają jedynie kursory, dla których kolejność działania
komendy FETCH jest rosnąca (kursory takie określa się jako forward cursors).
Aby poruszać się wstecz w zbiorze wynikowym kursora, należy go zamknąć
i ponownie otworzyć.
UWAGA:
Chociaż większość dostawców platform DBMS nie dostarcza kursorów
dwukierunkowych, DELPHI umożliwia ich wykorzystanie w naszych aplikacjach.
Środowisko BDE udostępnia bowiem mechanizm kursorów dwukierunkowych,
niezależnie od tego, czy platforma, na której zainstalowano DELPHI, obsługuje
takie kursory.
694 Część IV
Wiersze zwracane przez kursory aktualizowane możemy uaktualniać za pomocą
specjalnych wersji komend UPDATE i DELETE. Kursor musi być usuwany, jeżeli
ma być kursorem aktualizowanym, z użyciem klauzuli FOR UPDATE OF.
Ilustruje to poniższy przykład:
DECLARE c_PROPERTY CURSOR
FOR SELECT * FROM PROPERTY
FOR UPDATE OF Address
W klauzuli FOR UPDATE OF należy uwzględniać tylko te kolumny, które
rzeczywiście moglibyśmy uaktualniać. Deklarowanie niepotrzebnych pól do
aktualizacji powoduje zbyteczne obciążenie zasobów serwera.
Bieżący wiersz kursora aktualizowanego uaktualnia się lub usuwa za pomocą
komend UPDATE lub DELETE z klauzulą WHERE CURRENT OF
cursorname, zgodnie z poniższym przykładem:
UPDATE PROPERTY
SET Address= 357 Riverside Avenue
WHERE CURRENT OF c_PROPERTY
Aby usunąć wiersz, piszemy:
DELETE FROM PROPERTY
WHERE CURRENT OF c_PROPERTY
Kursory, z którymi zakończyliśmy pracę, zamykamy komendą CLOSE:
CLOSE c_PROPERTY
W platformie InterBase, komenda CLOSE zwalnia również wszystkie zasoby
systemowe związane z kursorem. W Sybase używamy do tego celu komendy
DEALLOCATE CURSOR, zgodnie z przykładem:
DEALLOCATE CURSOR c_PROPERTY
Optymalizacja w SQL
Reszta tego rozdziału poświęcona jest programowaniu w języku SQL na jeszcze
wyższym poziomie (niż dotąd omawiany), tzn. tworzeniu optymalnego kodu. Przez
optymalny należy rozumieć nie tylko szybki, ale także niezawodny - dzięki
zastosowaniu zdrowych technik projektowych, umożliwiających tworzenie kodu
przejrzystego oraz łatwego do konserwacji i rozszerzania. Niektóre z tych technik
w ogóle nie wiążą się z wydajnością, ale oszczędzają czas, umożliwiając nam
unikanie częstych pułapek i pomagając w pisaniu czystych i czytelnych
programów w języku SQL.
Zamieszczone niżej wskazówki na pewno nie są wyczerpujące. Są one
podpowiedziami, skrótami i sensownymi konwencjami, które autor uznał za
Rozdział 24 Zaawansowane programowanie w SQL 695
pożyteczne w pracy programisty. Realizowanie wszystkich tych wskazówek nie
jest tak ważne, jak pisanie programów w SQL w jednolity sposób. Pamiętajmy
chodzi tu o oszczędność czasu na dłuższą metę. Błyskawiczne sklecenie byle jakiej
procedury i dołączenie jej do naszej aplikacji może nas w końcowym efekcie
bardzo wiele kosztować - póz
niej i tak będziemy musieli ją poprawić, często
pogarszając integralność kodu. Wyświadczmy sobie przysługę: trzymajmy się
pewnych konwencji nazewnictwa i zasad kodowania we wszystkich programach,
podprogramach itd., które piszemy, zwłaszcza w języku SQL.
Autor podzielił wskazówki na dwie części: optymalne konstrukcje składniowe
w SQL oraz optymalna wydajność.
Składnia języka SQL
W tej części omawiamy niuanse, które nie mają faktycznego wpływu na
wydajność. Naszym celem jest przybliżenie zasad pisania kodu, który będzie
czytelny i przyjemny przez lata.
Przewaga składni według ANSI
Wszędzie, gdzie to możliwe, używajmy składni według ANSI. Autor czyni jedyny
wyjątek od tej zasady - w przypadku złączeń (joins). Składnia oferowana przez
Sybase/Microsoft jest bardziej zwięzła i czytelniejsza niż wzorcowa norma ANSI.
Tak czy inaczej bądz
my jednak zawsze świadomi różnic między używanym
dialektem SQL a składnią ANSI.
Takie podejście ułatwi innym korzystanie z efektów naszej pracy, dzięki używaniu
składni, na którą zgodziła się duża liczba dostawców platform DBMS. Zwiększy to
także pożytek z książek i materiałów szkoleniowych; gdyż największa ich liczba
dotyczy języka według ANSI, a nie dialektu konkretnego dostawcy. Wreszcie
konsekwentne trzymanie się wzorcowej wersji języka poszerzy możliwości
przenoszenia projektów między platformami i dostawcami.
Kwalifikowanie kolumn
Zawsze, gdy nasz kod dotyczy więcej niż jednej tabeli, używajmy pełnej
kwalifikacji kolumn do wyświetlenia. Z faktu, że tylko jedna (na razie!) tabela
w zapytaniu zawiera kolumnę o danej nazwie, wcale nie wynika, że możemy
omijać identyfikator z nazwą tabeli. Zawsze oznaczajmy każdą kolumnę nazwą jej
tabeli. Jeżeli zdarzy się nam póz
niej dodać tabelę o takiej samej nazwie kolumny,
co nazwy kolumn tabel już uwzględnionych w zapytaniu, takie podejście
zaoszczędzi nam czas poświęcony jego przeglądaniu - w celu usunięcia
wieloznacznych odniesień do kolumn.
696 Część IV
Nazwy zastępcze (aliasy) tablic
Korzystajmy z nazw zastępczych, aby zwiększyć czytelność naszego kodu. Nie
wpisujmy za każdym razem pełnej nazwy tabeli przed każdą kolumną w liście
wyboru; zastąpmy ją aliasem. Skraca to czas wpisywania oraz sam tekst zapytania
i zwiększa jego czytelność.
Nazwy kolumn w wyrażeniach INSERT
W rozdziale wprowadzającym do języka SQL zwróciliśmy uwagę, że nazwy
kolumn są opcjonalne w wyrażeniach INSERT, jeśli wstawiamy wartości do
wszystkich kolumn tabeli. Konsekwentne stosowanie nazw kolumn jest jednak
pożyteczne, gdyż pozwala nam upewnić się, że wstawiamy to, co chcemy wstawić.
Wymusza to na nas dopasowanie każdej z wprowadzanych wartości do
odpowiadającej jej nazwy kolumny. Jak poprzednio, nie jest to obligatoryjne, ale
stanowi dobrą praktykę programistyczną.
Klauzula GROUP BY
W zapytaniach zawierających funkcje sumaryczne (aggregates, aggragate
functions), powinniśmy zawsze używać do grupowania według wszystkich kolumn,
które nie są sumaryczne (tzw. nonaggregate columns) - klauzuli GROUP BY. Nie
przestrzeganie tego zalecenia prowadzi zwykle do generowania przez zapytania
bezużytecznych wyników i może stać się przyczyną zapętlenia. Niektóre platformy
DBMS odmawiają realizacji zapytań z klauzulą GROUP BY bez grupowania
według wszystkich kolumn które nie są sumaryczne.
Klauzula WHERE
Poniżej, w podpunktach  Unikanie iloczynów kartezjańskich oraz  Korzystanie
z nawiasów omówiliśmy techniki optymalizacji klauzuli WHERE. Używamy jej do
kwalifikowania uniwersalnej komendy SELECT, stąd też zachowanie jasności
i zwięzłości konstrukcji z jej użyciem ma kluczowe znaczenie dla czytelnego
programowania zapytań.
Unikanie iloczynów kartezjańskich
W przypadku komendy SELECT, operującej na wielu tabelach, każda tabela
wymaga poprawnego złączenia z przynajmniej jedną inną tabelą, a każde złączenie
winno wiązać się, poprzez słowo kluczowe AND, z innymi złączeniami
w wyrażeniu. Niespełnienie tego wymagania prowadzi do zwrócenia fragmentu
iloczynu kartezjańskiego tych tabel. Jeżeli są one bardzo duże, zapytanie może
działać nieskończenie długo. Zgodnie z wcześniejszymi rozważaniami, iloczyny
kartezjańskie mają bardzo ograniczoną przydatność i zwykle są rezultatem błędów.
Rozdział 24 Zaawansowane programowanie w SQL 697
Korzystanie z nawiasów
Zaleca się korzystanie z nawiasów do rozbijania na czytelne fragmenty złożonych
klauzul WHERE z dużą liczbą operatorów AND i OR. Ustala to jednoznacznie
priorytety elementów klauzuli i zwiększa czytelność kodu.
Procedury pamiętane
Poniższe wskazówki odnoszą się do optymalnych technik programowania procedur
pamiętanych (stored procedures). Ponieważ spora część kodu dużych systemów
typu klient/serwer napisana z wykorzystaniem takich procedur, ważne jest
zachowanie spójności podczas ich konstruowania.
Komentarze
Powinniśmy unikać komentarzy zagnieżdżonych - tym bardziej, że nie wszystkie
platformy je obsługują. Unikajmy też przesady w komentowaniu. Jedna ze szkół
programowania twierdzi, że cały program, niezależnie od tego, czy napisano go
w tradycyjnym języku programowania, czy też w SQL, powinien być bardzo
obszernie skomentowany. Autor nie zgadza się z taką koncepcją. Przyczyną, dla
której obszerne komentarze uważa za niepotrzebne (i w rzeczywistości
przeszkadzające w dobrym programowaniu w SQL), jest duże podobieństwo
języka SQL do naturalnego angielskiego. Język SQL pierwotnie miał nosić nazwę
Structured English Query Language (strukturalny język angielski do formułowania
zapytań) i stąd pochodzi akronim SQL. Prawidłowo napisany program w tym
języku powinien być  samokomentujący się .
Zbyt obszerne przypisy rozpraszają i odciągają uwagę od głównego tekstu książki.
Dotyczy to również umieszczania zbyt wielu komentarzy w kodzie SQL. Mogą one
bardzo utrudnić poruszanie się po obszernym skrypcie.
Przesadne komentarze powodują też podwojenie pracy - gdy zmieniamy kod,
musimy też zmieniać komentarze. Jeżeli kod jest za trudny do zrozumienia jedynie
z kilkoma dobrze umieszczonymi komentarzami, to być może trzeba go przepisać.
W żadnym jednak wypadku nie ma generalnego zakazu używania komentarzy
w programach SQL. Oszczędne komentowanie kodu z
ródłowego jest bardzo
potrzebne, nawet w przypadku języków programowania zbliżonych do
naturalnych. Musimy jednak zdać sobie sprawę, że SQL jest w dużym stopniu
językiem  samokomentującym się. Jeżeli więc prawidłowo konstruujemy nasze
zapytania, obszerne komentarze powinny okazać się zbędne.
Bloki BEGIN...END
Wydzielajmy fragmenty kodu za pomocą bloków BEGIN...END - nawet
w sytuacjach, w których nie jest to konieczne. Przykładowo, dialekt Transact-SQL
698 Część IV
platform Sybase/Microsoft pozwala omijać nawiasy BEGIN...END w wyrażeniu
IF, jeśli steruje ono tylko jedną instrukcją. Najlepiej jest stosować bloki
BEGIN...END we wszystkich pętlach oraz konstrukcjach IF. Zwiększa to
czytelność programów i ułatwia rozszerzanie wyrażeń warunkowych na wiele
wierszy.
Wydajność
Poniższe uwagi na temat optymalizacji dotyczą przede wszystkim wydajności.
Zastosowanie opisanych w nich technik może przyczynić się do znacznego
przyspieszenia pracy naszej bazy danych.
Indeksy
Poniżej opisano zasady optymalizacji pracy z indeksami. Przekonamy się, że
chociaż z czysto technicznego punktu widzenia model relacyjny nie obejmuje
indeksów, to mają one wielki wpływ na techniki projektowania zapytań.
Indeksy wewnętrzne a zewnętrzne
Systemy Sybase i Microsoft SQL Server udostępniają rozszerzoną wersję
komendy CREATE INDEX, która umożliwia tworzenie tzw. indeksów
wewnętrznych (clustered indexes). Indeks zewnętrzny (nonclustered index) to
indeks w tradycyjnym znaczeniu - istnieje na najniższym poziomie i przechowuje
wartości kluczy, za pomocą których baza danych odwołuje się do danych fizycznej
tabeli. Indeks wewnętrzny przechowuje rzeczywiste dane na najniższym poziomie
swojej struktury drzewiastej. Oznacza to, że w liściach drzewa przechowuje się
cały wiersz, a nie tylko wartości klucza. Z definicji, jedna tabela może mieć tylko
jeden indeks wewnętrzny.
Dane w indeksie wewnętrznym sortuje się według określonego klucza, co daje
znaczną poprawę wydajności w porównaniu z tradycyjnymi indeksami, zwłaszcza
w przypadku zapytań zwracających podciągi wierszy. Nie ma wówczas potrzeby,
w celu wyszukania klucza, który znajduje się w indeksie, odnoszenia się do
fizycznej tabeli. Indeks jest tabelą. Nie ma żadnej oddzielnej tabeli, w której
miałoby się czegoś szukać.
Odpowiednia konstrukcja w dialekcie systemu SQL Server jest następująca:
CREATED CLUSTERED INDEX INVOICES01 ON INVOICES (OrderNumber)
Niestety, InterBase nie udostępnia żadnych podobnych konstrukcji do tworzenia
indeksów wewnętrznych.
Rozdział 24 Zaawansowane programowanie w SQL 699
UWAGA:
Nie należy mylić indeksów wewnętrznych (clustered index) w systemach firm
Sybase/Microsoft, z klastrami (clusters) udostępnianymi przez Oracle. Chociaż
w języku angielskim są to podobne terminy, mają one inne znaczenia. W wersji
Oracle 8 pojawią się tzw. tablice indeksowe (Index Only Tables) - indeksy
przechowujące dane w liściach drzewa. Będą one przybliżonym ekwiwalentem
indeksów wewnętrznych.
Zalecamy stosowanie indeksów wewnętrznych wszędzie tam, gdzie jest to
możliwe.
Indeksy wyczerpujące (covered indexes)
Optymalizator zapytań w serwerze sprawdza, czy indeks zewnętrzny udostępnia
wszystkie kolumny spełniające warunki podane w wyrażeniu SELECT, bez
potrzeby odwoływania się do samej tabeli. Wyczerpujący indeks (covered index)
to indeks zewnętrzny, który zawiera jako część swojego klucza wszystkie dane,
które mają zostać zwrócone przez zapytanie. Oznacza to, że poza swoimi
wartościami, klucz indeksu wyczerpującego zawiera wszystkie pozostałe elementy
danych, wymagane do realizacji danego zapytania. Tak więc indeks wyczerpuje
zapytanie - stąd jego nazwa.
A oto przykład wykorzystania indeksu wyczerpującego:
CREATE INDEX ORDERS04 ON ORDERS (OrderDate, Amount)
Teraz zapytanie
SELECT OrderDate, SUM(Amount) TotalAmount
FROM ORDERS
GROUP BY OrderDate
może zostać zoptymalizowane w serwerze bazy danych tak, żeby odczyt danych
przebiegał tylko z indeksu, bez konieczności wyszukiwania wartości kolumny
Amount w samej tabeli.
Wydajność komendy SELECT
Kilka następnych wskazówek dotyczy komendy SELECT. Opisano w nich kilka
prostych sposobów zwiększenia wydajności używanych wyrażeń SELECT, bez
konieczności znacznego modyfikowania kodu.
Funkcja EXISTS
W wyrażeniach SELECT, do sprawdzania istnienia określonych danych należy
korzystać z funkcji EXISTS. W szczególności nie używajmy konstrukcji
700 Część IV
w rodzaju poniższych, jeżeli chcemy dokonać kwalifikacji zapytania według tego,
czy w tablicy istnieją wiersze spełniające dany warunek:
IF (SELECT COUNT(*) FROM TABLENAME WHERE condition) > 0
Wyrażenie to powoduje zliczenie wszystkich rekordów w tabeli, które spełniają
warunek w klauzuli WHERE, przed wyliczeniem warunku dla komendy IF.
W przypadku dużych tabel zliczanie takie może trwać w nieskończoność. Poniższa
konstrukcja jest znacznie wydajniejsza:
IF EXISTS (SELECT * FROM TABLENAME WHERE condition)
Większość serwerów optymalizuje zapytania w rodzaju powyższego tak, że
sterowanie wraca w momencie wyszukania jednego wiersza, spełniającego kryteria
podane w komendzie. Jeśli z indeksu wynika, że dla kryteriów takich istnieje
rekord dokładnie je spełniający, zapytanie może w ogóle nie być wykonywane.
Komenda SELECT ze słowem kluczowym DISTINCT
Konstrukcja SELECT DISTINCT rzadko kiedy bywa naprawdę konieczna (w
praktyce prawie nigdy). Aby zrozumieć zasadność powyższego stwierdzenia
musimy dokładnie wiedzieć, do czego służy słowo kluczowe DISTINCT.
Eliminuje ono mianowicie duplikaty ze zbioru wynikowego, zwróconego przez
komendę SELECT. Zapytajmy więc, czemu w ogóle zbiór wynikowy zawiera jakieś
duplikaty? Czy dzieje się tak w rezultacie  nieoptymalnie sformułowanego
zapytania? Jeśli odpowiedz
na drugie z tych pytań brzmi tak, to powinniśmy
poprawić samo zapytanie. W praktyce konstrukcja SELECT DISTINCT służy
przeważnie do maskowania iloczynu kartezjańskiego, który niepotrzebnie
generowany jest przez sam kod. Konstrukcji tej powinniśmy używać tylko wtedy,
gdy jest ona jedynym sposobem otrzymania żądanego wyniku, a nie wtedy, gdy jest
sposobem najprostszym.
Klauzula WHERE
Klauzula ta jest prawdopodobnie głównym i najczęstszym kandydatem do
modyfikacji, prowadzących do poprawy wydajności. Komenda SELECT jest sama
w sobie tak złożona i ma tyle aspektów, że można by całe książki napisać na temat
jej optymalizacji wraz z klauzulami. Poniższe wskazówki powinny pomóc
w poprawie wydajności konstrukcji z klauzulami WHERE.
Ograniczanie DataSet
Głównym zadaniem klauzuli WHERE jest ograniczanie zbioru wierszy, z którymi
musi pracować zapytanie. Klauzule WHERE powinniśmy tak organizować, żeby
liczba wierszy, których dotyczą manipulacje, była możliwie najmniejsza. Wszędzie
Rozdział 24 Zaawansowane programowanie w SQL 701
tam gdzie to możliwe, powinniśmy do tego celu wykorzystywać indeksy. Jeżeli
często się zdarza, że musimy ograniczać zbiór danych (DataSet) - za pomocą
klauzuli WHERE bez użycia indeksu - to być może nadszedł już czas na dodanie
nowego indeksu.
Zwróćmy uwagę, że dany układ klauzul FROM i WHERE nie gwarantuje żadnego
konkretnego porządku dostępu do tabeli. W platformach z optymalizacją zapytań
(a większość platform dysponuje lepszą lub gorszą optymalizacją), optymalizator
rezerwuje sobie prawo do przekonstruowania zapytań w celu osiągnięcia
optymalnej wydajności. Są sposoby ominięcia optymalizacji (np. za pomocą
komendy SET FORCEPLAN ON w systemie Microsoft SQL Server), ale zwykle
lepiej zostawić inicjatywę optymalizatorowi.
Pola kluczy w funkcjach i wyrażeniach
Unikajmy używania pól kluczy indeksowych w wyrażeniu lub jako parametrów
funkcji, jeżeli to wyrażenie lub funkcja występuje w klauzuli WHERE - by
serwer mógł wybrać prawidłowy indeks. Oto przykład zapytania w dialekcie
platformy InterBase, uniemożliwiającego optymalizatorowi zapytań użycie
indeksu:
SELECT * FROM ORDERS
WHERE CAST (OrderDate AS CHAR(8)) >=  07/01/95
Przykład ten z dwóch przyczyn jest ilustracją nieprawidłowego wykorzystania
funkcji CAST(). Po pierwsze jest ono niekonieczne, ponieważ system InterBase
automatycznie przeprowadza porównania między wyrażeniami typów znakowego
i daty. Po drugie wprowadza w błąd optymalizator zapytań, gdyż nie użyje on
indeksu, który istnieje dla kolumny OrderDate.
Słowo kluczowe LIKE
Przy słowie kluczowym LIKE powinniśmy zwrócić uwagę, czy możliwe jest
użycie indeksu. Przykładowo, indeksu nie można wykorzystać w poniższym
zapytaniu:
SELECT * FROM CUSTOMER
WHERE LastName LIKE  %Joh%on
Występowanie znaku uogólniającego (%*) na pierwszej pozycji wyrażenia LIKE
uniemożliwia wyszukiwanie indeksowe. Natomiast następne zapytanie jest w pełni
dopuszczalne, gdyż optymalizator zapytań może wyszukać pierwszą pozycję, która
spełnia kryterium wyszukiwania, używając jako klucza sekwencji znaków Joh:
SELECT * FROM CUSTOMER
WHERE LastName LIKE  Joh%on
702 Część IV
Teraz, za pomocą tradycyjnego wyszukiwania sekwencyjnego, wiersze
kwalifikowane przez Joh można z kolei ograniczyć do tych, które kończą się na
on. Tak więc potencjalny zbiór rekordów, spełniających kryterium wyszukiwania,
może - dzięki wyrażeniom w rodzaju LIKE - zostać wyznaczony z użyciem
indeksu. Upraszcza to zadanie ograniczania zbioru zwracanych z tabeli rekordów -
do rekordów zawierających podaną maskę dla nazwiska.
Zapytania podrzędne (subqueries)
Należy ich unikać wszędzie, gdzie to możliwe, wpisując zamiast nich
bezpośrednio listy wartości. Z zapytaniem podrzędnym z reguły wiąże się
konieczność zakładania przez serwer tymczasowej tabeli roboczej do
przechowywania wyników tego zapytania. Jeżeli zaś wyników nie zachowuje się
w tabeli roboczej, to zapytanie trzeba wykonywać dla każdej iteracji nadrzędnego
zapytania. Żadna z tych dwóch możliwości nie wydaje się szczególnie atrakcyjna.
W praktyce często okazuje się, że zapytania z zapytaniami podrzędnymi można tak
przepisać, że nie tylko będą szybciej realizowane, ale także staną się czytelniejsze.
Wyobraz
my sobie, na przykład, że piszemy zapytanie, w którym trzeba
podsumować tabelę z milionami potwierdzeń wypłat z użyciem kart kredytowych.
Interesują nas przy tym tylko potwierdzenia dla klientów indywidualnych (a nie
firm). Dysponujemy tabelą z dziesięcioma różnymi rodzajami klientów,
oznaczonymi albo jako klient indywidualny, albo firma. Wielu z nas
zrealizowałoby to zapytanie  nieoptymalnie w sposób następujący:
SELECT UseDate, SUM(Amount)AmountSpent
FROM RECEIPTS
WHERE CardType in (SELECT CardType FROM CARDTYPE WHERE
Type= I )
GROUP BY UseDate
Jednym ze sposobów przyspieszenia realizacji tego zapytania jest bezpośrednie
wpisanie listy, zamiast zwracającego ją zapytania podrzędnego. Jeżeli jednak po
takiej optymalizacji jakiś użytkownik bazy danych doda rekord do tablicy
CARDTYPE, to nie zostanie on uwzględniony w wygenerowanym raporcie. Tak
więc musimy każdorazowo rozstrzygnąć, czy wyżej podany sposób można
zastosować w danym przypadku.
Inna metoda optymalizacji polega na dynamicznym utworzeniu zapytania
w Delphi. A oto poszczególne etapy postępowania przy dynamicznym tworzeniu
zapytań:
1. Otworzyć zapytanie TQuery, którego kod SQL odpytuje serwer o typy kart
związane z klientami indywidualnymi
Rozdział 24 Zaawansowane programowanie w SQL 703
2. Przejrzeć w pętli zbiór wynikowy, zwrócony przez TQuery i utworzyć łańcuch
zawierający listę typów kart, oddzielonych przecinkami. A
ańcuch taki ma
postać następującą:
 1,2,4,12,13,20
3. Przekazać ten łańcuch dynamicznemu zapytaniu SQL, które ma wystąpić
w klauzuli IN naszego zapytania. Oto przykład takiego zapytania:
SELECT UseDate, SUM(Amount)AmountSpent
FROM RECEIPTS
WHERE CardType in (:CardTypeList)
GROUP BY UseDate
4. Przygotować i otworzyć dynamiczne zapytanie SQL.
W ten sposób otrzymaliśmy zapytanie w możliwie najelastyczniejszej postaci,
która pozwala na uzupełnianie tabeli CARDTYPE; z drugiej strony uniknęliśmy
zapytań podrzędnych w procesie podsumowania.
Najlepszym zaś sposobem optymalizacji jest po prostu złączenie dwóch tabel,
zgodnie z poniższym przykładem:
SELECT r.UseDate, SUM(r.Amount)AmountSpent
FROM RECEIPTS r, CARDTYPE c
WHERE r.CardType = c.CardType
and c.Type= I
GROUP BY UseDate
Dzięki temu całą pracę wykona serwer, a zapytania podrzędne zostały całkowicie
wyeliminowane.
Klauzula ORDER BY
Powinniśmy unikać tej klauzuli. Jest ona mało wydajna i może doprowadzić do
niepotrzebnego sortowania przez serwer wielkiego zbioru wynikowego. Nie
używajmy jej w ogóle - z wyjątkiem sytuacji, gdy rzeczywiście potrzeba nam
uporządkowania, jakie ta klauzula generuje. Zauważmy, że w platformach Sybase
i Microsoft SQL Server indeksy wewnętrzne powodują uporządkowanie wierszy
wynikowych. Oprócz tego w niektórych systemach, do uporządkowania danych
można wykorzystać klauzulę ORDER BY. Chociaż sam język SQL nie gwarantuje
żadnej konkretnej kolejności wierszy, niektóre platformy oferują takie czy inne
pośrednie metody porządkowania. Szczegółowych informacji na ten temat trzeba
zawsze szukać w dokumentacji posiadanego serwera.
Klauzula HAVING
Prawie zawsze istnieje jakiś lepszy sposób kwalifikowania zapytania, niż klauzula
HAVING. Ogólnie rzecz biorąc, klauzula HAVING jest zawsze mniej wydajna od
704 Część IV
klauzuli WHERE; w pierwszej zbiór wynikowy jest kwalifikowany już po jego
podziale na grupy, podczas gdy w drugiej - przed. Oto przykład zbędnej klauzuli
HAVING:
SELECT CUSTOMER.LastName, COUNT(*) NumberWithName
FROM CUSTOMER
GROUP BY CUSTOMER.LastName
HAVING CUSTOMER.LastName<> Citizen
A teraz to samo zapytanie po jego przepisaniu do prawidłowej postaci z klauzulą
WHERE:
SELECT CUSTOMER.LastName, COUNT(*) NumberWithName
FROM CUSTOMER
WHERE CUSTOMER.LastName<> Citizen
GROUP BY CUSTOMER.LastName
Podejście to jest lepsze, ponieważ grupowanie nie dotyczy już danych, których
i tak nie potrzebujemy. W przypadku klauzuli HAVING, większość serwerów
najpierw przeprowadziłoby grupowanie danych, a dopiero potem przefiltrowało je
według kryteriów podanych w tej klauzuli. Jest więc ona mniej wydajna -
chociażby z uwagi na uwzględnianie niepotrzebnych danych.
Jedynym usprawiedliwionym przypadkiem użycia HAVING jest kwalifikacja
zbioru wynikowego według funkcji sumarycznych - kolumn, które są wynikiem
obliczeń przeprowadzonych na zbiorze wynikowym. Ponieważ dane wejściowe dla
funkcji sumarycznych nie są znane do chwili utworzenia zbioru wynikowego przez
serwer, nie można ich użyć w klauzuli WHERE. Jeżeli chcemy więc dokonać
kwalifikacji zbioru wynikowego za pomocą funkcji sumarycznych, musimy
skorzystać z klauzuli HAVING. Oto przykład:
SELECT CUSTOMER.LastName, COUNT(*) NumberWithName
FROM CUSTOMER
WHERE CUSTOMER.LastName<> Citizen
GROUP BY CUSTOMER.LastName
HAVING COUNT(*) > 2
Ponieważ wartość pola NumberWithName jest nieokreślona do chwili
zakończenia tworzenia zbioru wynikowego, musieliśmy tutaj wykorzystać właśnie
klauzulę HAVING.
Rozdział 24 Zaawansowane programowanie w SQL 705
UWAGA:
Chociaż serwery coraz sprawniej optymalizują mało wydajne zapytania, zawsze
powinniśmy zwracać uwagę na wydajność pisanych przez nas programów. Nawet
jeśli konkretny, posiadany przez nas system RDBMS, potrafi zamienić
niewłaściwie użytą klauzulę HAVING na WHERE, to inne platformy nie muszą tego
umieć. Zwyczaj pisania zawsze wydajnego kodu SQL zaoszczędzi nam wysiłku
przy zmianie platformy DBMS.
Klauzula COMPUTE
Dialekty Sybase/Microsoft Transact-SQL umożliwiają sumowanie kolumn
zwracanych przez wyrażenie SELECT, bez konieczności odwoływania się w nim
do funkcji sumarycznych (aggregates) - dzięki klauzuli COMPUTE. Występuje ona
po innych klauzulach, składających się na wyrażenie SELECT i po prostu
powoduje zsumowanie pól zwróconej kolumny za pomocą zwykłej funkcji
sumarycznej - takiej, jak SUM(). Ilustruje to przykład:
SELECT CustomerNumber, Amount
FROM ORDERS
COMPUTE SUM(Amount)
Istnieje jednak kilka powodów, żeby z udogodnienia tego nie korzystać. Po
pierwsze, większość narzędzi użytkownika (front-end tools) nie umie przetwarzać
go jako części zbioru wynikowego. Po drugie, klauzula COMPUTE ma tak
bezsensowne ograniczenia, że jest w rzeczywistości znacznie mniej przydatna, niż
moglibyśmy przypuszczać. Po trzecie, i najważniejsze, zapytanie powyższe można
skonstruować tak, żeby żądana suma została zwrócona bez konieczności uciekania
się do klauzuli COMPUTE:
SELECT O.CustomerNumber, O.Amount, SUM(O2.Amount) TotalOrders
FROM ORDERS 0
ORDERS O2
GROUP BY O.CustomerNumber, O.Amount
Uzyskany format danych jest akceptowany przez większość narzędzi. Jeszcze
lepszym i wydajniejszym rozwiązaniem jest wykonanie dwóch oddzielnych
przebiegów dla danych: w jednym podzbiór danych kieruje się do tabeli
tymczasowej, a w drugim jest on sumowany. W każdym przypadku możemy, na
wiele sposobów, uzyskać żądane wielkości bez odwoływania się do mało
eleganckiej klauzuli COMPUTE.
Klauzula GROUP BY
Kolumny grupujmy zgodnie z kluczami indeksu, jeśli to możliwe. Przykładowo,
dla dwóch poniższych zapytań:
706 Część IV
SELECT OrderDate, CustomerNumber, SUM(Amount)TotalOrders
FROM ORDERS
GROUP BY OrderDate, CustomerNumber
i
SELECT OrderDate, CustomerNumber, SUM(Amount)TotalOrders
FROM ORDERS
GROUP BY CustomerNumber, OrderDate
tylko w drugim rozwiązaniu został użyty indeks dla tabeli ORDERS, który
utworzono za pomocą następującej konstrukcji:
CREATE INDEX ORDERS03 ON ORDERS (CustomerNumber, OrderDate)
Jeżeli nie istnieje indeks utworzony z użyciem OrderDate, jako kluczem
wyższego poziomu, optymalizator przy realizacji zapytania nie wykorzysta
żadnego indeksu.
Procedury pamiętane
Poniższe techniki odnoszą się do optymalizacji wydajności procedur pamiętanych
(stored procedures). Czasami już samo wykorzystanie tych procedur (zamiast
języka SQL w trybie interpretacyjnym), może przyczynić się do znacznego
zwiększenia wydajności naszej aplikacji. Poza tym optymalizacja samych procedur
pamiętanych może wpływać pozytywnie na wydajność.
Niewielka liczba parametrów
Parametry przekazywane do procedury pamiętanej powinny mieć jak najmniejszy
rozmiar i powinno ich być jak najmniej. Tak jak w przypadku tradycyjnych
języków programowania, powinniśmy minimalizować liczbę parametrów
przekazywanych na stos. Zagadnienie to staje się bardzo istotne w przypadku
parametrów procedur pamiętanych, gdyż przekazywanie ich do serwera przez sieć
może przyczyniać się do powstawania wąskich gardeł. Przekazujmy więc tylko
krótkie wartości całkowite i unikajmy za wszelką cenę długich łańcuchów
znakowych, jako parametrów.
Niektóre platformy pozwalają na przekazywanie parametrów zarówno przez
nazwę, jak i kolejność. W sytuacjach, kiedy procedury nie wywołujemy raz za
razem, parametry lepiej jest przekazywać przez nazwę, gdyż polepszy to
czytelność programu. Jeżeli zaś jest ona często wywoływana, to lepiej
przekazywać je przez położenie, gdyż przyczynia się to do niewielkiego
zwiększenia wydajności w większości platform DBMS. Takie małe zyski zwykle
gromadzą się, prowadząc w efekcie do znacznej poprawy wydajności wówczas,
gdy dana procedura wywoływana jest setki czy tysiące razy.
Rozdział 24 Zaawansowane programowanie w SQL 707
Konstrukcja SET NOCOUNT ON w systemie SQL Server
Systemy Sybase i Microsoftr SQL Server domyślnie przekazują do klienta liczbę
wierszy przetworzonych przez każde wyrażenie, które zawiera procedurę
pamiętaną, wywołaną przez tego klienta. Klient przeważnie w żaden sposób nie
wykorzystuje tych liczb. Wydajność procedur pamiętanych w systemie SQL
Server możemy zwiększyć za pomocą konstrukcji SET NOCOUTN ON. Jej
jedynym skutkiem ubocznym jest to, że komenda READTEXT, użyta razem
z funkcją dbreadtext() biblioteki DB-Library, może działać niepoprawnie.
Ponieważ sytuacja taka jest niezwykle rzadka, konstrukcję SET NOCOUNT ON
powinniśmy uważać za bezpieczną i z reguły pożądaną w definicjach procedur
pamiętanych.
Nadawanie wartości zmiennym lokalnym
Jeżeli w ramach procedury pamiętanej zmiennym lokalnym nadaje się wartości
z użyciem wyrażeń SELECT, to należy to czynić w jednym wyrażeniu SELECT.
W niektórych platformach pewne dodatkowe obciążenie zasobów wiąże się
z pierwszym przypisaniem wartości do zmiennej, w każdym wyrażeniu SELECT
z osobna. Tak więc zgrupowanie przypisań w jednym, zbiorczym wyrażeniu
SELECT, jest wówczas bardziej wydajne, zgodnie z przykładem w dialekcie
InterBase:
SELECT City, State, Zip
INTO :City, :State, :Zip
FROM CUSTOMER
oraz w dialekcie Transact-SQL:
SELECT @City=City, @State=State, @Zip=Zip
FROM CUSTOMER
Dodatkowo zmniejszamy liczbę wyrażeń SELECT w procedurze pamiętanej, co
ułatwia optymalizatorowi zapytań w serwerze jej rozkład gramatyczny
i optymalizację.
Wyszukiwanie informacji dodatkowych
Informacje dodatkowe powinny być wyszukiwane przez procedurę pamiętaną
(stored procedure) w ostatniej kolejności. Najpierw przecież trzeba ograniczyć
zbiór danych, a dopiero potem wyszukiwać w nim informacje.
Jeżeli chcemy np. dokonać podsumowania tabeli kart kredytowych, która zawiera
kilka milionów wierszy, w procesie podsumowywania nie powinniśmy
wyszukiwać nazwy klienta dla każdej karty; lepiej jest odłożyć to na póz
niej
i wykonać dopiero po możliwie dużej redukcji zbioru wynikowego (tzn. kiedy
gotowe będą wiersze wynikowe dla aplikacji klienckiej). Najefektywniejsze jest
więc zapytanie zrealizowane w wielu przebiegach, które za każdym razem
708 Część IV
przechowuje zredukowany zbiór wynikowy w tabelach tymczasowych. Dane po
pełnej kwalifikacji należy złączyć z tabelą CUSTOMER - w celu zwrócenia
dodatkowych informacji o klientach (osobach) do aplikacji - klienta (programu).
Chociaż sens wykonywania wielu przebiegów na dużym zbiorze danych może
wydawać się niezgodny z intuicją, zapamiętajmy, że serwer także wykonuje wiele
przebiegów tam, gdzie to konieczne. Jeżeli rozbijamy nasze zapytanie na wiele
przebiegów, to w pewien sposób sami dokonujemy optymalizacji. Najważniejszą
rzeczą przy optymalizowaniu zapytań w postaci procedur pamiętanych jest
możliwie najwcześniejsza redukcja zbioru danych, które są przetwarzane przez
zapytania. To zaś czasem wymaga wielu przebiegów oraz tabel tymczasowych.
Zapytania krzyżowe
W pewnych sytuacjach standardowa metoda grupowania danych poprzez
wyrażenie SELECT okazuje się nie wystarczająca. W szczególności trudne mogą
okazać się w realizacji z użyciem języka SQL tzw. zapytania krzyżowe (cross-tab
queries). Zapytanie takie organizuje dane w wiersze i kolumny, podobnie do
arkusza kalkulacyjnego. Sporą trudność może sprawić przedstawienie w postaci
tabeli danych, które (co zdarza się najczęściej) są zorganizowane w sposób
liniowy.
Wytłumaczmy to na przykładzie. Załóżmy, że piszemy zapytanie, które ma
zwrócić dane o wynikach sprzedaży trzech największych amerykańskich
producentów samochodów, Forda, General Motors i Chryslera. Aplikacja-klient
wymaga, żeby z lewej strony raportu znajdował się typ samochodu (subcompact,
compact, full size, truck itd.), a kolumny z danymi o sprzedaży dla każdego
wytwórcy - z prawej. Zwykły kod w języku SQL generujący te informacje jest
następujący:
SELECT CarType, Marker, Sales TotalSales
FROM BIGTHREESALES
Ponieważ jednak nasz systemowy program tworzący raporty (report writer) nie
umie generować odwołań krzyżowych (co umie, na szczęście, Delphi), to danych
nie można sformatować w sposób akceptowany przez klienta, bez pewnej dawki
,,ekwilibrystyki na poziomie kodu z
ródłowego. W tym przypadku musielibyśmy
utworzyć tabelę roboczą o wymaganym przez klienta formacie wyjściowym,
a następnie wypełnić ją odpowiednimi danymi. Oto przykład:
CREATE TABLE CARCROSS
(CarType CHAR(10) NULL,
FordSales FLOAT NULL,
GMSales FLOAT NULL,
ChryslerSales FLOAT NULL)
Rozdział 24 Zaawansowane programowanie w SQL 709
Typowym podejściem jest wykonanie wielu przebiegów dla tej tabeli
i wypełnienie jej właściwymi danymi, przed zwróceniem kompletnego zbioru
danych do programu tworzącego raporty (report writer). I znowu przykład:
INSERT INTO CARCROSS (CarType, FordSales)
Select CarType, Sales FordSales
FROM BIGTHREESALES
WHERE Maker= Ford
Teraz albo musimy użyć kursora i uaktualnić właściwe kolumny dotyczące
pozostałych dwóch producentów, albo zastosować w pętlach wyrażenie UPDATE,
żeby uaktualnić odpowiednie kolumny za jednym razem:
SELECT Sales
INTO :GMSales
FROM BIGTHREESALES
WHERE Maker= GM
AND CarType= SC
UPDATE CARCROSS
SET GMSales=:GMSales
WHERE CarType= SC
Moglibyśmy też użyć następującej konstrukcji składniowej w dialekcie systemu
SQL Server:
UPDATE CARCROSS
SET GMSales=Sales
FROM SALES S, CARCROSS C
WHERE Maker= GM
AND S.CarType=C.CarType
Istnieje jednak lepszy sposób osiągnięcia pożądanego formatu danych.
Wykorzystamy w nim jedną z najmocniejszych stron języka SQL - zdolność do
łatwego grupowania danych i dokonywania ich podsumowań - do umieszczenia
danych w formacie tabeli krzyżowej (cross-tab format) przy minimalnym
nakładzie pracy. Użyjemy przy tym techniki zwanej składaniem lub spłaszczaniem
zapytań (query folding lub query flattening). A oto poprzednie zapytanie
zrealizowane w technice składania zapytań:
Najpierw zbieramy i umieszczamy w odpowiedniej kolumnie dane dotyczące
Forda:
INSERT INTO CARCROSS (CarType, FordSales)
Select CarType, Sales FordSales
FROM BIGTHREESALES
WHERE Maker= Ford
Następnie to samo wykonujemy dla firmy GM:
INSERT INTO CARCROSS (CarType, GMSales)
710 Część IV
Select CarType, Sales GMSales
FROM BIGTHREESALES
WHERE Maker= GM
... i wreszcie dla Chryslera:
INSERT INTO CARCROSS (CarType, ChryslerSales)
Select CarType, Sales ChryslerSales
FROM BIGTHREESALES
WHERE Maker= Chrysler
W tym momencie wiersze tabeli CARCROSS wyglądają tak: (rys.24.1):
Rysunek 24.1.
Tabela
CARCROSS przed
złożeniem
Tablicę CARCROSS składamy, używając następującego zapytania:
Select CarType,SUM(FordSales) FordSales, SUM(GMSales)
Ą' GMSales, SUM(Chrysler Sales) ChryslerSales
FROM CARCROSS
GRUOP BY CarType
Zbiór wynikowy, zwrócony przez powyższe zapytanie, przedstawia rys.24.2.
Rozdział 24 Zaawansowane programowanie w SQL 711
Rysunek 24.2.
Zbiór wynikowy po
operacji składania
Jak widać, dane w formacie wymaganym dla raportu otrzymaliśmy tym razem bez
opisanych poprzednio komplikacji językowych.
Magazynowanie danych a język SQL
Jednym z klasycznych problemów, napotykanych przez projektantów przy
tworzeniu magazynów danych (data warehouses), jest wybór najlepszego sposobu
przechowywania danych sumarycznych i ich odpytywania. Magazyny danych
przeważnie przechowują duże ilości danych sumarycznych. Przeważnie oprócz
bieżącego (szczegółowego) odpytywania danych, użytkownicy chcą również mieć
do nich dostęp po ich podsumowaniu. Stwarza to jednak szereg problemów, które
trzeba rozwiązać przy projektowaniu architektury magazynu danych. Pierwszym
z nich jest wybór odpowiedniego miejsca na przechowanie podsumowanych
danych. Jeśli będą one przechowywane w tej samej bazie danych, co szczegółowe
dane, to czy należy dla nich przeznaczyć specjalne tablice sumaryczne? A może
lepiej je umieścić w specjalnej sumarycznej bazie danych - jak jednak zapewnić
wówczas do nich dostęp? Czy podwoi to pracę przy administrowaniu danymi?
A co z procedurami pamiętanymi i raportami utworzonymi dla danych
szczegółowych - czy powinno się je przebudować tak, żeby współpracowały
również z danymi sumarycznymi?
Optymalne rozwiązanie wyżej wymienionych problemów pociąga za sobą
niewielkie zwiększenie wymagań pamięciowych. Jego sednem jest tzw.
podsumowanie zintegrowanie (in-line summarization) danych. Polega ono na
ułożeniu tabel z danymi szczegółowymi w taki sposób, żeby podsumowywane
wiersze znajdowały się w tej samej tabeli, co wiersze szczegółowe, i żeby można
712 Część IV
je było odpytywać za pomocą tych samych zapytań, co używane w przypadku
wierszy szczegółowych. Podejście takie eliminuje potrzebę sumarycznych
tabel/baz danych oraz specjalnych, zorientowanych na dane sumaryczne, procedur
pamiętanych i raportów.
Zilustrujmy to rozwiązanie na przykładzie. Załóżmy, że mamy bazę danych
z milionami wypłat dokonywanych z użyciem kart kredytowych. Pod koniec
każdego miesiąca chcemy podsumować informacje o wypłatach według dni,
numerów karty oraz miejsca wypłaty. Musimy obliczyć zarówno całkowitą liczbę
wypłat, jak i całkowitą kwotę. Załóżmy iż chcemy wygenerować tylko jeden
rekord dla sytuacji, w której John Doe zrealizował pierwszego stycznia trzy
wypłaty w jednym ze sklepów sieci Foley. Rekord ten zawierałby numer karty
Johna Doe, datę, numer sklepu sieci Foley, liczbę wypłat i całkowitą kwotę
zakupów.
Zamiast przechowywać informacje sumaryczne w oddzielnej tabeli, można je
zapamiętać w tabeli już istniejącej, przeprowadzając tylko kilka zmian w jej
strukturze. Powiedzmy, że dotychczasowy układ tabeli był następujący:
CREATE TABLE CARDTRANS
(CardNumber char(20) NOT NULL,
TransactionDate date NOT NULL,
Location int NOT NULL,
Amount float NOT NULL)
Załóżmy też, że do utworzenia albo raportu, albo podsumowania, korzystamy
z poniższej instrukcji SELECT:
SELECT CardNumber, TransactionDate, Location, COUNT(*)
Ą' NumberTrans, SUM(Amount) Amount
FROM CARDTRANS
WHERE TransactionDate between  01/01/95 AND  02/01/95
GROUP BY CardNumber, TransactionDate, Location
Tabelę tę i zapytanie możemy przystosować do pracy z danymi sumarycznymi,
przeprowadzając tylko niewielkie zmiany. Poniżej przedstawiono zmodyfikowany
układ tabeli obsługującej podsumowania zintegrowane:
CREATE TABLE CARDTRANS
(CardNumber char(20) NOT NULL,
TransactionDate date NOT NULL,
Location int NOT NULL,
NumberTrans int NOT NULL,
Amount float NOT NULL)
Zauważmy, że pojawiła się tu nowa kolumna o nazwie NumberTrans. Co jest
pamiętane w jej wierszu szczegółowym? To samo, co w wierszu sumarycznym -
liczba wypłat dla danego wiersza tablicy. Dla wierszy szczegółowych pole
Rozdział 24 Zaawansowane programowanie w SQL 713
NumberTrans ma zawsze wartość 1 - dla zapewnienia możemy nawet zdefiniować
więzy DEFAULT. Dla wierszy sumarycznych, NumberTrans zawiera liczbę wypłat
reprezentowanych przez kluczowe pola wiersza. W naszym przykładzie z Johnem
Doe, liczba ta wynosi 3, ale może być dowolna w zakresie dopuszczalnym przez
typ danych int. Oto wspomniane wyżej zapytanie, przebudowane dla obsługi
zarówno wierszy szczegółowych, jak i sumarycznych:
SELECT CardNumber, TransactionDate, Location,
SUM(NumberTrans) nUMBERtRANS, SUM(Amount) Amount
FROM CARDTRANS
WHERE TransactionDate between  01/01/95 AND  02/01/95
GROUP BY CardNumber, TransactionDate, Location
Jedyną modyfikacją, jaką wprowadziliśmy, jest zamiana fragmentu COUNT(*)
(służącego do określania liczby wypłat) na fragment SUM(NumberTrans).
Teraz nasz kod będzie dobrze działać zarówno dla wierszy szczegółowych, jak
i sumarycznych - dzięki dołączeniu do wierszy szczegółowych kolumny
NumberTrans.
Typowy proces obejmuje podsumowanie danych z miesiąca i skierowanie tak
uzyskanych informacji sumarycznych do tabeli tymczasowej, z zastosowaniem
zapytania podobnego do poprzedniego. Następnie stare dane szczegółowe są
usuwane z tabeli CARDTRANS, po czym dane sumaryczne kieruje się z powrotem
do głównej tabeli. Wszystkie powyższe operacje serwer wykonuje jako pojedynczą
transakcję, co ma stanowić zabezpieczenie przed utratą danych w przypadku
wystąpienia nieprzewidzianych problemów.
UWAGA:
W niektórych platformach DBMS opisany powyżej proces może wiązać się
z usunięciem informacji o godzinie wypłaty z kolumny TransactionDate
(DataWypłaty) w trakcie podsumowania. W systemach takich, jak Sybase
i Microsoft SQL Server informacje o godzinie i dacie przechowuje się z użyciem
tego samego typu danych. Oznacza to, że kolumna TransactionDate może czasem
zawierać zarówno datę jak i godzinę w wierszach szczegółowych. Ponieważ
jednak żądane podsumowanie odbywa się względem daty, informacje dotyczące
godziny muszą zostać usunięte. W systemie SQL Server dane dotyczące godziny
usuwamy za pomocą następującej konstrukcji składniowej:
SELECT CardNumber, CONVERT(char(8),TransactionDate,
Ą' 112, Location, SUM(NumberTrans) NumberTrans,
Ą' SUM(Amount) Amount
FROM CARDTRANS
WHERE TransactionDate between  19950101 AND
Ą'  19950201
714 Część IV
GROUP BY CardNumber, CONVERT (char(8),TransactionDate,
Ą' 112)Location
Zwróćmy uwagę na użycie funkcji CONVERT do transformacji oryginalnego typu
pól kolumny TransactionDate na typ CHAR(8). System SQL Server może
automatycznie ponownie zamienić wartości tego typu, dzięki domyślnej (implicit)
konwersji z typu CHAR na typ DATETIME. Trzeci parametr funkcji CONVERT,
112, nadaje dacie format CCCCMMDD. Zapamiętanie jej w postaci typu
CHAR(8)spowoduje obcięcie informacji o godzinie.
Zwróćmy uwagę na to, że konieczne może okazać się zwiększenie rozmiaru takich
pól, jak Amount - aby mogły one pomieścić dane sumaryczne. Jeżeli np.
wcześniej zdefiniowaliśmy typ zmiennej Amount, jako dostępny w systemie
Sybase typ smallmoney, to teraz trzeba będzie go zmienić na bardziej pojemny
typ money.
Oto wygodny sposób uniknięcia kłopotów związanych z tworzeniem nowych tabel,
czy nawet baz danych na dane sumaryczne. Kosztem nieznacznego zwiększenia
wymagań pamięciowych, możemy teraz w jednolity sposób traktować dane
szczegółowe i sumaryczne.


Wyszukiwarka

Podobne podstrony:
07 Rozdzial 24 25
Rozdział 24
Wings of the wicked rozdział 24
Rozdział 24 Wydobycie mistrza
rozdzial (24)
Pan Wolodyjowski Rozdzial 24
rozdział 24 Belzebub przylatuje na planetę Ziemia po raz piąty
Rozdział 24 (tł Kath)
Rozdział 24
Bestia zachowuje sie źle Shelly Laurenston Rozdział 24

więcej podobnych podstron