DDevelopment & Design

Subtle feature of CONNECT BY

Hierarchical queries (CONNECT BY … START WITH …) are used when retrieving data from tree-like structures. You use them if for example you want to retrieve all people who are directly or indirectly below a certain person in a company hierarchy. However, if you use joins and add some other conditions in the WHERE clause then the effect of the query is not obvious.

The good description of hierarchical queries can be found on the page

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/queries4a.htm#2053937 There are 2 important points in this article:
* If the|WHERE| predicate contains a join, Oracle applies the join predicates /before/ doing the |CONNECT| |BY| processing.
* If the |WHERE| clause does not contain a join, Oracle applies all predicates other than the|CONNECT| |BY| predicates _/after/_doing the |CONNECT| |BY|processing without affecting the other rows of the hierarchy. What does it mean? How does it work? In order to explain this I will describe the error I came across before I understood how the hierarchical queries really work. The query was meant to return all direct and not direct superiors for a given customer (some customers work in companies).

SELECT c.* FROM customer_all c
CONNECT BY PRIOR c.customer_id_high = c.customer_id
START WITH c.customer_id = :cust_id

This worked fine. However we had to change the query in order to return billing address of customers. Each customer can 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:

SELECT level, c.*, cc.*
  FROM customer_all c,
       ccontact_all cc
 WHERE c.customer_id = cc.customer_id
   AND cc.ccbill = 'X'
CONNECT BY PRIOR c.customer_id_high = c.customer_id
START WITH c.customer_id = :cust_id

That was a mistake. The condition c.customer_id = cc.customer_id was applied before CONNECT BY whearas cc.ccbill = ‘X’ was executed after CONNECT BY. A customer (let’s suppose his/her customer_id = 123456) had two addresses, while his superior had one address and his superior’s superior had 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 from 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 from 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

SELECT level, c.*, cc.*
  FROM customer_all c, ccontact_all cc
 WHERE c.customer_id = cc.customer_id
   AND cc.ccbill = 'X'
CONNECT BY PRIOR c.customer_id_high = c.customer_id
       AND PRIOR cc.ccbill = 'X'
 START WITH c.customer_id = :cust_id

There is a clearer solution available – to make a join with addresses table after executing the hierarchical query:

SELECT *
  FROM (SELECT c. *
          FROM customer_all c,
        CONNECT BY PRIOR c.customer_id_high = c.customer_id
         START WITH c.customer_id = :cust_id) c,
       ccontract_all cc
 WHERE c.customer_id = cc.customer_id
   AND cc.ccbill = 'X'

There 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.

— Previous article

<!--:en-->Accessing Microsoft SQL Server from Oracle<!--:-->

Next article —

<!--:en-->Agile Skills Project at my company<!--:-->

You May Also Like
DDevelopment & Design

Log4j and MDC in Grails

Log4j provides very useful feature: MDC - mapped diagnostic context. It can be used to store data in context of current thread. It may sound scary a bit but idea is simple.

My post is based on post http://burtbeckwith.com/blog/?p=521 from Burt Beckwith's excellent blog, it's definitely worth checking if you are interested in Grails.

Short background story...


Suppose we want to do logging our brand new shopping system and we want to have in each log customer's shopping basket number. And our system can be used at once by many users who can perform many transactions, actions like adding items and so on. How can we achieve that? Of course we can add basket number in every place where we do some logging but this task would be boring and error-prone. 

Instead of this we can use MDC to store variable with basket number in map. 

In fact MDC can be treated as map of custom values for current thread that can be used by logger. 


How to do that with Grails?


Using MDC with Grails is quite simple. All we need to do is to create our own custom filter which works for given urls and puts our data in MDC.

Filters in Grails are classes in directory grails-app/conf/* which names end with *Filters.groovy postfix. We can create this class manually or use Grails command: 
grails create-filters info.rnowak.App.Basket

In result class named BasketFilters will be created in grails-app/conf/info/rnowak/UberApp.

Initially filter class looks a little bit empty:
class BasketFilters {
    def filters = {
        all(controller:'*', action:'*') {
            before = {

            }
            after = { Map model ->

            }
            afterView = { Exception e ->

            }
        }
    }
}
All we need to do is fill empty closures, modify filter properties and put some data into MDC.

all is the general name of our filter, as class BasketFilters (plural!) can contain many various filters. You can name it whatever you want, for this post let assume it will be named basketFilter. 

Another thing is change of filter parameters. According to official documentation (link) we can customize our filter in many ways. You can specify controller to be filtered, its actions, filtered urls and so on. In our example you can stay with default option where filter is applied to every action of every controller. If you are interested in filtering only some urls, use uri parameter with expression describing desired urls to be filtered.

Three closures that are already defined in template have their function and they are started in these conditions:

  • before - as name says, it is executed before filtered action takes place
  • after - similarly, it is called after the action
  • afterView - called after rendering of the actions view
Ok, so now we know what are these mysterious methods and when they are called. But what can be done within them? In official Grails docs (link again) under section 7.6.3 there is a list of properties that are available to use in filter.

With that knowledge, we can proceed to implementing filter.

Putting something into MDC in filter


What we want to do is quite easy: we want to retrieve basket number from parameters and put it into MDC in our filter:
class BasketFilters {
    def filters = {
        basketFilter(controller:'*', action:'*') {
            before = {
                MDC.put("basketNumber", params.basketNumber ?: "")
            }
            after = { Map model ->
                MDC.remove("basketNumber")
            }
        }
    }
}

We retrieve basket number from Grails params map and then we put in map under specified key ("basketNumber" in this case), which will be later used in logger conversion pattern. It is important to remove custom value after processing of action to avoid leaks.

So we are putting something into MDC. But how make use of it in logs?


We can refer to custom data in MDC in conversion patter using syntax: %X{key}, where key is our key we used in filter to put data, like:
def conversionPattern = "%d{yyyy-MM-dd HH:mm:ss} %-5p %t [%c{1}] %X{basketNumber} - %m%n"


And that's it :) We've put custom data in log4j MDC and successfully used it in logs to display interesting values.