pátek 26. září 2003

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 dotaz

SQL> 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ů EMP a 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 sloupcem EMPNO typu NUMBER(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 ve WHERE a projekce v SELECT (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

  1. vyberou prvky relace R2, které splňují spojovací podmínku (vnitřní spojení)
  2. 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 JOIN ON se specifikuje spojovací podmínka už v klausuli FROM a 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 klausule WHERE. 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 je TZW.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(+) is NULL. Proto spojením tabulek vznikne

(Tom,TomHouse,NULL,NULL)
(Jiri,JiriHouse,NULL,NULL)
(Ruda,RudaHouse,NULL,NULL)

Pro prvek (Tom,TomHouse) tabulky TZW se nenajde odpovídající záznam v tabulce TZS, protože žádný záznam v tabulce TZS nesplň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]

České blogy o Jave

Mám pocit, že tady u nás je blogování populární a tak bloguje kdo může lépe řečeno umí HTML a i to někdy nemusí platit. Pokud se porozhlédnu kolem sebe těch opravdu odborných blogu moc není, doufám že nikoho neurazím když řeknu, že by se daly doslova spočítat na deseti prstech. Trochu mi chybí více pestrobarevnosti v diskutovaných tématech, jinými slovy originálnosti. Většina oněch odborných blogu se věnuje webu v jakékoliv podobě a tak trochu mi chybí blogy orientované na programování a vše kolem něj.

Velkou radost mi proto udělal Tomáš Kouba nejenom tím, že publikuje blog věnovaný Jave, ale hlavně obsahem. Blog na mě udělal celkem dojem a proto si Java blog vysloužil odkaz a tuto upoutávku, snad to bude povzbuzení do další práce. Takže novému blogu věnovanému Jave přeji to nejdůležitější - kvalitní obsah, předpoklady k tomu určitě má.

Opera 7.20

V Norsku se nezahálí, společnost Opera Software ASA stojící za prohlížečem Opera, uvolnila novou verzi tohoto populárního prohlížeče označenou jako 7.20. Letmé pročtení changelogu ve mě vyvolalo pocit, že se jedná spíše o servisní "release".

Zajímavě ovšem vypadá implementace parseru podporujícího CSS3(na CSS3 si ovšem musíme ještě nějaký ten pátek počkat) a implementace voice-family z CSS2. Informace via. zive.cz( Finální Opera 7.2 – sehranější orchestr, několik nových not)

čtvrtek 25. září 2003

Krásná doporučení XHTML(HTML) a žádná odezva

Na serveru interval.cz vychází seriál o doporučení XHTML, v posledním díle XHTML - titulek, skupiny řádků a řádky tabulek jsou mimo jiné probrány skupiny tabulky(thead,tbody a tfoot) a atribut char. Bohužel ani jeden z výše uvedeného není v dnešních prohlížečích interpretován tak jak by měl být! Atribut char pojali tvůrci prohlížečů jako zbytečnou přítěž a tak jej soustavně ignorují. Stejně jako chování k možnosti rolovacího těla vnitřku tabulky(tbody), případně opakovaného tiskového výstupu hlavičky(thead) a patičky(tfoot) tabulky na vícestránkových tabulkách. Snad nemá cenu dodávat, že obé bylo již zaneseno v doporučení HTML 4.01.

Chování prohlížečů je prostě fakt a nic sním neuděláme, ale v záplavě nových funkčností, které na nás chrlí ten či onen, je ignorace prostých základů spíše k pláči.