Rekurzivní SQL

1. Rekurzivní SQL Petr Čermák Michal Danihelka

2. Osnova • Úvod do problematiky • Rekurzivní SQL v Oraclu • Řešení rekurzivních úloh bez podpory rekurzivního SQL • Rekurzivní SQL v DB/2 • Použití DB/2 UDF

3. Úvod do problematiky

4. Co je to rekurzivní dotaz? • Dotaz je rekurzivní, pokud se záznamy odkazují sami na sebe pomocí primárního nebo cizího klíče. • Vazba může být přímá, nebo i přes několik tabulek

5. Použití rekurzivních dotazů • Obecně kdekoliv mají data hierarchickou strukturu • Uspořádání organizace • Struktura výrobku • Rodinná hierarchie

6. Divergentní hierarchie • Každý uzel má nejvýše jednoho otce • Reprezentace tabulkou s poli Klíč, Otec a nějaká hodnota přiřazená objektu • Kořen možno označit například Klíč = Otec

7. Konvergentní hierarchie • Uzel může mít víc předků, nesmí však být svým otcem / dítětem (acyklický graf) • Jedna tabulka nestačí • Potřebujeme dvě – Objekty(PKey, Num) a Vztahy(PKey, CKey, Parametr vztahu)

8. Rekurzivní hierarchie • Graf může obsahovat i cykly • Reprezentace stejná jako v předchozím případu • Riziko zacyklení

9. Další dělení • Vyvážené • Hladiny většinou různých typů • Bývají divergentní • Nevyvážené • Hladiny prvků stejného druhu

10. Podpora rekurze v SQL • ANSI SQL nepodporuje dotazování popsaným způsobem ani výpočet obecně rekurzivních funkcí • podpora je zahrnuta v „chystaném“ ANSI SQL3 (SQL:1999) • náznaky implementace v ORACLE a DB2

11. Vlastnosti rekurzivních dotazů • vše na jeden dotaz • nepřehlednost • výhodné pouze když využiji velkou část výsledkové sady

12. Jak se obejít bez rekurze? • Použití cursorů, cyklů, ... • Ztráta možnosti optimalizace • Kód není tak “elegantní”

13. Požadavky kladené na rekurzi Při procházení grafem můžeme získat: • dosažitelnost • vyčíslitelnost cest • spojování cest • výpočet obecně rekurzivní funkce při průchodu vrcholem nebo hranou

14. Příklad: (vztahy mezi součástmi) P1 2 2 2 P2 P3 P4 3 2 1 3 P5 P6 P7 2 1 P8 P9

15. Dosažitelnost • Z daného vrcholu, nebo množiny vrcholů, chceme zjistit všechny dosažitelné potomky • “Z jakých částí se skládá výrobek P1?”

16. Vyčíslitelnost cest • “Zobraz celou strukturu výrobku P1 se všemi jeho částmi” P1(1) 2 2 2 P2(2) P3(2) P4(2) 3 2 1 3 P5(6) P6(4) P6(2) P7(6) 2 1 2 1 P8(8) P9(4) P8(4) P9(4)

17. Spojování cest • “Jaké jsou části výrobku P1 a kolik jich je třeba k jeho sestavení?” P1(1) 2 2 2 P2(2) P3(2) P4(2) 3 2 1 3 P5(6) P6(6) P7(6) 2 1 P8(12) P9(6)

18. Rekurzivní SQL v Oraclu

19. Co Oracle podporuje? • dosažitelnost • vyčíslitelnost cest • jednu rekurzivní funkci level

20. Příklad: (struktura výrobku) CREATE TABLE Parts (part# CHAR(5) PRIMARY KEY, part_name CHAR(4) NOT NULL, assembly_time INTEGER NOT NULL CHECK (VALUE >= 0), category_id INTEGER NOT NULL, FOREIGN KEY category_id REFERENCES Category); CREATE TABLE Usage (parent_part# CHAR(5) NOT NULL, component_part# CHAR(5) NOT NULL, quantity INTEGER NOT NULL, PRIMARY KEY(parent_part#, component_part#), FOREIGN KEY parent_part# REFERENCES Parts, FOREIGN KEY component_part# REFERENCES Parts);

21. CONNECT BY, START WITH • Klauzule START WITH • definuje počáteční podmínku pro dotaz • CONNECT BY • specifikuje vazbu mezi rodičovskými řádky a potomky (pomocí operátoru PRIOR)

22. Příklad: (zobraz strukturu výrobku – vyčíslitelnost cest) SELECT LEVEL, parent_part#, component_part# FROM Usage CONNECT BY PRIORcomponent_part# = parent_part# START WITH parent_part# = ‘P1’ • Výsledek dotazu není tabulka, výsledek je nutně uspořádaný • Omezení -- nemožnost počítat libovolnou obecně rekurzivní funkci • omezení na používání spojení v rek. dotazech

23. Algoritmus zpracování dotazu 1) Zjisti řádky splňující podmínku v START WITH 2) Najdi všechny potomky vzhledem k řádkům z kroku 2 nebo předchozího kroku 3 splňující podmínku v CONNECT BY 3) Opakuj krok 3 dokud dostáváš nové řádky 4) Eliminuj všechny řádky, které nesplňují podmínku ve WHERE 5) Vrať zbylé řádky.

