Princip náhrady NOT EXISTS outer joinem aneb proč se někdy nepíše (+)
Princip náhrady NOT EXISTS outer joinem aneb proč se někdy nepíše (+)
Na uvedený trik jsme narazili na ORACLE8 u jednoho dotazu s NOT EXISTS, který trval neúměrně dlouho, ale po
nahrazení outer joinem došlo k mnohonásobnému zrychlení. Protože jsme nejdřív zpochybňovali, zda je toto řešení správně,
namodeloval jsem příklad, na kterém ukážeme, že to jde:
Mějme tabulku TZW, kde jsou pracovníci a jejich bydliště, a tabulku TZS, kde jsou pro pracovníka
uvedeny jeho funkce či dovednosti:
create table TZW(name varchar2(25), home varchar2(25) );
create table TZS(name varchar2(25), skill varchar2(25));
insert into TZW values ('Tom','TomHouse' );
insert into TZW values ('Jiri','JiriHouse' );
insert into TZW values ('Ruda','RudaHouse' );
insert into TZS values ('Tom','NEW');
insert into TZS values ('Jiri','PROGRAMMER');
Chceme vypsat všechny, kteří nejsou noví (skill není NEW). Úlohu řeší dotaz:
SQL> ed
Zapsáno file afiedt.buf
1 select TZW.name, home from TZW -- DOTAZ 1
2* where not exists (select 1 from TZS where TZW.name = TZS.name and TZS.skill = 'NEW')
SQL> /
NAME HOME
------------------------- -------------------------
Jiri JiriHouse
Ruda RudaHouse
Stejnou úlohu (za předpokladu, že sloupec TZS.name nemůže být NULL) ale řeší i dotazSQL> ed
Zapsáno file afiedt.buf
1 select TZW.name, home from TZW, TZS -- DOTAZ 2
2* where TZW.name = TZS.name(+) and TZS.skill(+) = 'NEW' and TZS.name is NULL
SQL> /
NAME HOME
------------------------- -------------------------
Jiri JiriHouse
Ruda RudaHouse
Tato konstrukce vypadá neobvykle v tom, že u outerjoinovaných tabulek se zpravidla píšou plusítka ke všem sloupcům. Proč
není u sloupce TZS.name ve třetí podmínce plusítko? Když ho tam dáme,...SQL> ed
Zapsáno file afiedt.buf
1 select TZW.name, home from TZW, TZS -- DOTAZ 3
2* where TZW.name = TZS.name(+) and TZS.skill(+) = 'NEW' and TZS.name(+) is NULL
SQL> /
NAME HOME
------------------------- -------------------------
Jiri JiriHouse
Ruda RudaHouse
Tom TomHouse
...tak to vrací všechny hodnoty! :-( Podívejme se na problém podrobněji.
Teorie
Ačkoli se SQL příkazy zapisují jako SELECT - FROM - WHERE, pro přijatelnou představu o tom, jak dotaz pracuje,existuje jednoduchá pomůcka: číst SQL select v pořadí FROM - WHERE - SELECT. Tato pomůcka se mi osvědčí vždy,když potřebuji pochopit smysl nějakého dotazu. Vychází z relační algebry, na které je jazyk SQL postaven. Proto nejprve pár
slov k relační algebře. Pro příklady použijme okřídlené ukázky z manuálů ORACLE, kde je mj. tabulka zaměstnanců
EMPa tabulka pracovišťDEPT.
Relační algebra je model, v němž sloupce nazýváme atributy a tabulka o n sloupcích
je představována relací, tedy podmnožinou kartézského součinu množin všech možných hodnot jednotlivých atributů. Množině
všech možných hodnot atributu říkáme doména atributu, takže relace je podmnožina kartézského součinu domén. Relace je tedy
množina uspořádaných n-tic. Poznámka: Je omylem si myslet, že relační databáze je
proto, že mohou být relace mezi tabulkami, např. 1:N. Relační databáze je relační, i když obsahuje jedinou tabulku!
Např. doména atributu, který je v databázi modelován sloupcemEMPNOtypuNUMBER(4), je množina
všech čtyřciferných a kratších čísel. Tabulce EMP odpovídá relace EMP. Jednotlivé n-tice relace představují řádky tabulky.Zaměřme se nyní na tři nejdůležitější operace relační algebry, kterými je vnitřní spojení,
selekce a projekce.
Vnitřní spojení
je kartézský součin dvou relací, z něhož se vyberou ty n-tice, které splňují
spojovací podmínku. Spojovací podmínka je od toho, aby se k sobě dostala smysluplná data. Tak vznikne nová relace, jejíž
počet atributů je roven součtu počtu atributů uvažovaných relací.
Selekce
znamená vybrání těch prvků relace, které splňují určitou podmínku. V relační algebře se zapíše pomocí kulatých závorek.
Např. vybrání všech zaměstnanců zaměstnaných od 1.1.2000 se zapíše jako EMP(hiredate>1.1.2000).
Možná vás napadne, že vnitřní spojení je vlastně kartézský součin s následnou selekcí. Ano, je to tak. Je však důležité -
obzvlášť pro pozdější pochopení outer joinů - si všimnout, že u selekce podle spojovací podmínky a "obyčejné" selekce se
pokaždé jedná o jiný důvod, proč se selekce dělá. Selekce podle spojovací podmínky vyjadřují omezení, která je nutné
zachovat kvůli smysluplnosti datového modelu, zatímco ostatní selekce vyjadřují omezení, která vyplývají až z logiky
uživatelské aplikace. Kdybychom například neprovedli selekci při spojení relací EMP a DEPT, dostaneme kartézský součin, kde
bude uveden každý zaměstnanec s každým pracovištěm, což ve většině případů budou nesmyslné údaje v jednom prvku relace.
Když neprovedeme na relaci EMP selekci EMP(hiredate>1.1.2000), zůstane relace EMP. Sice to nebude to, co požadoval např.
filtr v aplikaci, ale prvky relace budou stále odpovídat reálným objektům, v tomto případě zaměstnancům.
Zde se dostávám k tomu, proč tu uvádím syntaxi zápisu v relační algebře, když to je jazyk pouze teoretický. Vystihuje totiž
odlišnost mezi uvedenými dvěma druhy spojení. V relační algebře vypadá zápis pro přirozené spojení relací EMP a DEPT takto:
EMP*DEPT. Spojovací podmínka se neuvádí, protože se chápe jako samozřejmost. (Jiné než implicitní spojovací podmínky
nejspíš taky jde specifikovat, ale syntaxí relační algebry se tu nechci tak do hloubky zabývat.) Takže v relační algebře
vyjádříme lépe to, co chceme a nesnižujeme čitelnost dotazu samozřejmostmi.
Projekce
znamená vytvoření nové relace z původní vybráním určitých atributů z každé n-tice v
relaci. Nová relace má tedy stejný počet prvků, ale prvky relace jsou m-tice, kde
0<m<=n.
Po uvedení těchto operací vám nejspíš dojde souvislost mezi relační algebrou a SQL. Spojení se provádí ve
FROM, selekce veWHEREa projekce vSELECT(ano, ze začátku to mate, že? ale brzy si
zvyknete :-) Můžeme se spolehnout, že systém vrátí stejná data, jako kdyby nejprve spojil všechny tabulky, pak provedl
selekci a nakonec projekci (taková implementace by byla k smíchu, ale pro představu chování navenek je vhodná). SQL
samozřejmě umí o něco víc, než relační algebra, ale pro pochopení outer joinů nám tento model stačí. Přechodem od relační
algebry k SQL jsme se totiž připravili o rozlišování podmínek pro selekci na výše uvedené dva typy. V SQL (zvláště ORACLE)
totiž všechno namastíme do WHERE.Outer joiny
Zmíněný rozdíl začne být znatelný, když začneme používat vnější spojení (outer joiny). Vysvětlíme to na levém spojení. Levé
vnější spojení dvou relací R1, R2 je spojení, kde se pro každý prvek relace R1
- vyberou prvky relace R2, které splňují spojovací podmínku (vnitřní spojení)
- pokud žádný takový prvek neexistuje, bude aktuální prvek relace R1 přesto přítomen ve výsledné relaci a atributy z relace
- R2 budou doplněny hodnotami NULL
Zápis vnějšího spojení např. v MSSQL odpovídá teoretickému základu mnohem více než v ORACLE: pomocí LEFT OUTER JOINONse specifikuje spojovací podmínka už v klausuliFROMa nezhoršuje se tak čitelnost klausule
WHERE, která pak slouží jen pro aplikační podmínky. Bohužel na ORACLE se zapisují všechny podmínky do klausuleWHERE. Podmínky s(+)za názvem sloupce jsou spojovací podmínky pro vnější spojení, zatímco
podmínky bez (+) jsou podmínky aplikace (nebo i spojovací podmínky pro vnitřní spojení).Jak tedy fungují dotazy uvedené v úvodu?
Spojovací podmínka v dotazu 2 jeTZW.name = TZS.name(+) and TZS.skill(+) = 'NEW'.
Proto spojením tabulek vznikne
(Tom,TomHouse,Tom,NEW)
(Jiri,JiriHouse,NULL,NULL)
(Ruda,RudaHouse,NULL,NULL)
Pak se aplikuje selekce na podmínku TZS.name is NULL, projekce a vznikne správný výsledek(Jiri,JiriHouse)
(Ruda,RudaHouse)
Slovně vyjádřeno: jestliže nechceme dát do výsledku osoby, které splňují určitou podmínku, připojíme jinou tabulku podle
této podmínky. Pak ponecháme jen ty řádky, v nichž za nás outer join dosadil NULL, protože víme, že dosadil NULL tehdy a
jen tehdy, když daná podmínka nebyla splněna.
Naproti tomu spojovací podmínka v dotazu 3 je TZW.name = TZS.name(+) and TZS.skill(+) = 'NEW' and TZS.name(+) isNULL.
Proto spojením tabulek vznikne
(Tom,TomHouse,NULL,NULL)
(Jiri,JiriHouse,NULL,NULL)
(Ruda,RudaHouse,NULL,NULL)
Pro prvek(Tom,TomHouse)tabulkyTZWse nenajde odpovídající záznam v tabulceTZS,
protože žádný záznam v tabulceTZSnesplňuje třetí podmínku.
Kromě spojovací podmínky už žádná další podmínka není, takže po aplikaci projekce vznikne nechtěný výsledek
(Tom,TomHouse)
(Jiri,JiriHouse)
(Ruda,RudaHouse)
Odkazy
jeden dotaz na http://www.google.com
něco k teorii [PDF]
Související články
Související epizody CZ Podcastu
- #261 · Leadership
O vedení lidí a týmů.