zlaczenia.pdf

X Konferencja PLOUG

Kościelisko

Październik 2004

Złączenia – pułapki i nowe możliwości

Jarosław Gramacki, Artur Gramacki

Uniwersytet Zielonogórski

Instytut Informatyki i Elektroniki

ul. Podgórna 50, 65-246, Zielona Góra

e-mail: j.gramacki@iie.uz.zgora.pl, a.gramacki@iie.uz.zgora.pl

Abstrakt

Celem niniejszego artykułu jest omówienie dostępnych w systemie Oracle rodzajów połączeń (ang. joins) tabel relacyj-

nych. Zagadnienie łączenia tabel jest tak stare, jak zasady, na których opiera się model relacyjny. Niemniej liczba do-

stępnych w systemie Oracle siedmiu (!) różnych terminów posiadających w swej nazwie frazę „join” (Equijoins, Self

Joins, Cartesian Products, Inner Joins, Outer Joins, Antijoins, Semijoins), powoduje, że pozornie proste zagadnienia

stają się często źródłem wielu błędów. Ważnym przykładem zastosowania połączeń są też tzw. zapytania hierarchiczne,

w których połączenia występują w sposób niejawny. Sprawę komplikuje ponadto wprowadzona niedawno (począwszy

od wersji 9i) obsługa nowej składni połączeń wynikającej z implementacji w bazie Oracle kolejnych elementów stan-

dardu SQL/99. Niektóre opisane tam możliwości nie były ponadto (w sposób bezpośredni) dostępne we wcześniejszych

wersjach bazy. W referacie przedstawiono opisane wyżej zagadnienia na odpowiednio dobranych przykładach. Pokaza-

no również, że nawet pozornie proste zapytania kierowane do bazy mogą wymagać budowy ciekawych zapytań z połą-

czeniami. Z uwagi na przyjęty zakres pracy, nie zajmowano się problemami „systemowej” optymalizacji zapytań. Po-

minięto więc takie zagadnienia jak np. indeksacja, partycjonowanie, widoki zmaterializowane, podpowiedzi dla optyma-

lizator (ang. hints), itd.

Informacje o autorach:

dr inż. Artur Gramacki – pracuje w Instytucie Informatyki i Elektroniki Uniwersytetu Zielonogórskiego na stanowisku

adiunkta. Jego zainteresowania koncentrują się wokół szeroko rozumianych zagadnień związanych z bazami danych,

w szczególności firmy Oracle. Oprócz prowadzenia zajęć dydaktycznych stara się wykorzystywać swoją wiedzę uczest-

nicząc w różnych projektach informatycznych z tego zakresu. Brał udział w trzech projektach, których celem było przy-

gotowanie systemów wspomagających działalność Uniwersytetu Zielonogórskiego.

dr inż. Jarosław Gramacki – jest pracownikiem naukowym w Instytucie Informatyki i Elektroniki Uniwersytetu Zielo-

nogórskiego. Zajmuje się projektowaniem, wykonywaniem oraz wdrażaniem aplikacji bazodanowych usprawniających

szeroko rozumiane zarządzanie Uczelnią. Od wielu lat prowadzi również zajęcia dydaktyczne dotyczące projektowania

baz danych, działania i administrowania systemami zarządzania bazami danych oraz wykorzystania technologii Oracle

w budowie aplikacji użytkowych.

260

Jarosław Gramacki, Artur Gramacki

Złączenia – pułapki i nowe możliwości

261

1. Wstęp

Dane zgromadzone w bazie danych są użyteczne tylko wówczas, gdy potrafimy z nich popraw-

nie i bezbłędnie skorzystać. Jedyną metodą dostępu do danych bazy relacyjnej jest język SQL i to

niezależnie od platformy programistycznej, z której korzystamy [ORASQL]. Zapytania w tym

języku pojawiają się zawsze – nawet, gdy są bardzo skrzętnie ukryte przed użytkownikiem.

Ważnym zagadnieniem w ramach języka SQL są połączenia relacji (tabel) pomiędzy sobą i w

