{"id":21,"date":"2009-02-23T10:45:19","date_gmt":"2009-02-23T08:45:19","guid":{"rendered":"http:\/\/touk.pl\/blog\/?p=21"},"modified":"2023-03-16T14:31:46","modified_gmt":"2023-03-16T13:31:46","slug":"subtelny-feature-konstrukcji-connect-by","status":"publish","type":"post","link":"https:\/\/touk.pl\/blog\/2009\/02\/23\/subtelny-feature-konstrukcji-connect-by\/","title":{"rendered":"Subtelny feature konstrukcji CONNECT BY"},"content":{"rendered":"

Je\u015bli kto\u015b u\u017cywa konstrukcji CONNECT BY i chce unikn\u0105\u0107 subtelnych b\u0142\u0119d\u00f3w to powinienen zapozna\u0107 si\u0119 z opisan\u0105 poni\u017cej w\u0142asno\u015bci\u0105. Je\u015bli w kwerendzie hierarchicznej (czyli u\u017cywaj\u0105cej konstrukcji CONNECT BY … START WITH …) u\u017cywa si\u0119 r\u00f3wnocze\u015bnie z\u0142\u0105cze\u0144, czy to ANSI czy Oracle’owych to wynik jest nieintuicyjny. W\u0142a\u015bnie wykry\u0142em istotny b\u0142\u0105d w moim kodzie z tym zwi\u0105zany. Kwerenda hierarchiczna to takie co\u015b, co pozwala uzyska\u0107 w jednym zapytaniu np. list\u0119 wszystkich prze\u0142o\u017conych danego pracownika, ale nie tylko tych bezpo\u015brednich, ale r\u00f3wnie\u017c prze\u0142o\u017conych tych prze\u0142o\u017conych, itd. Chodzi o uzyskanie informacji z drzewa. Dobry opis dzia\u0142ania kwerend hierarchicznych znajduje si\u0119 tutaj:<\/p>\n

http:\/\/download.oracle.com\/docs\/cd\/B10501_01\/server.920\/a96540\/queries4a.htm#2053937<\/a> S\u0105 tam 2 wa\u017cne punkty:
\n* If the|WHERE| predicate contains a join, Oracle applies the joinpredicates \/before\/<\/em> doing the |CONNECT| |BY| processing.
\n* If the |WHERE| clause does not contain a join, Oracle applies allpredicates other than the|CONNECT| |BY| predicates _\/after\/_doing the |CONNECT| |BY|processing without affecting the other rows of the hierarchy. Je\u015bli klauzura where zawiera i z\u0142\u0105czenie i nie-z\u0142\u0105czenie (warunek nie b\u0119d\u0105cy z\u0142\u0105czeniem) to z\u0142\u0105czenie dokonywane jest przed przetwarzaniem connect by a nie-z\u0142\u0105czenie po. Jako przyk\u0142ad opisz\u0119 w uproszczeniu wykryty b\u0142\u0105d: Kwerenda mia\u0142a zwraca\u0107 dla danego klienta list\u0119 jego prze\u0142o\u017conych z jego firmy (w uproszczeniu)<\/p>\n

SELECT c.* FROM customer_all c\r\nCONNECT BY PRIOR c.customer_id_high = c.customer_id\r\nSTART WITH c.customer_id = 123456<\/pre>\n
Potem zasz\u0142a potrzeba, aby pobiera\u0107 te\u017c adresy billingowe klient\u00f3w. Ka\u017cdy klient mo\u017ce mie\u0107 wiele adres\u00f3w, ale tylko jeden billingowy czyli z flag\u0105 CCBILL=’X’. Kwerenda zosta\u0142a zmieniona na<\/p>\n
SELECT level, c.*, cc.*\r\n  FROM customer_all c,\r\n       ccontact_all cc\r\n WHERE c.customer_id = cc.customer_id\r\n   AND cc.ccbill = 'X'\r\nCONNECT BY PRIOR c.customer_id_high = c.customer_id\r\nSTART WITH c.customer_id = 123456<\/pre>\n
I tu by\u0142 b\u0142\u0105d. Warunek c.customer_id = cc.customer_id nast\u0105pi\u0142 przed wykonaniem connect by a cc.ccbill = ‘X’ po, co spowodowa\u0142o \u017ce b\u0142\u0105d wyst\u0105pi\u0142, gdy klient 123456 mia\u0142 dwa adresy a jego prze\u0142o\u017cony i prze\u0142o\u017cony prze\u0142o\u017conego mieli po jednym. Liczenie zapytania wygl\u0105da\u0142o nast\u0119puj\u0105co: Etap 1: w wyniku zapytania wyst\u0119puj\u0105 2 rekordy dla klienta 123456, jeden dla adresu billingowego, drugi dla innego adresu Etap 2: dla obu rekord\u00f3w powsta\u0142ych w poprzednim etapie wykonywany jest warunek connect by. Do wynik\u00f3w 2 razy zostaje wrzucony prze\u0142o\u017cony 123456 Etap 3: dla obu rekord\u00f3w powsta\u0142ych w poprzednim etapie wykonywany jest warunek connect by. Do wynik\u00f3w 2 razy zostaje wrzucony prze\u0142o\u017cony prze\u0142o\u017conego 123456 Etap 4: Usuni\u0119ty zosta\u0142 rekord z adresem niebillingowym dla klienta 123456, ale rekordy jego prze\u0142o\u017conych dodane z powodu tego rekordu nie zosta\u0142y usuni\u0119te poniewa\u017c<\/p>\n
*ich*<\/strong> adres by\u0142 billingowy (nie mieli \u017cadnego nie-billingowego). Skutek by\u0142 taki, \u017ce kwerenda zwr\u00f3ci\u0142a 5 rekord\u00f3w, a powinna by\u0142a zwr\u00f3ci\u0107 3: rekordy prze\u0142o\u017conego i prze\u0142o\u017conego prze\u0142o\u017conego zosta\u0142y zdublikowane z powodu drugiego adresu klienta 123456. Rekord z jego drugim adresem zosta\u0142 usuni\u0119ty, ale ga\u0142\u0105\u017a, kt\u00f3ra z niego powsta\u0142a – nie. Rozwi\u0105zaniem by\u0142o dodanie warunku na poprzedni rekord w CONNECT BY, koniecznie z PRIOR: PRIOR cc.ccbill = ‘X’<\/p>\n
SELECT level, c.*, cc.*\r\n  FROM customer_all c, ccontact_all cc\r\n WHERE c.customer_id = cc.customer_id\r\n   AND cc.ccbill = 'X'\r\nCONNECT BY PRIOR c.customer_id_high = c.customer_id\r\n       AND PRIOR cc.ccbill = 'X'\r\n START WITH c.customer_id = 123456<\/pre>\n
Bezpiecznym rozwi\u0105zaniem by\u0142oby te\u017c robienie joina z ccontract_all dopiero po obliczeniu wyniku kwerendy hierarchicznej.<\/p>\n
SELECT *\r\n  FROM (SELECT c. *\r\n          FROM customer_all c,\r\n        CONNECT BY PRIOR c.customer_id_high = c.customer_id\r\n         START WITH c.customer_id = 123456) c,\r\n       ccontract_all cc\r\n WHERE c.customer_id = cc.customer_id\r\n   AND cc.ccbill = 'X'<\/pre>\n
Jeszcze tak\u0105 ciekaw\u0105 rzecz zauwa\u017cy\u0142em: gdy w zapytaniu posiadaj\u0105cym i z\u0142\u0105czenia i kwerend\u0119 hierarchiczn\u0105 zrobi si\u0119 z\u0142\u0105czenie poprzez konstrukcj\u0119 JOIN … ON a nie warunek w WHERE to czas wykonania wzrasta kilkaset tysi\u0119cy razy! By\u0107 mo\u017ce s\u0105 przypadki, gdy jest odwrotnie, wi\u0119c warto to zawsze sprawdzi\u0107.<\/p>\n","protected":false},"excerpt":{"rendered":"Je\u015bli kto\u015b u\u017cywa konstrukcji CONNECT BY i chce unikn\u0105\u0107 subtelnych b\u0142\u0119d\u00f3w to powinienen zapozna\u0107 si\u0119 z opisan\u0105 poni\u017cej…\n","protected":false},"author":8,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[687,13],"class_list":{"0":"post-21","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-development-design","7":"tag-db","8":"tag-oracle"},"_links":{"self":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/21","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/comments?post=21"}],"version-history":[{"count":10,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/21\/revisions"}],"predecessor-version":[{"id":15293,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/21\/revisions\/15293"}],"wp:attachment":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/media?parent=21"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/categories?post=21"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/tags?post=21"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}