24. Pořadí vrácených řádků • Řádky jsou vraceny v preorderu tj. nejdříve rodičovský vrchol, pak teprve podstromy.

25. Příklad: (Dosažitelnost) SELECT DISTINCT component_part# FROM (viz. select pro zobrazení struktury)

26. Příklad: (obcházení joinu v connect by – součástky s popisem) SELECT part#, category_name FROM Parts,Category WHERE Parts.category_id = Category.category_id AND part# IN (SELECT component_part# FROM Usage START WITH parent_part# = ‘P1’ CONNECT BY PRIOR componet_part# = parent_part#);

27. Příklad: (obcházení joinu v connect by – dosažitelné dvojice) SELECT DISTINCT PX.part#, PX.part_name#, PY.part#, PY.part_name# FROM Parts PX,Parts PY WHERE PY.part# IN (SELECT component_part# FROM Usage START WITH parent_part# = PX.part# CONNECT BY PRIOR componet_part# = parent_part#) ORDER BY PX.part#, PY.part#;

28. Rozšíření Oracle 9i – podpora join • SELECT employee_name, manager_name, dept_name FROM employee, deptWHERE employee.deptno = dept.deptno START WITH employee_name = 'KING' CONNECT BY PRIOR employee_id = manager_id;

29. Rozšíření Oracle 9i – třídění řádků s daným předkem • SELECT employee_name, manager_name, dept_name FROM employee, dept WHERE employee.deptno = dept.deptno START WITH employee_name = 'KING' CONNECT BY PRIOR employee_id = manager_idORDER SIBLINGS BY hire_date;

30. Rozšíření Oracle 9i - cesty • SELECT employee_name, SYS_CONNECT_BY_PATH(employee_name, '/') "PATH" FROM employee START WITH employee_name = 'KING' CONNECT BY PRIOR employee_id = manager_id;

31. Rozšíření Oracle 10g – společný předek SELECT DISTINCT CONNECT_BY_ROOT assembly_id, CONNECT_BY_ROOT assembly_name FROM bill_of_materials WHERE part_number = 1019 START WITH parent_assembly IS NULL CONNECT BY parent_assembly = PRIOR assembly_id; CONNECT_BY_ROOTASSEMBLY_ID CONNECT_BY_ROOTASSEMBLY 100 Automobile

32. Rozšíření Oracle 10g – jsem list? – část 1 SELECT ASSEMBLY_ID, RPAD(' ', 2*(LEVEL-1)) || assembly_name assembly_name, quantity, CONNECT_BY_ISLEAF FROM bill_of_materials WHERE LEVEL <= 2 START WITH assembly_id = 100 CONNECT BY parent_assembly = PRIOR assembly_id;

33. Rozšíření Oracle 10g – jsem list? – část 2 SELECT ASSEMBLY_ID, RPAD(' ', 2*(LEVEL-1)) || assembly_name assembly_name, quantity, CONNECT_BY_ISLEAF FROM bill_of_materials WHERE LEVEL = 2 START WITH assembly_id = 110 CONNECT BY parent_assembly = PRIOR assembly_id;

34. Rozšíření Oracle 10g – cykly SELECT RPAD(' ', 2*(LEVEL-1)) || assembly_name assembly_name, quantity, CONNECT_BY_ISCYCLE FROM bill_of_materials START WITH assembly_id = 100 CONNECT BY NOCYCLE parent_assembly = PRIOR assembly_id;

35. Řešení rekurzivních úloh bez podpory rekurzivního SQL

36. Formáty pro tabulky jakožto výsledky dotazů – část 1 • Na rozdíl od oracla nesmějí mít tyto tabulky žádné číslování, aby šli používat v poddotazech • Formát dostupnosti (kam se všude dostanu z vrcholu) – stačí binární tabulka obsahující kořen a syna

37. Formáty pro tabulky jakožto výsledky dotazů – část 2 • Formát vyčíslování cest (struktura s počtem podčástí) – použijeme následující tabulku • Formát spojování cest (seznam následníků s počtem) – použijeme následující tabulku