konsekwencji obsługa tychże połączeń z poziomu języka SQL. Niniejszy artykuł poświęcony jest

w całości omówieniu wybranych aspektów połączeń w relacyjnej bazie danych Oracle.

2. Model danych 1

Przy opisie jakichkolwiek zagadnień dotyczących języka SQL konieczny jest odpowiedni, ade-

kwatny do opisywanych problemów, schemat tabel relacyjnych wraz z ich powiązaniami. Na Ry-

sunku 1 przedstawiono wykorzystywany w pracy model relacyjny. Większość tabel tworzy kla-

syczny wręcz model używany w wielu pracach dotyczących relacyjnych baz danych i pochodzi

z oryginalnego schematu demonstracyjnego systemu Oracle o nazwie HR (ang. human resources )

– szczegółowo omawiany jest on w [HRSchema]. Wymieńmy jednak kilka jego „niedogodności”,

które będą istotne w pracy:

1. Kolumna Employees.manager_ID dopuszcza wartość NULL .

2. Kolumna Employees.department_ID dopuszcza wartość NULL .

3. Kolumna Departments.manager_ID dopuszcza wartość NULL .

4. Wygodna na etapie modelowania, ale sprawiająca trudności w użyciu i podatna na błędy, re-

lacja wewnętrzna Employees.employee_ID -> Employees.manager_ID .

5. W tabeli Sport_enrollment (patrz następny akapit) pracownik może zapisać się do sekcji

sportowej, która akurat nie jest prowadzona w danym sezonie (np. sekcja tenisa jest ofero-

wana w sezonie letnim).

Na potrzeby pracy rozszerzono ten model 2 . Tabele Sports oraz Sport_enrollment (nie wy-

stępują one w oryginalnym schemacie HR ) opisują prowadzone w firmie „ligi” sportowe i pra-

cowników, którzy zapisali się na dane rozgrywki. Potrójny klucz primary (na wszystkich kolum-

nach tabeli Sport_enrollment ) utworzono aby „bezkosztowo” uniemożliwić wielokrotne zapisa-

nie się tego samego pracownika na te same rozgrywki.

Część z wymienionych wyżej właściwości (1, 2, 3) może być efektem celowo przyjętych zało-

żeń i z formalnego punktu widzenia nie stanowi błędu.

Właściwość 4 można by zastąpić odpowiednią strukturą typu m aster-detail (mniej podatna na

błędy), jednak wtedy należałoby z góry ustalić ilość poziomów podległości pracowników.

Właściwość 5 jest wynikiem pewnego niedbalstwa projektanta bazy. Bez problemu można było

utworzyć odpowiednie ograniczenie, np. poleceniem:

ALTER TABLE Sport_enrollment

ADD CONSTRAINT xxx_FK FOREIGN KEY (discipline, season_ID)

REFERENCES Sports (discipline, season_ID);

1 W pracy wszystkie nazwy tabel pisane są małymi literami + pierwsza litera duża (np. Departments ), nazwy ko-

lumn w całości małymi literami (np. title ) , aliasy dla kolumn to jedna bądź dwie duże litery (np. J, SE ).

2 Wszystkie skrypty wykorzystywane w pracy można otrzymać kontaktując się z autorami.

262

Jarosław Gramacki, Artur Gramacki

LOCATIONS

COUNTRIES

LOCATION_ID

<pk> NUMBER(4)

not null

COUNT RY_ID

<pk> CHAR(2)

not null

ST REET _ADDRESS

VARCHAR2(40) nul l

COUNT RY_NAME

VARCHAR2(40) nul l

POST AL_CODE

VARCHAR2(12) nul l

COUNTRY_ID = COUNTRY_ID

RE GION_ ID

<fk> NUMB E R

n ul l

CIT Y

VARCHAR2(30) not null

ST AT E_PROVINCE

VARCHAR2(25) nul l

COUNT RY_C_ID_PK

C OUNT RY_ID

<fk> CHAR(2)

nul l

LOC_ID_PK

LOC_CIT Y_IX

LOC_COUNT RY_IX

LOC_STATE_PROVINCE_IX

