25.Rozdziaę 24.pdf

Rozdział 24

Zaawansowane programowanie

w SQL

Rozdziału niniejszego nie kontynuujemy od tego miejsca, wktórym

zakończyliśmy rozdział 5. Spora luka dzieli podstawową izaawansowaną

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. Wprzykładach zamieszczonych wtym 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 znaleźć 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 znaleźć w dokumentacji

posiadanej platformy SQL. Omówiliśmy szereg zagadnień, związanych z językiem

670

Część IV

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ń wSQL:

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’

SQL

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 iMicrosoft, 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

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.

device ) za pomocą komendy DISK INIT :

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 iindeks INVOICES02 znajdują się teraz wróż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 iutworzenie wramach 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óź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

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

Duże tabele umieszczać na różnych napędach, zwłaszcza jeżeli mają być one

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ć wdialekcie InterBase zużyciem komendy

DESCENDING , na przykład:

CREATE DESCENDING INDEX INVOICES03 ON INVOICES (Amount)

Dzięki temu szybciej będą przetwarzane zapytania w rodzaju:

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: