SELECT c.* FROM customer_all c\nCONNECT BY PRIOR c.customer_id_high = c.customer_id\nSTART WITH c.customer_id = :cust_id<\/pre>\n<\/div>\nThis worked fine. However we had to change the query in order to return billing address of customers. Each customer\u00a0can have many addresses, but only one of them is a billing one. Such address has the flag CCBILL set to ‘X’. Thus, the query was changed into this one:<\/p>\n
SELECT level, c.*, cc.*\n FROM customer_all c,\n ccontact_all cc\n WHERE c.customer_id = cc.customer_id\n AND cc.ccbill = 'X'\nCONNECT BY PRIOR c.customer_id_high = c.customer_id\nSTART WITH c.customer_id = :cust_id<\/pre>\nThat was a mistake. The condition c.customer_id = cc.customer_id was applied before CONNECT BY whearas cc.ccbill = \u2018X\u2019 was executed after CONNECT BY. A customer (let’s suppose his\/her customer_id = 123456) had two addresses, while his superior\u00a0had one address and his superior’s superior\u00a0had also one address. The query was executed in the following way (I omit stage irrelevant to this example): Stage 1: 2 records are found for the client 123456: one for his billing address and one for another address. They have LEVEL=1 Stage 2: CONNECT BY clause is applied to both records with LEVEL=1\u00a0from the previous stage. Thus, the superior of 123456 is added twice to the result set and there are two records with LEVEL=2 Stage 3: CONNECT BY clause is applied to both records with LEVEL=2\u00a0from the previous stage. Thus the superior of the superior of 123456 is added twice to the result set and there are two records with LEVEL=3 Stage 4: The non-billing address of customer 123456 is removed from the result set, but his superiors records which were added because of this record are not removed, as their address is not a billing one (they do not have any non-billing address). As a result, the query returned 5 records, although it was expected to return 3 records. The records of the superior and superior’s superior were duplicated because of the second address of 123456. The record with the second address was removed but the branch it created was preserved. The solution was to check in CONNECT BY clause if the address in the PRIOR record is the billing one<\/p>\n
SELECT level, c.*, cc.*\n FROM customer_all c, ccontact_all cc\n WHERE c.customer_id = cc.customer_id\n AND cc.ccbill = 'X'\nCONNECT BY PRIOR c.customer_id_high = c.customer_id\n AND PRIOR cc.ccbill = 'X'<\/em><\/strong>\n START WITH c.customer_id = :cust_id<\/pre>\nThere is a clearer solution available – to make a join with addresses table after executing the hierarchical query:<\/p>\n
SELECT *\n FROM (SELECT c. *\n FROM customer_all c,\n CONNECT BY PRIOR c.customer_id_high = c.customer_id\n START WITH c.customer_id = :cust_id) c,\n ccontract_all cc\n WHERE c.customer_id = cc.customer_id\n AND cc.ccbill = 'X'<\/pre>\nThere is another interesting issue. When I changed the join to ANSI style (JOIN … ON instead of condition in the WHERE clause) then the execution time increased a few hundred thousand times. It seems that Oracle 9 on which I tested this query does not cope well with ANSI join syntax. This is not a surprise. Once we had to rapidly convert many views in a system. They used ANSI-style joins and when they were used in queries using Oracle-style joins then their performance dropped rapidly. We had to convert the views to use Oracle-style joins.<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"Hierarchical queries (CONNECT BY … START WITH …) are used when retrieving data from tree-like structures. You use…\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-408","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\/408","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=408"}],"version-history":[{"count":6,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/408\/revisions"}],"predecessor-version":[{"id":14298,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/408\/revisions\/14298"}],"wp:attachment":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/media?parent=408"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/categories?post=408"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/tags?post=408"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}