REGION_ID = REGION_ID

REGIONS

RE GION_ ID

<pk> NUMBER

not null

LOCATION_ID = LOCATION_ID

RE GION_ NA ME

V A RCHA R2 (25 ) n ul l

REG_ID_PK

DEPARTMENTS

DEPART MENT _ID

<pk>

NUMBER(4)

not null

EMPLOYEE_ID = MAN AGER _ID

DEPART MENT _NAME <ak>

VARCHAR2(30) not null

MANAGER_ID

<fk>

NUMBER(6)

nul l

LOCAT ION_ID

<fk,ak> NUMBER(4)

nul l

DEPT _ID_PK

DE P T _L OCA T ION_ IX

DEPARTMENT_ID = DEPARTMENT_ID

EMPLOYEES

EMPLOYEE_ID

<pk> NUMBER(6)

not null

JOB_HISTORY

FIRST _NAME

VARCHAR2(20) nul l

LAST_NAME

VARCHAR2(25) not null

EMPLOYEE_ID

<pk,fk> NUMBER(6)

not null

EMPLOYEE_ID = EMPL OYEE_ID

<ak> VARCHAR2(25) not null

START_DATE

<pk>

DAT E

not null

PHONE_NUMBER

VARCHAR2(20) nul l

END_DAT E

DAT E

not null

HIRE_DATE

DATE

not null

JOB_ID

<fk>

VARCHAR2(10) not null

J OB_ID

<fk> VARCHAR2(10) not null

DEPART MENT _ID <fk>

NUMBER(4)

nul l

SALARY

NUMBER(8,2) null

JHIST_EMP_ID_ST_DATE_PK

JHIST _DEPART MENT _IX

JHIST_EMPLOYEE_IX

J HIS T _JOB _IX

COMMISSION_PCT

NUMBER(2,2) nul l

MANAGER_ID

<fk> NUMBER(6)

nul l

DEPART MENT _ID

<fk> NUMBER(4)

nul l

EMP_EMP_ID_PK

EMP_DEPARTMENT_IX

EMP_JOB_IX

EMP_MANAGER_IX

EMP_NAME_IX

JOB_ID = JOB_ID

J OB _I D = J O B_ ID

JOBS

JOB_ID

<pk> VARCHAR2(10) not null

J OB _T I T LE

< ak> V A RCHA R2 ( 35 ) n ot n u ll

EMPLOYEE_ID = MAN AGER _ID

EMPLOYEE_ID = EMPL OYEE_ID

MIN_SALARY

NUMBER(6)

nul l

EMPLOYEE_ID = EMPL OYEE_ID

MAX_SALARY

NUMBER(6)

nul l

JOB_ID_PK

SALARY_HISTORY

EMPLOYEE_ID <pk,fk> NUMBER(7)

not null

SPORT_ENROLLMENT

SALARY

NUMBER(11,2) not null

SPORTS

DISCIPLINE <pk> VARCHAR(20) not null

SEASON_ID <pk> NUMBER(3)

END_DAT E

<pk>

DAT E

not null

DISCIPLINE

<pk,fk> VARCHAR2(20) not null

SEASON_ID

<pk,fk> NUMBER(3)

not null

SHIST_EMP_EDATE_PK

SHIST_EMPLOYEE__IX

EMPLOYEE_ID <pk,fk> NUMBER(7)

not null

S_ENROLL_DIS_SEA_EMP_PK

S_ENROLL_DIS_IX

S_ENROLL_SEA_IX

S_ENROLL_EMP_IX

D ISC IP LI NE = D ISC IP LI NE

SEASON _ID = SEASON _ID

Patrz opis w tekście

Rys. 1. Model relacyjny HR uzupełniony dodatkowe tabele (zaznaczone ciemniejszym kolorem)

Ponieważ jednak do czasu zauważenia błędu wprowadzono (tak zakładamy) dużą ilość „brud-

nych” danych (pracownicy pozapisywali się do sekcji, do których chcieliby należeć w wygodnych

dla nich okresach, interpretując wprowadzone wpisy jako swoistą listę życzeń) więc dodanie ogra-

