Prohlašuji, že jsem svou semestrální práci vypracoval samostatně. Všechny zdroje, ze kterých jsem čerpal, jsou uvedeny v části Odkazy.
Systém je určen pro spíše uzavřenou skupinu uživatelů - jezdců na kole - a slouží pro sdílení informací o jezdcích, závodech, komponentech a obchodech s cyklistickým zbožím. Základem je seznam jezdců, u kterých se sleduje jejich jméno, příjmení, přezdívka a e-mail. Existuje několik jezdeckých disciplín a každý jezdec má alespoň jednu oblíbenou. Ve většině disciplín se jezdí závody, kterých se jezdci mohou zúčastnit. Seznam lokací udržuje informace o oblastech, kde se dají provozovat cyklistické sporty. Další částí je seznam komponent - na výběr jsou 3 komponenty - rám, vidlice a plášť. Každý komponent je určen trojicí: výrobce, model a rok. Komponent má poté další vlastnosti, které zavisejí na druhu komponentu (např. zdvih u vidlice, poloměr kola, pro který je vidlice určena, velikost rámu, poloměr kola, pro který je rámurčen apod.). Jezdec může ke konkrétnímu komponentu napsat svou zkušenost, a to zadáním hodnot poměr cena/výkon, celkové hodnocení a může napsat i nepovinné slovní hodnocení. Mezi jezdcem a komponentem může být navázán vztah vyjadřující skutečnost, že jezdec v současnosti daný komponent využívá (má ho na svém kole). Udržuje se také seznam obchodů s cyklistickými potřebami. Obchod je identifikován svým názvem, povinným parametrem je adresa a jako nepovinný parametr je přidán odkazna případné www stránky. Jezdec může napsat hodnocení obchodu, kde ohodnotí sortiment, ceny, přívětivost a může napsat i slovní ohodnocení. V systému je ještě seznam nákupu. Každý nákup je identifikován čtveřicí - datum, komponent, jezdec a obchod. Dalším parametrem nákupu je cena.
- Celková suma nákupů
U každého jezdce budeme udržovat částku, kterou utratil za komponenty. Hodnota bude součtem všech nákupů jezdce, které budou v systému. Částka se bude ukládat jako atribut jezdce a její hodnota bude aktualizována pomocí triggeru.
- Poloměry plášťů
Je nutné zajistit, že poloměry plášťů, které jezdec využívá odpovídají poloměrům, pro které jsou určeny vidlice a rámy používané jezdcem. Tedy pokud jezdec používá vidlici pro poloměr 26", pak musí také používat plášť s poloměrem 26". Obdobně pro rám. Naopak platí, že nemůže používat plášť, který nepasuje do žádné používané vidlice a do žádného používaného rámu. Kontrolu je třeba provádět při přidávání, odebírání a upravovaní komponentů.
- Závod jen v oblíbené disciplíně
Při vkládání informace o účasti na závodu je nutno zajistit, že se jezdec může účastnit pouze závodů v disciplíně, kterou má uvedenu jako oblíbenou.
- Platný rok nákupu
Při zaznamenávání nákupu komponentu se kontroluje, zda je rok z datumu nákupu vyšší nebo roven roku uvedení komponentu na trh.
- Kontrola formátu emailu
Při vkládání nového jezdce nebo při úpravách existujícího se kontroluje email, zda má platný formát emailové adresy.
- Hodnoty hodnocení
Systém zajistí, že hodnoty parametrů poměr cena výkon a hodnocení v seznamu zkušeností a parametrů sortiment, ceny a přívětivost v seznamu hodnocení obchodů jsou celá čísla z rozsahu 1 až 10.
Popis smyček:
Jezdec-Dispciplina-Zavod: Jezdec má různé oblíbené dispciplíny a může se v nich účastnit závodů. Na omezení počtu závodů v disciplínách nejsou kladeny omezení, tedy nemusí se účastnit žádného, ale může se také účastnit i více než jednoho.
Jezdec-Komponent-Nakup: Jezdec může používat i komponenty u kterých nezaznamenal jejich nákup. Tato možnost je tu například proto, že při koupi celého kola nelze rozlišit cenu jednotlivých komponentů. Stejně tak je možné, že jezdec komponent zakoupil, ale již nepoužívá (např. ho prodal).
Jezdec-Komponent-Zkusenost: Důvodem této smyčky je opět skutečnost, že vztah pouziva reprezentuje současný stav, zatímco zkusenost vyjadřuje to, že daný jezdec daný komponent někdy používal.
Zkusenost-Komponent-Nakup: Odůvodnění je stejné jako u předchozí smyčky.
Jezdec-Hodnoceni-Obchod-Nakup: Jezdec může napsat Hodnoceni Obchodu i když neprovedl ještě žádný Nakup. V reálné situaci to může být způsobeno například tím, že koupil věc, která se do naší databáze nezaznamenává, či se byl v Obchode jen podívat.
Seznam názvů obchodů s částkami v nich utracených za nákupy.
SELECT nazev, COALESCE(soucet,0) castka FROM obchod LEFT OUTER JOIN (SELECT ob_nazev nazev, SUM(cena) soucet FROM nakup GROUP BY(ob_nazev)) USING(nazev);
Plan hash value: 3279846785 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 250 | 5 (40)| 00:00:01 | |* 1 | HASH JOIN OUTER | | 5 | 250 | 5 (40)| 00:00:01 | | 2 | INDEX FULL SCAN | PK_OBCHOD | 5 | 50 | 1 (0)| 00:00:01 | | 3 | VIEW | | 3 | 120 | 3 (34)| 00:00:01 | | 4 | HASH GROUP BY | | 3 | 36 | 3 (34)| 00:00:01 | | 5 | TABLE ACCESS FULL| NAKUP | 7 | 84 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBCHOD"."NAZEV"="from$_subquery$_002"."NAZEV"(+)) Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
NAZEV CASTKA -------------------------------------------------- ---------------------- Madla Sport 18400 Dookie 14549 901 Bikes 6600 Bike Kellys Seky 0 Katmar 0 5 rows selected
Odezva na dotaz je správná.
Dotaz používá náročné vnější polospojení. Zkusíme ho zefektivnit použitím vnořeného dotazu.
SELECT nazev, COALESCE((SELECT SUM(cena) FROM nakup N WHERE N.ob_nazev=O.nazev),0) castka FROM obchod O;
Plan hash value: 1153909728 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 50 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 12 | | | |* 2 | TABLE ACCESS FULL| NAKUP | 2 | 24 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_OBCHOD | 5 | 50 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("N"."OB_NAZEV"=:B1) Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 26 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
NAZEV CASTKA -------------------------------------------------- ---------------------- 901 Bikes 6600 Bike Kellys Seky 0 Dookie 14549 Katmar 0 Madla Sport 18400 5 rows selected
Odezva je správná. Od první verze se ale liší pořadím.
Druhá verze je znatelně efektivnější. Verze 2 je opravdu efektivnější, cena dotazu je o 3/8 levnější. Množinový rozdíl je méně efektivní, než použití poddotazu s negativním predikátem. V tomto případě se tedy vyplatí použít druhou verzi dotazu.
SELECT O.nazev, (SELECT COALESCE(SUM(N.cena), 0) FROM nakup_obj N WHERE REF(O)=N.ob_nazev) AS castka FROM obchod_obj O;
Plan hash value: 2248251368 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 160 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 19 | | | |* 2 | TABLE ACCESS FULL| NAKUP_OBJ | 2 | 38 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | OBCHOD_OBJ | 5 | 160 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("N"."SYS_NC00008$"=:B1) Statistics ----------------------------------------------------------- 0 recursive calls 0 db block gets 30 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 0 parse count (hard) 0 parse count (failures)
NAZEV CASTKA -------------------------------------------------- ---------------------- Dookie 14549 901 Bikes 6600 Katmar 0 Bike Kellys Seky 0 Madla Sport 18400 5 rows selected
Odpovídá očekávané odezvě. Pořadí řádků je rozdílné od předchozích dvou verzí.
Nejvýhodnější je Verze 2. Ukázalo se, že v tomto případě je výhodnější použití hodnotových cizích klíčů namísto objektových referencí.
Zobrazit všechna hodnocení obchodů a nákupy. Ke každému nákupu přiřadit všechna hodnocení vztahující se k obchodu, kde byl nákup proveden. Zobrazit i nákupy v obchodech, které nebyly hodnoceny a hodnocení obchodů, kde nebyl ještě proveden nákup.
SELECT * FROM nakup FULL OUTER JOIN hodnoceni USING(ob_nazev);
Plan hash value: 1177690318 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 4242 | 8 (13)| 00:00:01 | | 1 | VIEW | | 14 | 4242 | 8 (13)| 00:00:01 | | 2 | UNION-ALL | | | | | | |* 3 | HASH JOIN OUTER | | 12 | 852 | 5 (20)| 00:00:01 | | 4 | TABLE ACCESS FULL| NAKUP | 7 | 357 | 2 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL| HODNOCENI | 7 | 140 | 2 (0)| 00:00:01 | |* 6 | HASH JOIN ANTI | | 2 | 58 | 4 (25)| 00:00:01 | | 7 | TABLE ACCESS FULL| HODNOCENI | 7 | 140 | 2 (0)| 00:00:01 | | 8 | INDEX FULL SCAN | PK_NAKUP | 7 | 63 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("NAKUP"."OB_NAZEV"="HODNOCENI"."OB_NAZEV"(+)) 6 - access("NAKUP"."OB_NAZEV"="HODNOCENI"."OB_NAZEV") Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 16 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
OB_NAZEV CENA DATUM JE_ID_JEZDCE KO_MODEL KO_VYROBCE KO_ROK_VYROBY SORTIMENT CENY PRIVETIVOST SLOVNI_HODNOCENI JE_ID_JEZDCE -------------------------------------------------- ---------------------- ------------------------- ---------------------- -------------------------------------------------- -------------------------------------------------- ------------- ---------------------- ---------------------- ---------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- Dookie 4000 10.09.03 2 Dragstar LeaderFox 2003 2 2 1 Pohodovej majitel. 1 Dookie 450 29.03.04 1 Minion DH Maxxis 2004 2 2 1 Pohodovej majitel. 1 Dookie 10099 29.03.04 1 DirtJumper III Marzocchi 2004 2 2 1 Pohodovej majitel. 1 901 Bikes 6000 03.02.04 2 Blackhorse Blackhorse 2003 1 3 2 1 901 Bikes 600 29.03.04 1 Minion DH Maxxis 2004 1 3 2 1 Dookie 4000 10.09.03 2 Dragstar LeaderFox 2003 2 1 1 4 Dookie 450 29.03.04 1 Minion DH Maxxis 2004 2 1 1 4 Dookie 10099 29.03.04 1 DirtJumper III Marzocchi 2004 2 1 1 4 Madla Sport 400 10.09.05 3 High Roller Maxxis 2004 4 1 3 3 Madla Sport 18000 10.09.05 3 P2 Specialized 2005 4 1 3 3 Dookie 4000 10.09.03 2 Dragstar LeaderFox 2003 2 2 1 6 Dookie 450 29.03.04 1 Minion DH Maxxis 2004 2 2 1 6 Dookie 10099 29.03.04 1 DirtJumper III Marzocchi 2004 2 2 1 6 Katmar 3 3 4 3 Katmar 3 2 4 1 15 rows selected
Odezva je správná.
Nyní provedeme dotaz jinou technikou. Místo plného vnějšího spojení provedeme dvě jednostranná vnější spojení a ty potom spojíme množinovým sjednocením.
(SELECT * FROM nakup LEFT OUTER JOIN hodnoceni USING(ob_nazev)) UNION (SELECT * FROM nakup RIGHT OUTER JOIN hodnoceni USING(ob_nazev));
Plan hash value: 1670255159 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 24 | 1512 | 11 (64)| 00:00:01 | | 1 | SORT UNIQUE | | 24 | 1512 | 11 (64)| 00:00:01 | | 2 | UNION-ALL | | | | | | |* 3 | HASH JOIN OUTER | | 12 | 756 | 5 (20)| 00:00:01 | | 4 | TABLE ACCESS FULL| NAKUP | 7 | 301 | 2 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL| HODNOCENI | 7 | 140 | 2 (0)| 00:00:01 | |* 6 | HASH JOIN OUTER | | 12 | 756 | 5 (20)| 00:00:01 | | 7 | TABLE ACCESS FULL| HODNOCENI | 7 | 140 | 2 (0)| 00:00:01 | | 8 | TABLE ACCESS FULL| NAKUP | 7 | 301 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("NAKUP"."OB_NAZEV"="HODNOCENI"."OB_NAZEV"(+)) 6 - access("HODNOCENI"."OB_NAZEV"="NAKUP"."OB_NAZEV"(+)) Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 20 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
OB_NAZEV CENA DATUM QCSJ_C000000000300000 KO_MODEL KO_VYROBCE KO_ROK_VYROBY SORTIMENT CENY PRIVETIVOST SLOVNI_HODNOCENI QCSJ_C000000000300001 -------------------------------------------------- ---------------------- ------------------------- ---------------------- -------------------------------------------------- -------------------------------------------------- ------------- ---------------------- ---------------------- ---------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- 901 Bikes 600 29.03.04 1 Minion DH Maxxis 2004 1 3 2 1 901 Bikes 6000 03.02.04 2 Blackhorse Blackhorse 2003 1 3 2 1 Dookie 450 29.03.04 1 Minion DH Maxxis 2004 2 1 1 4 Dookie 450 29.03.04 1 Minion DH Maxxis 2004 2 2 1 Pohodovej majitel. 1 Dookie 450 29.03.04 1 Minion DH Maxxis 2004 2 2 1 6 Dookie 4000 10.09.03 2 Dragstar LeaderFox 2003 2 1 1 4 Dookie 4000 10.09.03 2 Dragstar LeaderFox 2003 2 2 1 Pohodovej majitel. 1 Dookie 4000 10.09.03 2 Dragstar LeaderFox 2003 2 2 1 6 Dookie 10099 29.03.04 1 DirtJumper III Marzocchi 2004 2 1 1 4 Dookie 10099 29.03.04 1 DirtJumper III Marzocchi 2004 2 2 1 Pohodovej majitel. 1 Dookie 10099 29.03.04 1 DirtJumper III Marzocchi 2004 2 2 1 6 Katmar 3 2 4 1 Katmar 3 3 4 3 Madla Sport 400 10.09.05 3 High Roller Maxxis 2004 4 1 3 3 Madla Sport 18000 10.09.05 3 P2 Specialized 2005 4 1 3 3 15 rows selected
Výstupy se liší pořadím. V druhém případě také přibyly automaticky vytvořené sloupce.
Dotazy mají podobné exekuční plány. Výhodnější je první verze.
(SELECT COALESCE(DEREF(N.ob_nazev).nazev, DEREF(h.ob_nazev).nazev), cena, datum, DEREF(N.je_id_jezdce).id_jezdce, DEREF(N.ko_komponent).model, DEREF(N.ko_komponent).vyrobce, DEREF(N.ko_komponent).rok_vyroby, H.sortiment, h.ceny, h.privetivost, h.slovni_hodnoceni, DEREF(h.je_id_jezdce).id_jezdce FROM nakup_obj N LEFT OUTER JOIN hodnoceni_obj H ON(N.ob_nazev=h.ob_nazev)) UNION (SELECT COALESCE(DEREF(N.ob_nazev).nazev, DEREF(h.ob_nazev).nazev), cena, datum, DEREF(N.je_id_jezdce).id_jezdce, DEREF(N.ko_komponent).model, DEREF(N.ko_komponent).vyrobce, DEREF(N.ko_komponent).rok_vyroby, H.sortiment, h.ceny, h.privetivost, h.slovni_hodnoceni, DEREF(h.je_id_jezdce).id_jezdce FROM nakup_obj N RIGHT OUTER JOIN hodnoceni_obj H ON(N.ob_nazev=h.ob_nazev));
Plan hash value: 914798819 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1206 | 229K| 34 (59)| 00:00:01 | | 1 | SORT UNIQUE | | 1206 | 229K| 34 (59)| 00:00:01 | | 2 | UNION-ALL | | | | | | |* 3 | HASH JOIN RIGHT OUTER | | 603 | 114K| 16 (13)| 00:00:01 | | 4 | TABLE ACCESS FULL | JEZDEC_OBJ | 10 | 120 | 2 (0)| 00:00:01 | |* 5 | HASH JOIN RIGHT OUTER | | 241 | 44103 | 14 (15)| 00:00:01 | | 6 | TABLE ACCESS FULL | OBCHOD_OBJ | 5 | 100 | 2 (0)| 00:00:01 | |* 7 | HASH JOIN RIGHT OUTER | | 193 | 31459 | 11 (10)| 00:00:01 | | 8 | TABLE ACCESS FULL | HODNOCENI_OBJ | 7 | 294 | 2 (0)| 00:00:01 | |* 9 | HASH JOIN OUTER | | 110 | 13310 | 9 (12)| 00:00:01 | | 10 | NESTED LOOPS OUTER | | 33 | 3597 | 6 (0)| 00:00:01 | | 11 | NESTED LOOPS OUTER | | 12 | 936 | 4 (0)| 00:00:01 | | 12 | TABLE ACCESS FULL | NAKUP_OBJ | 7 | 406 | 2 (0)| 00:00:01 | | 13 | TABLE ACCESS BY INDEX ROWID| OBCHOD_OBJ | 2 | 40 | 1 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | SYS_C001505094 | 1 | | 0 (0)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID | KOMPONENT_OBJ | 3 | 93 | 1 (0)| 00:00:01 | |* 16 | INDEX UNIQUE SCAN | SYS_C001505105 | 1 | | 0 (0)| 00:00:01 | | 17 | TABLE ACCESS FULL | JEZDEC_OBJ | 10 | 120 | 2 (0)| 00:00:01 | |* 18 | HASH JOIN RIGHT OUTER | | 603 | 114K| 16 (13)| 00:00:01 | | 19 | TABLE ACCESS FULL | JEZDEC_OBJ | 10 | 120 | 2 (0)| 00:00:01 | |* 20 | HASH JOIN RIGHT OUTER | | 181 | 33123 | 14 (15)| 00:00:01 | | 21 | TABLE ACCESS FULL | KOMPONENT_OBJ | 17 | 527 | 2 (0)| 00:00:01 | |* 22 | HASH JOIN OUTER | | 64 | 9728 | 11 (10)| 00:00:01 | |* 23 | HASH JOIN OUTER | | 38 | 5016 | 9 (12)| 00:00:01 | | 24 | NESTED LOOPS OUTER | | 22 | 1628 | 6 (0)| 00:00:01 | | 25 | NESTED LOOPS OUTER | | 9 | 558 | 4 (0)| 00:00:01 | | 26 | TABLE ACCESS FULL | HODNOCENI_OBJ | 7 | 294 | 2 (0)| 00:00:01 | | 27 | TABLE ACCESS BY INDEX ROWID| OBCHOD_OBJ | 1 | 20 | 1 (0)| 00:00:01 | |* 28 | INDEX UNIQUE SCAN | SYS_C001505094 | 1 | | 0 (0)| 00:00:01 | | 29 | TABLE ACCESS BY INDEX ROWID | JEZDEC_OBJ | 3 | 36 | 1 (0)| 00:00:01 | |* 30 | INDEX UNIQUE SCAN | SYS_C001505138 | 1 | | 0 (0)| 00:00:01 | | 31 | TABLE ACCESS FULL | NAKUP_OBJ | 7 | 406 | 2 (0)| 00:00:01 | | 32 | TABLE ACCESS FULL | OBCHOD_OBJ | 5 | 100 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("P000018$"."SYS_NC_OID$"(+)="H"."SYS_NC00010$") 5 - access("P000012$"."SYS_NC_OID$"(+)="H"."SYS_NC00009$") 7 - access("N"."SYS_NC00008$"="H"."SYS_NC00009$"(+)) 9 - access("P000014$"."SYS_NC_OID$"(+)="N"."SYS_NC00009$") 14 - access("P000010$"."SYS_NC_OID$"(+)="N"."SYS_NC00008$") 16 - access("P000016$"."SYS_NC_OID$"(+)="N"."SYS_NC00010$") 18 - access("P000024$"."SYS_NC_OID$"(+)="N"."SYS_NC00009$") 20 - access("P000026$"."SYS_NC_OID$"(+)="N"."SYS_NC00010$") 22 - access("P000020$"."SYS_NC_OID$"(+)="N"."SYS_NC00008$") 23 - access("N"."SYS_NC00008$"(+)="H"."SYS_NC00009$") 28 - access("P000022$"."SYS_NC_OID$"(+)="H"."SYS_NC00009$") 30 - access("P000028$"."SYS_NC_OID$"(+)="H"."SYS_NC00010$") Statistics ----------------------------------------------------------- 0 recursive calls 0 db block gets 86 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 0 parse count (hard) 0 parse count (failures)
NAZEV CENA DATUM ID_JEZDCE MODEL VYROBCE ROK_VYROBY SORTIMENT CENY PRIVETIVOST SLOVNI_HODNOCENI ID_JEZDCE -------------------------------------------------- ---------------------- ------------------------- ---------------------- -------------------------------------------------- -------------------------------------------------- ---------- ---------------------- ---------------------- ---------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- 901 Bikes 600 29.03.04 1 Minion DH Maxxis 2004 1 3 2 1 901 Bikes 6000 03.02.04 2 Blackhorse Blackhorse 2003 1 3 2 1 Dookie 450 29.03.04 1 Minion DH Maxxis 2004 2 1 1 4 Dookie 450 29.03.04 1 Minion DH Maxxis 2004 2 2 1 Pohodovej majitel. 1 Dookie 450 29.03.04 1 Minion DH Maxxis 2004 2 2 1 6 Dookie 4000 10.09.03 2 Dragstar LeaderFox 2003 2 1 1 4 Dookie 4000 10.09.03 2 Dragstar LeaderFox 2003 2 2 1 Pohodovej majitel. 1 Dookie 4000 10.09.03 2 Dragstar LeaderFox 2003 2 2 1 6 Dookie 10099 29.03.04 1 DirtJumper III Marzocchi 2004 2 1 1 4 Dookie 10099 29.03.04 1 DirtJumper III Marzocchi 2004 2 2 1 Pohodovej majitel. 1 Dookie 10099 29.03.04 1 DirtJumper III Marzocchi 2004 2 2 1 6 Katmar 3 2 4 1 Katmar 3 3 4 3 Madla Sport 400 10.09.05 3 High Roller Maxxis 2004 4 1 3 3 Madla Sport 18000 10.09.05 3 P2 Specialized 2005 4 1 3 3 15 rows selected
Odpovídá očekávané odezvě. Nepodařilo se najít objektové řešení, které by se obešlo bez použití spojení. Je to z toho důvodu, že z povahy dotazu je jen malá provázanost mezi řádky z jedné a druhé tabulky. Zajímavé je, že bylo nutné použít sjednocení dvou vnějších polospojení. Pokud se zadal příkaz používající FULL OUTER JOIN došlo k chybě a byl okamžitě uzavřen socket spojení k databázi - tedy s největší pravděpodobností byl ukončen proces provádějící daný příkaz.
Nejvýhodnější je verze 1. Objektový přístup je v tomto případě zcela nevhodný. Je nutné totiž načíst i objekty z dalších tabulek. Při neobjektovém přístupu stačilo vypsat hodnoty cizích klíčů.
Názvy obchodů, kde byla provedena koupě i byl hodnocen.
SELECT ob_nazev nazev FROM nakup INTERSECT SELECT ob_nazev nazev FROM hodnoceni;
Plan hash value: 620573327 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 7 | 119 | 4 (75)| 00:00:01 | | 1 | INTERSECTION | | | | | | | 2 | SORT UNIQUE | | 7 | 63 | 2 (50)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_NAKUP | 7 | 63 | 1 (0)| 00:00:01 | | 4 | SORT UNIQUE NOSORT| | 7 | 56 | 2 (50)| 00:00:01 | | 5 | INDEX FULL SCAN | PK_HODNOCENI | 7 | 56 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Statistics ----------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 0 parse count (hard) 0 parse count (failures)
NAZEV -------------------------------------------------- 901 Bikes Dookie Madla Sport 3 rows selected
Odezva je správná. Je zřejmé, že se používá řazení.
Místo množinového průniku použijeme spojení a porovnáme.
SELECT DISTINCT ob_nazev nazev FROM hodnoceni JOIN nakup USING(ob_nazev);
Plan hash value: 4006305037 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 51 | 4 (50)| 00:00:01 | | 1 | HASH UNIQUE | | 3 | 51 | 4 (50)| 00:00:01 | |* 2 | HASH JOIN | | 12 | 204 | 3 (34)| 00:00:01 | | 3 | INDEX FULL SCAN| PK_HODNOCENI | 7 | 56 | 1 (0)| 00:00:01 | | 4 | INDEX FULL SCAN| PK_NAKUP | 7 | 63 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("HODNOCENI"."OB_NAZEV"="NAKUP"."OB_NAZEV") Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
NAZEV -------------------------------------------------- Madla Sport Dookie 901 Bikes 3 rows selected
Odezva je správná. Operace hash unique se používá z důvodu uvedení klíčového slova DISTINCT.
Výhodnější je první přístup. Seřadit jednotlivé mezivýsledky a poté provést průnik vychází lépe než provést join a následně z něj odstraňovat duplicity.
SELECT DISTINCT DEREF(N.ob_nazev).nazev AS nazev FROM nakup_obj N WHERE N.ob_nazev IN (SELECT ob_nazev FROM hodnoceni_obj);
Plan hash value: 3286966436 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 415 | 8 (25)| 00:00:01 | | 1 | HASH UNIQUE | | 5 | 415 | 8 (25)| 00:00:01 | |* 2 | HASH JOIN | | 20 | 1660 | 7 (15)| 00:00:01 | | 3 | NESTED LOOPS OUTER | | 12 | 804 | 4 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | NAKUP_OBJ | 7 | 112 | 2 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| OBCHOD_OBJ | 2 | 102 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | SYS_C001505094 | 1 | | 0 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | HODNOCENI_OBJ | 7 | 112 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("N"."SYS_NC00008$"="HODNOCENI_OBJ"."SYS_NC00009$") 6 - access("P000004$"."SYS_NC_OID$"(+)="N"."SYS_NC00008$") Statistics ----------------------------------------------------------- 36 recursive calls 0 db block gets 50 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
NAZEV -------------------------------------------------- Madla Sport Dookie 901 Bikes 3 rows selected
Odpovídá očekávané odezvě.
Nejvýhodnější je první verze. Nejhorší je objektová verze. Mimo jiné vyžaduje další čtení z tabulky Obchod.
Kdyby se každý jezdec účastnil každého závodu, kolik by bylo takových účastí.
SELECT COUNT(*) pocet FROM jezdec CROSS JOIN zavod;
Plan hash value: 3490665904 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | MERGE JOIN CARTESIAN | | 50 | 4 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_ZAVOD | 5 | 1 (0)| 00:00:01 | | 4 | BUFFER SORT | | 10 | 3 (0)| 00:00:01 | | 5 | INDEX FAST FULL SCAN| PK_JEZDEC | 10 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
POCET ---------------------- 50 1 rows selected
Odpovídá očekávané odezvě.
Zkusíme výsledek vypočítat jako násobení počtu hodnot v jednotlivých tabulkách.
SELECT (SELECT COUNT(*) FROM jezdec) * (SELECT COUNT(*) FROM zavod) pocet FROM dual;
Plan hash value: 1302054247 ------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FULL SCAN | PK_JEZDEC | 10 | 1 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | | | | 4 | INDEX FULL SCAN| PK_ZAVOD | 5 | 1 (0)| 00:00:01 | | 5 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------ Statistics ----------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 0 parse count (hard) 0 parse count (failures)
POCET ---------------------- 50 1 rows selected
Odpovídá očekávané odezvě.
Druhá verze je efektivnější. Není totiž třeba provádět náročný cross join, pokud nás nezajímají přímo data z něj, nýbrž jen jejich agregace.
SELECT (SELECT COUNT(*) FROM jezdec_obj) * (SELECT COUNT(*) FROM zavod_obj) pocet FROM dual;
Plan hash value: 2760921415 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FULL SCAN | SYS_C001505139 | 10 | 1 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | | | | 4 | INDEX FULL SCAN| SYS_C001505129 | 5 | 1 (0)| 00:00:01 | | 5 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
POCET ---------------------- 50 1 rows selected
Odpovídá očekávané odezvě.
Verze 2 a 3 jsou rovnocennné. Objektovost nemá na tento dotaz vliv, nevyužívají se žádné reference.
Seznam jezdců (jméno, příjmení) s počtem účastí na závodech. Zobrazit pouze jezdce, kteří se účastnili více jak jednoho závodu.
SELECT jmeno, prijmeni, pocet FROM ( SELECT role10_id_jezdce id_jezdce, COUNT(*) pocet FROM se_ucastnil GROUP BY role10_id_jezdce HAVING COUNT(*)>1 ) NATURAL JOIN jezdec;
Plan hash value: 122544899 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 40 | 3 (34)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 40 | 3 (34)| 00:00:01 | | 2 | VIEW | | 1 | 26 | 2 (50)| 00:00:01 | |* 3 | FILTER | | | | | | | 4 | HASH GROUP BY | | 1 | 2 | 2 (50)| 00:00:01 | | 5 | INDEX FULL SCAN | UNQ_SE_UCASTNIL_1 | 10 | 20 | 1 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID| JEZDEC | 1 | 14 | 1 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | PK_JEZDEC | 1 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(COUNT(*)>1) 7 - access("from$_subquery$_001"."ID_JEZDCE"="JEZDEC"."ID_JEZDCE") Statistics ----------------------------------------------------------- 24 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
JMENO PRIJMENI POCET ------------------------------ ------------------------------ ---------------------- Marek Handlu 2 Michal Andy 5 2 rows selected
Odpovídá očekávané odezvě.
Zkusíme požadavek na minimálně počet závodů přesunout z vnitřku ven, tedy použijeme WHERE místo HAVING.
SELECT jmeno, prijmeni, pocet FROM ( SELECT role10_id_jezdce id_jezdce, COUNT(*) pocet FROM se_ucastnil GROUP BY role10_id_jezdce ) NATURAL JOIN jezdec WHERE pocet > 1;
Plan hash value: 122544899 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 40 | 3 (34)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 40 | 3 (34)| 00:00:01 | | 2 | VIEW | | 1 | 26 | 2 (50)| 00:00:01 | |* 3 | FILTER | | | | | | | 4 | HASH GROUP BY | | 1 | 2 | 2 (50)| 00:00:01 | | 5 | INDEX FULL SCAN | UNQ_SE_UCASTNIL_1 | 10 | 20 | 1 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID| JEZDEC | 1 | 14 | 1 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | PK_JEZDEC | 1 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(COUNT(*)>1) 7 - access("from$_subquery$_001"."ID_JEZDCE"="JEZDEC"."ID_JEZDCE") Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
JMENO PRIJMENI POCET ------------------------------ ------------------------------ ---------------------- Marek Handlu 2 Michal Andy 5 2 rows selected
Výsledek je stejný.
Oba dotazy mají shodné exekuční plány. Přesun podmínky se neprojevil.
SELECT S.role10_id_jezdce.jmeno AS jmeno, S.role10_id_jezdce.prijmeni AS prijmeni, COUNT(*) as pocet FROM se_ucastnil_obj S GROUP BY s.role10_id_jezdce.jmeno, s.role10_id_jezdce.prijmeni HAVING COUNT(*) > 1; -- alias pocet tady nefunguje
Plan hash value: 1599958322 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 5 (20)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | HASH GROUP BY | | 1 | 38 | 5 (20)| 00:00:01 | | 3 | NESTED LOOPS OUTER | | 20 | 760 | 4 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | SE_UCASTNIL_OBJ | 10 | 160 | 2 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| JEZDEC_OBJ | 2 | 44 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | SYS_C001505138 | 1 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(COUNT(*)>1) 6 - access("P000003$"."SYS_NC_OID$"(+)="S"."ROLE10_ID_JEZDCE") Statistics ----------------------------------------------------------- 0 recursive calls 0 db block gets 17 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 0 parse count (hard) 0 parse count (failures) Statistics ----------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 0 parse count (hard) 0 parse count (failures)
JMENO PRIJMENI POCET ------------------------------ ------------------------------ ---------------------- Michal Andy 5 Marek Handlu 2 2 rows selected
Odpovídá očekávané odezvě. Zajímavé je, že bylo nutné použít tečkové notace místo funkce DEREF. Při použití DEREF docházelo k vrácení nesprávných údajů - součty správně ale jména špatně.
Neobjektové formy dotazů jsou efektivnější, ale objektový dotaz je srozumitelnější.
--DOTAZ UPRAVEN OPROTI PŘEDCHOZÍ VERZI--
Jména jezdců, kteří mají všechny disciplíny uvedené jako oblíbené. Pro potřeby SQL je nutné převézt obecný kvantifikátor na existenční. Tedy např: Jména jezdců, pro které neexistuje disciplína, kterou by neměli rádi. Nebo ještě složitějí díky vícenásobné negaci v českých větách: Jména jezdců, pro něž žádná disciplína v systému není neoblíbená.
SELECT (J.jmeno || ' ' || J.prijmeni) AS cele_jmeno FROM jezdec J WHERE NOT EXISTS (SELECT nazev FROM disciplina D MINUS SELECT M.role6_nazev AS nazev FROM ma_rad M WHERE M.role3_id_jezdce = J.id_jezdce);
Plan hash value: 110028514 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 126 | 22 (46)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | JEZDEC | 10 | 140 | 2 (0)| 00:00:01 | | 3 | MINUS | | | | | | | 4 | SORT UNIQUE NOSORT| | 10 | 50 | 2 (50)| 00:00:01 | | 5 | INDEX FULL SCAN | PK_DISCIPLINA | 10 | 50 | 1 (0)| 00:00:01 | | 6 | SORT UNIQUE NOSORT| | 2 | 12 | 2 (50)| 00:00:01 | |* 7 | INDEX FULL SCAN | UNQ_MA_RAD_1 | 2 | 12 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS ( (SELECT /*+ */ "NAZEV" FROM "DISCIPLINA" "D")MINUS (SELECT /*+ */ "M"."ROLE6_NAZEV" FROM "MA_RAD" "M" WHERE "M"."ROLE3_ID_JEZDCE"=:B1))) 7 - access("M"."ROLE3_ID_JEZDCE"=:B1) filter("M"."ROLE3_ID_JEZDCE"=:B1) Statistics ----------------------------------------------------------- 0 recursive calls 0 db block gets 25 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 0 parse count (hard) 0 parse count (failures)
CELE_JMENO ------------------------------------------------------------- Jan Schusti 1 rows selected
Odpovídá očekávané odezvě.
Místo operátoru NOT EXIST zkusíme použít agregační funkce k určení počtu disciplín, které má jezdec rád a to porovnáme s celkovým počtem disciplín v systému.
SELECT (J.jmeno || ' ' || J.prijmeni) AS cele_jmeno FROM jezdec J WHERE (SELECT COUNT(DISTINCT(M.role6_nazev)) FROM ma_rad M WHERE M.role3_id_jezdce=J.id_jezdce) = (SELECT COUNT(*) FROM disciplina);
Plan hash value: 1749947326 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 140 | 3 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | JEZDEC | 10 | 140 | 2 (0)| 00:00:01 | | 3 | SORT GROUP BY | | 1 | 6 | | | |* 4 | INDEX FULL SCAN | UNQ_MA_RAD_1 | 2 | 12 | 1 (0)| 00:00:01 | | 5 | SORT AGGREGATE | | 1 | | | | | 6 | INDEX FULL SCAN| PK_DISCIPLINA | 10 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( (SELECT COUNT(DISTINCT "M"."ROLE6_NAZEV") FROM "MA_RAD" "M" WHERE "M"."ROLE3_ID_JEZDCE"=:B1)= (SELECT COUNT(*) FROM "DISCIPLINA" "DISCIPLINA")) 4 - access("M"."ROLE3_ID_JEZDCE"=:B1) filter("M"."ROLE3_ID_JEZDCE"=:B1) Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
CELE_JMENO ------------------------------------------------------------- Jan Schusti 1 rows selected
Odpovídá očekávané odezvě.
Druhá verze je značně efektivnější. V první verzi se ve WHERE části hlavního dotazu provádějí SELECTY, které vracejí data, a s těmi se navíc poté pracuje množinově, což vyžaduje jejich řazení. V druhé verzi se použije agregační funkce součtu, což je rychlejší.
SELECT (J.jmeno || ' ' || J.prijmeni) AS cele_jmeno FROM jezdec_obj J WHERE (SELECT COUNT(DISTINCT(role6_nazev)) FROM ma_rad_obj M WHERE M.role3_id_jezdce=REF(J)) = (SELECT COUNT(*) FROM disciplina_obj);
Plan hash value: 1275115247 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 340 | 4 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | JEZDEC_OBJ | 10 | 340 | 2 (0)| 00:00:01 | | 3 | SORT GROUP BY | | 1 | 32 | | | |* 4 | TABLE ACCESS FULL| MA_RAD_OBJ | 2 | 64 | 2 (0)| 00:00:01 | | 5 | SORT AGGREGATE | | 1 | | | | | 6 | INDEX FULL SCAN| SYS_C001505148 | 10 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( (SELECT COUNT(DISTINCT "ROLE6_NAZEV") FROM "MA_RAD_OBJ" "M" WHERE "M"."SYS_NC00005$"=:B1)= (SELECT COUNT(*) FROM "DISCIPLINA_OBJ" "DISCIPLINA_OBJ")) 4 - filter("M"."SYS_NC00005$"=:B1) Statistics ----------------------------------------------------------- 15 recursive calls 0 db block gets 60 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
CELE_JMENO ------------------------------------------------------------- Jan Schusti 1 rows selected
Odpovídá očekávané odezvě.
Nejvýhodnější je verze 2. Objektová verze je jen o malinko horší.
Názvy a případně www adresy obchodů, kde provedl jezdec s přezdívkou Mareen alespoň jeden nákup.
SELECT nazev, www FROM obchod WHERE EXISTS (SELECT * FROM nakup JOIN jezdec ON(je_id_jezdce=id_jezdce) WHERE prezdivka='Mareen' AND ob_nazev=nazev);
Plan hash value: 675024332 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 84 | 6 (17)| 00:00:01 | |* 1 | HASH JOIN SEMI | | 2 | 84 | 6 (17)| 00:00:01 | | 2 | TABLE ACCESS FULL | OBCHOD | 5 | 75 | 2 (0)| 00:00:01 | | 3 | VIEW | VW_SQ_1 | 2 | 54 | 3 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 2 | 40 | 3 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL| JEZDEC | 1 | 9 | 2 (0)| 00:00:01 | |* 6 | INDEX FULL SCAN | PK_NAKUP | 2 | 22 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OB_NAZEV"="NAZEV") 5 - filter("JEZDEC"."PREZDIVKA"='Mareen') 6 - access("JE_ID_JEZDCE"="ID_JEZDCE") filter("JE_ID_JEZDCE"="ID_JEZDCE") Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 11 consistent gets 4 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
NAZEV WWW -------------------------------------------------- ---------------------------------------------------------------------------------------------------- 901 Bikes Dookie www.dookie.cz 2 rows selected
Odpovídá očekávané odezvě.
Operátor EXISTS nahradíme operátorem IN. Místo joinu použijeme podmínku v klauzuli WHERE.
SELECT nazev, www FROM obchod WHERE nazev IN ( SELECT ob_nazev FROM nakup WHERE je_id_jezdce=(SELECT id_jezdce FROM jezdec WHERE prezdivka='Mareen') );
Plan hash value: 3226301129 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 84 | 6 (17)| 00:00:01 | |* 1 | HASH JOIN SEMI | | 2 | 84 | 6 (17)| 00:00:01 | | 2 | TABLE ACCESS FULL | OBCHOD | 5 | 75 | 2 (0)| 00:00:01 | | 3 | VIEW | VW_NSO_1 | 2 | 54 | 3 (0)| 00:00:01 | |* 4 | INDEX FULL SCAN | PK_NAKUP | 2 | 22 | 1 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL| JEZDEC | 1 | 9 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("NAZEV"="$nso_col_1") 4 - access("JE_ID_JEZDCE"= (SELECT "ID_JEZDCE" FROM "JEZDEC" "JEZDEC" WHERE "PREZDIVKA"='Mareen')) filter("JE_ID_JEZDCE"= (SELECT "ID_JEZDCE" FROM "JEZDEC" "JEZDEC" WHERE "PREZDIVKA"='Mareen')) 5 - filter("PREZDIVKA"='Mareen') Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 11 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
NAZEV WWW -------------------------------------------------- ---------------------------------------------------------------------------------------------------- 901 Bikes Dookie www.dookie.cz 2 rows selected
Odpovídá očekávané odezvě.
Druhá verze je efektivnější. V první verzi se používá nested loops kvůli spojování.
SELECT DISTINCT DEREF(ob_nazev).nazev AS nazev, DEREF(ob_nazev).www AS www FROM nakup_obj N WHERE DEREF(je_id_jezdce).prezdivka = 'Mareen';
Plan hash value: 3882746863 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 592 | 8 (25)| 00:00:01 | | 1 | HASH UNIQUE | | 8 | 592 | 8 (25)| 00:00:01 | |* 2 | FILTER | | | | | | |* 3 | HASH JOIN OUTER | | 39 | 2886 | 7 (15)| 00:00:01 | | 4 | NESTED LOOPS OUTER | | 12 | 684 | 4 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | NAKUP_OBJ | 7 | 224 | 2 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID| OBCHOD_OBJ | 2 | 50 | 1 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | SYS_C001505094 | 1 | | 0 (0)| 00:00:01 | | 8 | TABLE ACCESS FULL | JEZDEC_OBJ | 10 | 170 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(DECODE("P000005$"."SYS_NC_OID$",NULL,SYS_OP_ATG(NULL,4,5,2),"P000005$"."PREZ DIVKA")='Mareen') 3 - access("P000005$"."SYS_NC_OID$"(+)="N"."SYS_NC00009$") 7 - access("P000003$"."SYS_NC_OID$"(+)="N"."SYS_NC00008$") Statistics ----------------------------------------------------------- 22 recursive calls 0 db block gets 64 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
NAZEV WWW -------------------------------------------------- ---------------------------------------------------------------------------------------------------- Dookie www.dookie.cz 901 Bikes 2 rows selected
Odpovídá očekávané odezvě.
Nejvýhodnější je verze 2. Objektová verze je opět méně efektivní, ale její zápis je o něco srozumitelnější.
Jména a příjmení jezdců, kteří se ještě nezúčastnili žádného závodu.
SELECT jmeno, prijmeni FROM jezdec WHERE id_jezdce NOT IN (SELECT role10_id_jezdce FROM se_ucastnil);
Plan hash value: 260798367 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 80 | 4 (25)| 00:00:01 | |* 1 | HASH JOIN ANTI | | 5 | 80 | 4 (25)| 00:00:01 | | 2 | TABLE ACCESS FULL| JEZDEC | 10 | 140 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | UNQ_SE_UCASTNIL_1 | 10 | 20 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("ID_JEZDCE"="ROLE10_ID_JEZDCE") Statistics ----------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 0 parse count (hard) 0 parse count (failures)
JMENO PRIJMENI ------------------------------ ------------------------------ Tomáš Andy Martin Holý Jakub Růža Štěpán Grundl Michal Benest 5 rows selected
Odpovídá očekávané odezvě.
Porovnáme jestli se operátory NOT IN a NOT EXISTS liší.
SELECT jmeno, prijmeni FROM jezdec WHERE NOT EXISTS (SELECT * FROM se_ucastnil WHERE id_jezdce= role10_id_jezdce);
Plan hash value: 260798367 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 80 | 4 (25)| 00:00:01 | |* 1 | HASH JOIN ANTI | | 5 | 80 | 4 (25)| 00:00:01 | | 2 | TABLE ACCESS FULL| JEZDEC | 10 | 140 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | UNQ_SE_UCASTNIL_1 | 10 | 20 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("ID_JEZDCE"="ROLE10_ID_JEZDCE") Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
JMENO PRIJMENI ------------------------------ ------------------------------ Tomáš Andy Martin Holý Jakub Růža Štěpán Grundl Michal Benest 5 rows selected
Výsledek je stejný.
Je zřejmé, že NOT IN a NOT EXISTS mají stejný exekuční plán.
SELECT J.jmeno, J.prijmeni FROM jezdec_obj J WHERE REF(J) NOT IN (SELECT role10_id_jezdce FROM se_ucastnil_obj);
Plan hash value: 2351109479 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 34 | 4 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| JEZDEC_OBJ | 10 | 340 | 2 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| SE_UCASTNIL_OBJ | 2 | 32 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "SE_UCASTNIL_OBJ" "SE_UCASTNIL_OBJ" WHERE LNNVL("SE_UCASTNIL_OBJ"."SYS_NC00006$"<>:B1))) 3 - filter(LNNVL("SE_UCASTNIL_OBJ"."SYS_NC00006$"<>:B1)) Statistics ----------------------------------------------------------- 15 recursive calls 0 db block gets 60 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
JMENO PRIJMENI ------------------------------ ------------------------------ Tomáš Andy Jakub Růža Štěpán Grundl Michal Benest Martin Holý 5 rows selected
Odpovídá očekávané odezvě.
Všechny verze dotazu jsou ekvivalentní, co se efektivity týče.
Jména a příjmení všech registrovaných jezdců.
SELECT jmeno, prijmeni FROM jezdec;
Plan hash value: 4067037959 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 120 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| JEZDEC | 10 | 120 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------- Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
JMENO PRIJMENI ------------------------------ ------------------------------ Marek Handlu Michal Andy Jan Schusti Jakub Anton Tomáš Andy Jakub Růža Štěpán Grundl Pavel Herink Michal Benest Martin Holý 10 rows selected
Odpovídá očekávané odezvě.
Zkusíme přidat řazení podle primárního klíče.
SELECT jmeno, prijmeni FROM jezdec ORDER BY id_jezdce;
Plan hash value: 2482864222 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 140 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| JEZDEC | 10 | 140 | 2 (0)| 00:00:01 | | 2 | INDEX FULL SCAN | PK_JEZDEC | 10 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
JMENO PRIJMENI ------------------------------ ------------------------------ Marek Handlu Michal Andy Jan Schusti Jakub Anton Tomáš Andy Jakub Růža Štěpán Grundl Pavel Herink Michal Benest Martin Holý 10 rows selected
Výsledek je stejný včetně pořadí.
První verze je efektivnější. V druhém případě se navíc provádí řazení, které ale v tomto případě nemá na pořadí vliv.
SELECT jmeno, prijmeni FROM jezdec_obj;
Plan hash value: 1770270476 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 120 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| JEZDEC_OBJ | 10 | 120 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------- Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
JMENO PRIJMENI ------------------------------ ------------------------------ Marek Handlu Michal Andy Jan Schusti Jakub Anton Tomáš Andy Jakub Růža Štěpán Grundl Pavel Herink Michal Benest Martin Holý 10 rows selected
Odpovídá očekávané odezvě.
Objektovost se v tomto dotazu nijak neprojevuje, jelikož se nevyužívají reference. Verze 1 a 3 jsou tedy stejně efektivní.
Jména a příjmení všech jezdců, kteří mají jako svou oblíbenou disciplínu sjezd (DH).
SELECT jmeno, prijmeni FROM jezdec JOIN ma_rad ON(id_jezdce=role3_id_jezdce) WHERE role6_nazev='DH';
Plan hash value: 656974699 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 40 | 2 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 2 | 40 | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| JEZDEC | 10 | 140 | 2 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN| UNQ_MA_RAD_1 | 1 | 6 | 0 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("MA_RAD"."ROLE6_NAZEV"='DH' AND "ROLE3_ID_JEZDCE"="ID_JEZDCE") Statistics ----------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 0 parse count (hard) 0 parse count (failures)
JMENO PRIJMENI ------------------------------ ------------------------------ Michal Andy Jan Schusti Jakub Anton Pavel Herink 4 rows selected
Odpovídá očekávané odezvě.
Zjistíme, jestli se JOIN chová stejně jako EXISTS.
SELECT jmeno, prijmeni FROM jezdec WHERE EXISTS (SELECT * FROM ma_rad WHERE role6_nazev='DH' AND id_jezdce=role3_id_jezdce);
Plan hash value: 379750019 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 60 | 2 (0)| 00:00:01 | | 1 | NESTED LOOPS SEMI | | 3 | 60 | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| JEZDEC | 10 | 140 | 2 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN| UNQ_MA_RAD_1 | 1 | 6 | 0 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ROLE6_NAZEV"='DH' AND "ID_JEZDCE"="ROLE3_ID_JEZDCE") Statistics ----------------------------------------------------------- 1 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
JMENO PRIJMENI ------------------------------ ------------------------------ Michal Andy Jan Schusti Jakub Anton Pavel Herink 4 rows selected
Výsledek je stejný.
Obě verze mají stejné exekuční plány.
SELECT DEREF(role3_id_jezdce).jmeno AS jmeno, DEREF(role3_id_jezdce).prijmeni AS prijmeni FROM ma_rad_obj WHERE DEREF(role6_nazev).nazev = 'DH';
Plan hash value: 2605581212 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 24 | 1656 | 7 (0)| 00:00:01 | | 1 | NESTED LOOPS OUTER | | 24 | 1656 | 7 (0)| 00:00:01 | |* 2 | FILTER | | | | | | | 3 | NESTED LOOPS OUTER | | 24 | 1128 | 4 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | MA_RAD_OBJ | 24 | 768 | 2 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| DISCIPLINA_OBJ | 1 | 15 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | SYS_C001505147 | 1 | | 0 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID | JEZDEC_OBJ | 1 | 22 | 1 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | SYS_C001505138 | 1 | | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(DECODE("P000005$"."SYS_NC_OID$",NULL,SYS_OP_ATG(NULL,2,3,2),"P000005$"."NAZ EV")='DH') 6 - access("P000005$"."SYS_NC_OID$"(+)="MA_RAD_OBJ"."SYS_NC00006$") 8 - access("P000003$"."SYS_NC_OID$"(+)="MA_RAD_OBJ"."SYS_NC00005$") Statistics ----------------------------------------------------------- 15 recursive calls 0 db block gets 74 consistent gets 0 physical reads 0 redo size 0 total read fandles 0 read fandles in use 0 fandle allocation failures 1 parse count (hard) 0 parse count (failures)
JMENO PRIJMENI ------------------------------ ------------------------------ Michal Andy Jan Schusti Jakub Anton Pavel Herink 4 rows selected
Odpovídá očekávané odezvě.
Neobjektové verze jsou efektivnější. Objektový přístup je snadno čitelný, ale méně efektivní při provádění, jelikož se načítají hodnoty z tabulek, na které ukazují reference.
| Kategorie | Pokryta příkazy číslo: | Charakteristika kategorie příkazu |
|---|---|---|
| A | 9 | jednoduché dotazy (SELECT ... FROM ... WHERE) |
| B | 3, 6, 7, 10 | Vyber ty, pro něž platí, že... |
| C | 8 | Vyber ty, pro něž NEplatí, že... |
| D | 6 | Vyber ty, pro něž platí, že všechny... |
| E | 6 | Vyber ty, pro něž platí, že žádné... |
| F | 1, 2, 4, 5, 7, 10 | spojení |
| G | 6, 7, 8, 10 | IN a EXISTS |
| H | 1, 7, 8, 10 | vztažený poddotaz |
| I | 3, 6, 7, 8, 10 | poddotaz ve WHERE |
| J | 5 | poddotaz ve FROM |
| K | 1, 4 | poddotaz v SELECT |
| L | 1, 2 | jednostranné vnější spojení, plné vnější spojení |
| M | 2 | množinové sjednocení |
| N | 6 | množinový rozdíl |
| O | 3 | množinový průnik |
| P | 1, 4, 5, 6 | agregační výpočty |
| Q | 1, 5 | seskupení a agregace |
| S | 5 | všechny přípustné klauzule |
U každého jezdce budeme udržovat částku, kterou utratil za komponenty. Hodnota bude součtem všech nákupů jezdce, které budou v systému. Částka se bude ukládat jako atribut jezdce a její hodnota bude aktualizována pomocí triggeru.
Součástí modulu je i procedura, která přidá sloupec kam se částka ukládá, pokud ještě v tabulce jezdců neexistuje a spočte prvotní hodnoty součtu pro všechny jezdce.
Dále je tu procedura pro jednodušší vložení nového pláště - vkládá automaticky do tabulky komponent i plast.
-- přidám nákup komponentu s nulovou cenou a zkontroluji korektní naplnění nového sloupce -- vymažu předchozí nákup -- přidám nákup komponentu s nenulovou cenou -- upravím nákup zvýšením ceny -- vymažu předchozí nákup
-- přidám nákup komponentu s nulovou cenou a zkontroluji korektní naplnění nového sloupce konstatuji, ze krok testu prokázal předem očekavané chování -- vymažu předchozí nákup konstatuji, ze krok testu prokázal předem očekavané chování -- přidám nákup komponentu s nenulovou cenou konstatuji, ze krok testu prokázal předem očekavané chování -- upravím nákup zvýšením ceny konstatuji, ze krok testu prokázal předem očekavané chování -- vymažu předchozí nákup konstatuji, ze krok testu prokázal předem očekavané chování
Vše funguje tak, jak má. Na začátku se vytvoří nový sloupec celkova_castka v tabulce jezdec a naplní se správnými daty. Poté trigger hlídá operace nad tabulkou s nákupy a udržuje hodnotu celkova_castka aktuální.
Bohužel se nepodařilo vytvořit původně zamýšlené řešení vytváření nového sloupce. Myšlenkou bylo vytvoření session triggeru, který by před každou úpravou nad tabulkou nákup zkontroloval, jestli existuje sloupec celkova_castka. Při jeho neexistenci by ho vytvořil a naplnil daty. Jelikož po DDL příkazech následuje automaticky commit, bylo nutné použít autonomní transakci. Bohužel ani poté to nefungovalo, jelikož docházelo ke kolizi v přístupu k blokovanému prostředku.
Je nutné zajistit, že poloměry plášťů, které jezdec využívá odpovídají poloměrům, pro které jsou určeny vidlice a rámy používané jezdcem. Tedy pokud jezdec používá vidlici pro poloměr 26", pak musí také používat plášť s poloměrem 26". Obdobně pro rám. Naopak platí, že nemůže používat plášť, který nepasuje do žádné používané vidlice a do žádného používaného rámu. Kontrolu je třeba provádět při přidávání, odebírání a upravovaní komponentů.
Modul vyžaduje proceduru pro vkládání nového pláště z předchozího modulu. Pro jednoduchost sem byla zkopírována.
-- jezdec má rám pro 26" a vidlici pro 26", zkusím mu přidat plášť 24" -- jezdec má rám pro 26" a vidlici pro 26", zkusím mu přidat plášť 26" -- jezdec má rám pro 26" a vidlici pro 24", zkusím mu přidat plášť 24" -- jezdec má rám pro 26" a vidlici pro 24", zkusím mu přidat plášť 27" -- jezdec má rám pro 24" a vidlici pro 26", zkusím mu přidat plášť 24"
-- jezdec má rám pro 26" a vidlici pro 26", zkusím mu přidat plášť 24" konstatuji, ze krok testu prokázal předem očekavané chování (zahlášení chyby) -- jezdec má rám pro 26" a vidlici pro 26", zkusím mu přidat plášť 26" konstatuji, ze krok testu prokázal předem očekavané chování (korektní vložení) -- jezdec má rám pro 26" a vidlici pro 24", zkusím mu přidat plášť 24" konstatuji, ze krok testu prokázal předem očekavané chování (korektní vložení) -- jezdec má rám pro 26" a vidlici pro 24", zkusím mu přidat plášť 27" konstatuji, ze krok testu prokázal předem očekavané chování (zahlášení chyby) -- jezdec má rám pro 24" a vidlici pro 26", zkusím mu přidat plášť 24" konstatuji, ze krok testu prokázal předem očekavané chování (korektní vložení)
Trigger funguje tak, jak má. Zajistí, že jezdec používá pouze pláště, které pasují do rámu a vidlic, která sám používá.
Při vkládání informace o účasti na závodu je nutno zajistit, že se jezdec může účastnit pouze závodů v disciplíně, kterou má uvedenu jako oblíbenou.
-- zkusím přidat účast jezdce na závodu v disciplíně, kterou nemá rád -- přídám účast jezdce v disciplíně, kterou má rád
-- zkusím přidat účast jezdce na závodu v disciplíně, kterou nemá rád konstatuji, ze krok testu prokázal předem očekavané chování (zahlášení chyby) -- přídám účast jezdce v disciplíně, kterou má rád konstatuji, ze krok testu prokázal předem očekavané chování (korektní vložení)
Trigger funguje tak, jak má. Dovolí přidat účast na závodu pouze v oblíbených disciplínách jezdců.
Při zaznamenávání nákupu komponentu se kontroluje, zda je rok z datumu nákupu vyšší nebo roven roku uvedení komponentu na trh.
-- zkusím přidat záznam o koupi komponentu z roku 2002 provedené v roce 2001 -- přidám záznam o koupi komponentu z roku 2002 provedené v roce 2002
-- zkusím přidat záznam o koupi komponentu z roku 2002 provedené v roce 2001 konstatuji, ze krok testu prokázal předem očekavané chování (zahlášení chyby) -- přidám záznam o koupi komponentu z roku 2002 provedené v roce 2002 konstatuji, ze krok testu prokázal předem očekavané chování (korektní vložení)
Trigger funguje tak, jak má. Zamezí vložení nesmyslného nákupu výrobku, který ještě nebyl uveden na trh.
Při vkládání nového jezdce nebo při úpravách existujícího se kontroluje email, zda má platný formát emailové adresy. Trigger nejprve odstraní mezery ze začátku a konce vstupních dat a poté provede kontrolu adresy vůči regulárnímu výrazu.
- zkusím přidat jezdce s neplatnou emailovou adresou - přidám jezdce s platnou emailovou adresou
- zkusím přidat jezdce s neplatnou emailovou adresou konstatuji, ze krok testu prokázal předem očekavané chování (zahlášení chyby) -- přidám jezdce s platnou emailovou adresou onstatuji, ze krok testu prokázal předem očekavané chování (korektní vložení)
Trigger funguje tak, jak má. Povolí vložení pouze platné emailové adresy. Ke kontrole se používá regulární výraz pokrývající všechny běžně používané emailové adresy.
Systém zajistí, že hodnoty parametrů poměr cena výkon a hodnocení v seznamu zkušeností a parametrů sortiment, ceny a přívětivost v seznamu hodnocení obchodů jsou celá čísla z rozsahu 1 až 10.
-- zkusím přidat záznam o zkušenosti s nepovolenou hodnotou poměru cena výkon rovno 0 -- zkusím přidat záznam o zkušenosti s nepovolenou hodnotou hodnocení rovno 0 -- zkusím přidat záznam o zkušenosti s nepovolenou hodnotou poměru cena výkon rovno 11 -- zkusím přidat záznam o zkušenosti s nepovolenou hodnotou hodnocení rovno 11 -- zkusím přidat záznam o zkušenosti s hodnotou poměru cena výkon rovno 10 -- zkusím přidat záznam o zkušenosti s hodnotou hodnocení rovno 10 -- zkusím přidat záznam o hodnocení obchodu s nepovolenou hodnotou sortiment rovno 0 -- zkusím přidat záznam o hodnocení obchodu s nepovolenou hodnotou ceny rovno 0 -- zkusím přidat záznam o hodnocení obchodu s nepovolenou hodnotou přívětivost rovno 0 -- zkusím přidat záznam o hodnocení obchodu s nepovolenou hodnotou sortiment rovno 11 -- zkusím přidat záznam o hodnocení obchodu s nepovolenou hodnotou ceny rovno 11 -- zkusím přidat záznam o hodnocení obchodu s nepovolenou hodnotou přívětivost rovno 11 -- zkusím přidat záznam o hodnocení obchodu s hodnotou sortiment rovno 10 -- zkusím přidat záznam o hodnocení obchodu s hodnotou ceny rovno 10 -- zkusím přidat záznam o hodnocení obchodu s hodnotou přívětivost rovno 10
-- zkusím přidat záznam o zkušenosti s nepovolenou hodnotou poměru cena výkon rovno 0 konstatuji, ze krok testu prokázal předem očekavané chování (zahlášení chyby) -- zkusím přidat záznam o zkušenosti s nepovolenou hodnotou hodnocení rovno 0 konstatuji, ze krok testu prokázal předem očekavané chování (zahlášení chyby) -- zkusím přidat záznam o zkušenosti s nepovolenou hodnotou poměru cena výkon rovno 11 konstatuji, ze krok testu prokázal předem očekavané chování (zahlášení chyby) -- zkusím přidat záznam o zkušenosti s nepovolenou hodnotou hodnocení rovno 11 konstatuji, ze krok testu prokázal předem očekavané chování (zahlášení chyby) -- zkusím přidat záznam o zkušenosti s hodnotou poměru cena výkon rovno 10 konstatuji, ze krok testu prokázal předem očekavané chování (korektní vložení) -- zkusím přidat záznam o zkušenosti s hodnotou hodnocení rovno 10 konstatuji, ze krok testu prokázal předem očekavané chování (korektní vložení) -- zkusím přidat záznam o hodnocení obchodu s nepovolenou hodnotou sortiment rovno 0 konstatuji, ze krok testu prokázal předem očekavané chování (zahlášení chyby) -- zkusím přidat záznam o hodnocení obchodu s nepovolenou hodnotou ceny rovno 0 konstatuji, ze krok testu prokázal předem očekavané chování (zahlášení chyby) -- zkusím přidat záznam o hodnocení obchodu s nepovolenou hodnotou přívětivost rovno 0 konstatuji, ze krok testu prokázal předem očekavané chování (zahlášení chyby) -- zkusím přidat záznam o hodnocení obchodu s nepovolenou hodnotou sortiment rovno 11 konstatuji, ze krok testu prokázal předem očekavané chování (zahlášení chyby) -- zkusím přidat záznam o hodnocení obchodu s nepovolenou hodnotou ceny rovno 11 konstatuji, ze krok testu prokázal předem očekavané chování (zahlášení chyby) -- zkusím přidat záznam o hodnocení obchodu s nepovolenou hodnotou přívětivost rovno 11 konstatuji, ze krok testu prokázal předem očekavané chování (zahlášení chyby) -- zkusím přidat záznam o hodnocení obchodu s hodnotou sortiment rovno 10 konstatuji, ze krok testu prokázal předem očekavané chování (korektní vložení) -- zkusím přidat záznam o hodnocení obchodu s hodnotou ceny rovno 10 konstatuji, ze krok testu prokázal předem očekavané chování (korektní vložení) -- zkusím přidat záznam o hodnocení obchodu s hodnotou přívětivost rovno 10 konstatuji, ze krok testu prokázal předem očekavané chování (korektní vložení)
Triggery fungují tak, jak mají. Dovolí vložení pouze hodnot z definovaného rozmezí.
V práci jsem si prakticky ozkoušel použití objektové rozšíření SQL. Tento přístup má výhody v tom, že je možné použít způsob myšlení, který se používá při vývoji v běžných programovacích jazycích jako je C++ a Java. Programátor tedy nemusí "přepínat" mezi objektovým a relačním myšlením. V některých případech je takový zápis jednodušší a čitelnější - například v případech složených cizích klíčů. Na druhou stranu je z výsledků provedených měření patrné, že objektový přístup je méně efektivní.
Další přínosnou částí práce bylo programování v PL/SQL. Procedurální rozšíření umožňují dát databázím mnohem větší možnosti práce s daty. Bohužel programování v Oracle SQL Developeru není moc komfortní. Hlášky při kompilačních chybách bývají indikovány na špatných místech a nejsou moc srozumitelné. Problém spatřuji v tom, že PL/SQL je bohužel nestandardní jazyk vyvinutý společností Oracle.
Na práci se mi nelíbila její časová náročnost. Deset dotazů a 6 modulů je příliš mnoho. Člověk poté stráví mnoho hodin jen mechanickým kopírováním reakcí na dotazy, aniž by mu to přinášelo jakýkoli intelektuální užitek. Podle mých záznamů mi vytvoření této semestrální práce zabralo přibližně 23 hodin.
[1] Stránky předmětu SQL - http://service.felk.cvut.cz/courses/X36SQL
[2] Rozšíření PL/SQL v Oracle 10g http://www.oracle-base.com/articles/10g/PlsqlEnhancements10g.php
[3] Formát emailové adresy http://www.regular-expressions.info/email.html