38. Kterak použít ANSI SQL k vyhodnocení rekurzivních dotazů – část 1 • Dostupnost SELECT DISTINCT parent_part#, component_part# FROM PartsPathEnum WHERE parent_part# = ‘P1’ • Vyčíslování cest SELECT * FROM PartsPathEnum WHERE parent_part# = ‘P1’ ORDER BY squence#;

39. Kterak použít ANSI SQL k vyhodnocení rekurzivních dotazů – část 2 • Spojování cest SELECT parent_part#, component_part#, SUM(total_quantity) FROM PartsPathEnum WHERE parent_part# = ‘P1’ GROUP BY parent_part#,component_part#;

40. Kde vzít tabulku vyčíslitelnosti cest? • PL/SQL nebo jakýkoliv jazyk podporující rekurzi a SQL • Doporučeno udělat index na kombinaci sloupců (parent_part#,component_part#), jelikož je tato tabulka typicky hodně veliká • Není aktuální, ale v praxi většinou nevadí

41. Ořezávání dotazu SELECT PE1.* FROM PartsPathEnum PE1, PartsPathEnum PE2 WHERE PE1.parent_part# = ‘P1’ AND PE2.parent_part# = ‘P1’ AND PE2.component_part# = ‘P3’ AND (PE1.seq# NOT BETWEEN PE2.seq# AND PE2.max_subtree_seq#) ORDER BY PE1.seq#;

42. Rekurzivní SQL v DB/2

43. Úvodní příklad • Úkol: Získat seznam potomků (i nepřímých) uzlu A. HIERARCHY A B C D E F G

44. Řešení A B C D E F WITH PARENT (PKEY, CKEY) AS (SELECT PKEY, CKEY FROM HIERARCHY WHERE PKEY = ‘A‘ UNION ALL SELECT C.PKEY, C.CKEY FROM HIERARCHY C, PARENT P WHERE P.CKEY = C.PKEY ) SELECT PKEY, CKEY FROM PARENT; G

45. Řešení problému zacyklení • Týká se rekurzivních hierarchií • Dvě základní metody řešení: • Zastavit procházení po určitém počtu hladin • Udržovat si seznam navštívených uzlů a ignorovat dříve navštívené

46. Příklad 2 A B C D E F • Seznam potomků s hloubkou, v jaké se nalézají. G WITH PARENT (CKEY, LVL) AS (SELECT DISTINCT PKEY, 0 FROM HIERARCHY WHERE PKEY = ‘A‘ UNION ALL SELECT C.CKEY, P.LVL + 1 FROM HIERARCHY C, PARENT P WHERE P.CKEY = C.PKEY ) SELECT PKEY, CKEY FROM PARENT;

47. Příklad 3 A B C D E F • Seznam potomků hloubky max. 2 G WITH PARENT (CKEY, LVL) AS (SELECT DISTINCT PKEY, 0 FROM HIERARCHY WHERE PKEY = ‘A‘ UNION ALL SELECT C.CKEY, P.LVL + 1 FROM HIERARCHY C, PARENT P WHERE P.CKEY = C.PKEY ) SELECT PKEY, CKEY FROM PARENT, WHERE LVL < 3;

48. Příklad 3 podruhé A B C D E F • Seznam potomků hloubky max. 2 G WITH PARENT (CKEY, LVL) AS (SELECT DISTINCT PKEY, 0 FROM HIERARCHY WHERE PKEY = ‘A‘ UNION ALL SELECT C.CKEY, P.LVL + 1 FROM HIERARCHY C, PARENT P WHERE P.CKEY = C.PKEY AND P.LVL + 1 < 3 ) SELECT PKEY, CKEY FROM PARENT;

49. Příklad 4 A B C D E F • Výpis cesty, která má délku 4 G WITH TEMP1 (CKEY, LVL) AS (SELECT DISTINCT PKEY, 1 FROM HIERARCHY WHERE PKEY = ‘A‘ UNION ALL SELECT C.CKEY, P.LVL + 1 FROM HIERARCHY C, TEMP1 P WHERE P.CKEY = C.PKEY AND P.LVL < 4 ), TEMP2 (CKEY, LVL) AS (SELECT CKEY, LVL FROM TEMP1, WHERE LVL = 4 UNION ALL SELECT C.PKEY, D.LVL – 1 FROM HIERARCHY C, TEMP2 D WHERE D.CKEY = C.CKEY ) SELECT * FROM TEMP2;

50. Příklad 5 • Všichni potomci uzlu A do 4. hladiny HIERARCHY A B C D E F G