niczenia nie wchodzi (przynajmniej na razie) w grę. Innym, często spotykanym w praktyce powo-

dem niemożności usunięcia opisanego błędu jest brak odpowiednich uprawnień. Osobami, które

najczęściej budują zapytania do bazy są programiści raportów, którzy w poprawnie administrowa-

nym środowisku mają jedynie uprawnienia SELECT do obiektów schematu.

W stosunku do oryginalnego schematu HR usunięto również występujące w nim jawne „błędy”

projektowe. W tabeli Departments dodano ograniczenie unique dla kolumn department_name

oraz location_ID . W tabel Jobs dodano ograniczenie unique dla kolumny job_title . Oba ogra-

niczenia oznaczono na schemacie symbolem <ak> .

Dodano również tabelę Salary_history . W oryginalnym schemacie HR można zapamiętywać

historię zmian miejsca pracy pracownika (kolumna Employees.department_ID ) lub zmian stano-

wiska (kolumna Employees.job_ID ). Jest to implementacja tzw. wersjowania relacji. Brakuje

natomiast możliwości zapamiętywania np. zmian płacy pracownika (kolumna Employ-

Złączenia – pułapki i nowe możliwości

263

ees.salary ), czyli tzw. wersjowania atrybutów. Obsługę tą zapewnia właśnie tabela Sala-

ry_history . Dane do niej zapisywane są z użyciem analogicznego wyzwalacza do tego, który w

oryginalnej wersji schematu HR obsługuje wpisy do tabeli Job_history .

Abstrahując jednak od powyższej dyskusji, w praktyce często przychodzi nam pracować na

modelu takim jaki jest (nie mamy możliwości zmiany modelu). Dużego znaczenia nabiera wtedy

poprawne konstruowanie zapytań opartych na danym modelu.

3. Połączenia w systemie Oracle

Połączenia zajmują ważne miejsce w każdej bazie relacyjnej i są to bardzo newralgiczne ele-

menty. Dokumentacja systemu Oracle poświęca im jednak stosunkowo niewiele miejsca, dodat-

kowo prawie zupełnie nie zwracając uwagi na różne związane z nimi niuanse, gdyż prezentowane

tam przykłady są typu „zawsze zadziała”. Ponadto w oryginalnej dokumentacji systemu Oracle

pojawia się siedem pojęć, w których używana jest fraza „join” 3 , zestawiono je poniżej. Zdaniem

autorów część z nich, jakkolwiek poprawna, wprowadza zbędne zamieszanie w i tak już skompli-

kowanej materii. W nawiasach podano również inne, pojawiające się w różnych publikacjach na-

zwy, które jednak nie pojawiają się w dokumentacji Oracle. W Tabeli 1 krótko opisano każde

z połączeń:

•

(Natural Joins),

•

Equijoins,

•

Self Joins,

•

Cartesian Products,

•

Inner Joins (Simple Joins),

•

Outer Joins,

•

Antijoins,

•

Semijoins,

•

(Theta Joins).

Tabela 1. Różne używane pojęcia związane z połączeniami relacyjnymi

Nazwa

Opis

Natural Joins

Połączenie dwóch lub więcej tabel poprzez kolumny o tych samych na-

zwach. W praktyce chodzi z reguły o połączenia równościowe.

Equijoins

Dotyczy połączenia opisanego operatorem porównania. Nie wspomina się

o żadnych dodatkowych zagadnieniach / założeniach szczegółowych.

Self Joins

Dotyczy połączenia w obrębie jednej tabeli. Tabela ta musi więc dwa razy

pojawić się w klauzuli FROM (z reguły z dwoma różnymi aliasami). Nie

wspomina się tu jednak o ew. strukturze hierarchicznej, która z reguły jest

opisywana poprzez relację zdefiniowaną w jednej tabeli dla dwóch ko-

lumn (a te wymagają z reguły zastosowania specjalizowanych operato-

rów).

3 Wszędzie użyto pisowni zgodnej z używaną w oryginalnej dokumentacji firmy Oracle.

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: