1 / 65

Stromy a hierarchické struktury v SQL

Stromy a hierarchické struktury v SQL. 30.11.2005 Mária Szabó, Jirka Zouhar, Gergely Jakab. M ária Szabó 30 .11.2005. Using the Node Type to Solve Problems with Hierarchies in DB2 Universal Database. Node Type & Hierarchies in DB2. Z ákladní p ojmy Úvod do problematiky Koncept řešení

gunnar
Télécharger la présentation

Stromy a hierarchické struktury v SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Stromy a hierarchické struktury v SQL 30.11.2005 Mária Szabó, Jirka Zouhar, Gergely Jakab

  2. Mária Szabó 30.11.2005 Using the Node Type to Solve Problems with Hierarchies in DB2 Universal Database

  3. Node Type & Hierarchies in DB2 • Základní pojmy • Úvod do problematiky • Koncept řešení • Node type • Výhody • Příklady

  4. Node Type & Hierarchies in DB2 • Hiearchie • Relační Db • Node Type Pojmy

  5. Node Type & Hierarchies in DB2 • Před 30 lety – hierarchical db • Svázánost se db strukturou • Ukazatel – pointer • Nákladné chyby • Vícenásobné použití pro stejná data Problematika

  6. Node Type & Hierarchies in DB2 • Průměr účetní bilance pro každý region i odvětví • 10 největších klientů • Definovat více hierarchií • Synchronizace dat

  7. Node Type & Hierarchies in DB2 • Pre-processing dat • Myšlenka typu UZEL • Série čísel • Hladina – Level • Indexování jako v SQL Uzel & Hierarchie

  8. Node Type & Hierarchies in DB2 CREATE TABLE employee ( Employee_Id Integer, Manager_Id Integer, Last_Name varchar(30) ... PRIMARY KEY(Employee_Id), FOREIGN KEY (Manager_Id) REFERENCES employee(Employee_Id); Kolik zaměstnanců pracuje pro jistého managera? SELECT COUNT (*) FROM employee emp, employee manager WHERE emp.Manager_Id = manager.Employee_Id AND manager.Last_Name = ‘Roy’; Pro každého zaměstnance zjistit počet podřízených !?!

  9. Node Type & Hierarchies in DB2 • Vícenásobné levely v managmentu • SQL příkaz pro každý manager pro každý level • Víckrát vnořovat Problém CREATE TABLE employee2 ( Employee_Id Node, Last_Name varchar(30), ... PRIMARY KEY(Employee_Id);

  10. Node Type & Hierarchies in DB2 1| |Joe| 2|1|Joe2| . . . 8|2|Joe8| . . . 14|3|Joe14| NULL na místě managera  šéf ---------------------------- 1.0|Joe| 1.2|Joe2| . . . 1.2.8|Joe8| . . . 1.3.14|Joe14|

  11. Node Type & Hierarchies in DB2 • Kolik zaměstnanců pracuje pod specifikovaným managerom? • Přímé potomky • Rekurzie dokud jsme neprošli všechny lidi • Rekurzivní dotaz nebo procedura Pro EMPLOYEE table: WITH RPL (employee_Id, Manager_Id, Last_Name) AS (SELECT ROOT.employee_Id, ROOT.Manager_Id, ROOT.Last_Name FROM employee ROOT WHERE ROOT.employee_Id = 1 UNION ALL SELECT CHILD.EMployee_Id, CHILD.Manager_Id, CHILD.Last_Name FORM RPL PARENT, employee CHILD WHERE PARENT.Employee_Id = CHILD.Manager_Id ) SELECT COUNT(*) -1 FROM RPL;

  12. Node Type & Hierarchies in DB2 • Kolik zaměstnanců pracuje pod špecifikovaným mangerom? • uzly, které padají pod spec. managerom Pro EMPLOYEE2 table: 1.3 > 1.2.8 > 1.2 SELECT COUNT(*) FROM Employee2 WHERE Employee_ID > NodeInput(‘1.2’) AND EMPLOYEE_ID < NodeInput(‘1.3’);

  13. Node Type & Hierarchies in DB2 Test • Každý manager 6 přímých potomků • Lidi úplně na spodku nejsou managery • pro 9331 řádků 37 násobné zrychlení • tradiční přístup exponenciální char. • uzlový přístup lineární charakteristika • rozdíl se zvětšuje #levelu

  14. Node Type & Hierarchies in DB2 Implementace typu UZEL - NODE CREATE DISTINCT TYPE Node AS varchar(64) FOR BIT DATA WITH COMPARISONS;

  15. Node Type & Hierarchies in DB2 Indexování a SQL dotazy • indexování typu uzel • umístnit do WHERE klauzuli • možnost i bez indexování  scan table SELECT * FROM employee2 WHERE isDescendant(employee_Id, NodeInput(‘1.7.43’)) ORDER BY employee_Id Desc; Příklad vrátí všechny potomky uzlu 1.7.43

  16. Node Type & Hierarchies in DB2 Příklad vrátí všechny potomky uzlu 1.7.43 SELECT * FROM employee2 WHERE employee_Id > NodeInput(‘1.7.43’) AND employee_Id < NodeInput(‘1.7.44’) ORDER BY employee_Id Desc; Příklad vrátí pouze přímé potomky uzlu 1.7.43 SELECT * FROM employee2 WHERE employee_Id > NodeInput(‘1.7.43’) AND employee_Id < NodeInput(‘1.7.44’) AND Length(employee_id) = 4 ORDER BY employee_Id Desc;

  17. Node Type & Hierarchies in DB2 !Občas je zapotřeby přidat funkce pro zachování použitelnosti indexu! SELECT * FROM employee2 WHERE isAncestor(employee_Id, NodeInput(‘1.7.43.256.1537’)) ORDER BY employee_Id Desc; 1.7.43.256 , 1.7.43, 1.7, 1.0 SELECT * FROM Employee2 WHERE Employee_Id IN ( SELECT x.col1 FROM TABLE(Ancestors(NodeInput(‘1.7.43.256.1537’))) AS x ) ORDER BY employee_Id Desc;

  18. Node Type & Hierarchies in DB2 Hlavní výnos • Obrovské tabulky  rýchlá odezva • Rozumné dotazy  nezávislost na# požadavků

  19. Děkuji Mária Szabó 30.11.2005

  20. Detaily hierarchii v DB2 30.11.2005 Jirka Zouhar

  21. Použití • Ve složitějších případech je třeba oddělit hierarchii od dat • Lze použít i více hierarchií • Příklady: • Skladová evidence zásob • Hierarchické systémy zásad • Klasifikace objektů

  22. Hierarchie zásad • CREATE TABLE Policies ( PolicyNumber Node, Description varchar(50) ); • CREATE TABLE Objects ( Name varchar(50), PolicyNumber Node ); • CREATE TABLE Safe_places ( Location Node, PolicyNumber Node, Description varchar(75), Administrator varchar(30) );

  23. Hierarchie zásad • Jaké objekty mají „malé“ zabezpečení? • Jak zabezpečený je daný objekt? • Mají všechny zóny korektní zabezpečení? • Nejsou objekty v špatných zónách? • SELECT * FROM Objects, Places WHERE Places.policyNumber NOT IN Ancestors(Objects.policyNumber)

  24. Skladová evidence • CREATE TABLE Parts ( PartNumber integer, Name varchar(30), Price money ); • CREATE TABLE Components ( ComponentId Node, Sequence integer, Quantity integer, Name varchar(30), PartNumber integer );

  25. Klasifikace Objektů • CREATE TABLE materials ( • Name varchar(30), • Type Node ); • CREATE TABLE parts ( • Name varchar(30), • Type node, • ComponentId node, • Price integer, • Provider varchar(30), • PartNumber ingeter, • Description integer );

  26. Klasifikace objektů 1 Materiál 1.1 dřevo 1.2 železo 1.3 kůže 1.1.1 mahagon

  27. Klasifikace objektů • Najdi všechny součástky z mahagonu s cenou menší padesáti. • SELECT name,desc,price FROM parts WHERE isDescendant(type, NodeInput( SELECT type FROM materials WHERE name='mahagon')) AND price < 50;

  28. Funkce pro práci s Node Type • Node nodeInput (varchar) Převede varchar na Node Type. • Varchar nodeOutput (node) Převede Node Type na Varchar V dalším bude pro zjednodušení používán místo nodeInput ('1.3.5') zjednodušený zápis 1.3.5

  29. Funkce pro práci s Node Type • Node[] Ancestors (node) Vrátí seznam předků: Ancestors(1.25.3) = {1.25,1} • Integer GetMember (Node, Integer) Vrátí číslo větve na dané úrovni: GetMember(1.3.5.4,2) = 3 • Node getParent (node) Vrátí otce zadaného: getParent(1.5.4) = 1.5

  30. Funkce pro práci s Node Type • Node Increment (node) Vrátí další node v řadě: Incerment (1.2.22) = 1.2.23 • Node Increment (node, integer) Vrátí další node v řadě na dané úrovni: Increment (1.2.22, 2) = 1.3 • Node newLevel (node) Vrátí nový node o úroveň níž: newLevel (1.2) = 1.2.1

  31. Funkce pro práci s Node Type • Integer isAncestor (node1, node2) Testuje, zda je node2 v podstromu node1: isAncestor (1.2, 1.2.3.55) = 1 isAncestor (1.3, 1.2.4) = 0 • Integer isDescendant(node,node) Opak isAncestor: isDescendant (node1, node2) = isAncestor (node2, node1)

  32. Funkce pro práci s Node Type • Integer isChild (node1, node2) Testuje, zda je node2 synem node1: isChild (1.2, 1.2.3) = 1 isChild (1.2, 1.2.3.5) = 0 • Integer isParent (node, node) Opak isChild • Integer length(node) Zjistí úroveň node ve stromu: length (1.2.3.5) = 4

  33. Funkce pro práci s Node Type • Node graft(node,node,node) Přesune větev danou třetím parametrem jako podstrom druhého parametru, první je otec přemísťovaného stromu. UPDATE Parts SET Part_num = Graft( 1.2, 1.5.6, Part_num) WHERE Part_num > 1.2.18 AND Part_num < 1.2.19

  34. Funkce pro práci s Node Type • Graft (1.1.3, 1.4, 1.1.3.2) = 1.4.2 1 1 1.1 1.4 1.1 1.4 1.1.3 1.4.1 1.1.3 1.4.1 1.4.2 1.1.3.2

  35. Zdroje • http://www3.software.ibm.com/ ibmdl/pub/software/dw/dm/db2/0302roy/ 0302roy.pdf • http://www3.software.ibm.com/ ibmdl/pub/software/dw/dm/db2/0302roy/ DB2Node.zip

  36. Hnízděné intervaly 30.11.2005 Gergely Jakab Nested intervals

  37. Obsah • Úvod • Materializovaná cesta • Vnořené množiny • vylepšení • Vnořené intervaly • Základní myšlenka • Mapovaní • Normalizace • Funkce • Příklady: Test struktury

  38. Úvod • tranzitivní závislosti v relační databázi (hierarchické struktury) • 4 dobře známé metody z nichž 2 prozkoumáme • materializovaná cesta • vnořené množiny • Vadim Tropashko vymyslel smíšeninu těchto dvou metod (vnořené intervaly) • implementace a příklady na Oracle

  39. Skok zpátky : materializovaná cesta • každý záznam obsahuje absolutní cestu k uzlu • cesta = čísla uzlů od kořene oddělené separátorem • jistá podobnost k absolutním cestám na UNIXu • kompaktnější verze místo primárního klíče uzlů staví cestu z pořadového čísla uzlu mezi sourozenci

  40. Dotazy u materializované cesty • standardní dotazy s „LIKE“ : • efektivní na podřízené • NEefektivní na nadřízené • chytré dotazování pro nadřízené: • pomoci řetězcové funkce, která vrátí množinu cest všech nadřízených • f('1.2.1.1') = ('1.2.1', '1.2', '1') • …WHERE super.path in f(this.path)

  41. Vnořené množiny Albert (1,12) • uchovává globální pozici uzlu • pozice uzlu = 2 celé čísla definující interval [left, right], který obsahuje všechny potomky • uzel p je předkem uzlu c právě když :p.left <= c.left <= c.right <= p.right • nevýhody : • dotazy na nadřízené (hledání intervalů obsahující daný bod) • nestálá struktura – přidávání prvku ≈ přepočet půlku struktury • vhodné pouze pro statické množiny Bert (2,3) Chuck (4,11) Donna (5,6) Eddy (7,8) Fred (9,10)

  42. Vnořené množiny - vylepšení • původní pravidlo : • každý uzel (s pozicí [left, right]) má přesně (right - left - 1) / 2 potomků • vylepšení od Joe Celko : • vypuštěním výše uvedeného pravidla nechat větší mezery mezi left a right pro přidávané potomky • nepomůže, pokud left a right můžou být pouze celá čísla, protože za nějakou dobu se zaplní libovolně velká mezera

  43. Vnořené intervaly - definice • jde o zobecnění vnořených množin • hranice intervalů již nemusí být celá čísla • potřebujeme hustší doménu • podle potřeby můžou být racionální nebo reálné čísla • nadále platí : uzel p je předkem uzlu c právě kdyžp.left <= c.left <= c.right <= p.right • je několik způsobů jak umístit do struktury nový uzel

  44. Vnořené intervaly - 1.způsob • najdeme volný úsek <left1,right1> uvnitř intervalu rodiče • pak vkládaný uzel bude [(2*left1 + right1)/3, (left1 + 2*right1)/3] • a pro další potomky toho rodiče ještě jsou k dispozici :<left1, (2*left1 + right1)/3> a <(left1 + 2*right1)/3, right1> p.left left1 2*left1 + right1)/3 (left1 + 2*right1)/3 right1 p.right

  45. Vnořené intervaly - 2.způsob y • 2-dimenzionální model : • osa x je right • osa y je left • relace „býti podmnožinou“ je částečné uspořádání 1.1 1.2 1.3 1 x

  46. Vnořené intervaly - mapování • kořen zvolíme libovolně, např. [0,1] • každý uzel pak reprezentuje jeden pravoúhlý trojúhelník pod diagonálou • definujeme 2 důležité body: • bod konvergence do hloubky • průsečík diagonály a vertikály protínající uzel • bod konvergence do šířky • průsečík diagonály a horizontály protínající uzel y b.k. do hloubky b.k. do šířky 1.1 1 x

  47. Vnořené intervaly - mapování2 • pro každý uzel pozice jeho : • prvního syna je v polovině úsečky mezi uzlem a b.k. do hloubky • dalších synů je v polovině úsečky mezi předchozím synem a b.k. do šířky • např.: uzel 1.2 je [ ¼ , ½ ]

  48. Vnořené intervaly - symetrie • naše hustá doména nebude doména reálných ani racionálních čísel, ale doména dvojic zlomků • symetrie – každý podstrom má stejnou strukturu • syn je zmenšený rodič • obdoba fraktálů

  49. Vnořené intervaly - normalizace • kvůli zmíněné symetrie souřadnice uzlu nejsou na sobě nezávislé • z jejich sumy lze vypočítat složky x a y • namísto dvojic zlomků stačí pamatovat součet dvojic, tj. jeden zlomek, tj. 2 celá čísla • tím jsme se dostali na úroveň vnořených množin v množství uchovávaných dat na jeden uzel

  50. Vnořené intervaly - normalizace function x_numer( numer integer, denom integer ) RETURN integer IS ret_num integer; ret_den integer; BEGIN ret_num := numer+1; -- posun svisle nahoru na diagonálu ret_den := denom*2; -- souřadnice x je polovina podílu while floor(ret_num/2) = ret_num/2 loop -- redukce zlomku ret_num := ret_num/2; ret_den := ret_den/2; end loop; RETURN ret_num; -- pro čitatel -- RETURN ret_den;-- pro jmenovatel ve funkci x_denom() END;

More Related