Weblog for the AMIS Technology corner
Posts tagged connect by
Oracle 11gR2 – alternative for CONNECT_BY_ISLEAF function for Recursive Subquery Factoring (dedicated to Anton)
Nov 14th
On our blog, we have been discussing the new hierarchical query functionality in Oracle Database 11g Release 2, using Recursive Suquery Factoring. Instead of using CONNECT BY and its close associates such as START WITH, PRIOR, LEVEL and more exotic comrades like SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT and NOCYCLE this release gave us a new, less proprietary and eventually probably more intuitive and functionally rich approach. We have also written how though we have no straightforward alternatives for LEVEL, SYS_CONNECT_BY_PATH and CONNECT_BY_ROOT – in the new recursive approach they are fantastically easy to emulate.
Until recently I have been quite happy with the new hierarchical querying and telling the world how I felt. Then an esteemed colleague – a far more experienced SQL programmer than I am – came up to me and remined me how the recursive sub query syntax at the present does not have a replacement for the CONNECT_BY_ISLEAF function – the SQL function that tells us whether a node produced in an hierarchical query has any children or is at the bottom of the chain – i.e. a leaf node. For leaf nodes (child-less), the function returns a value of one and for parent nodes the value is zero.
Anton (my colleague) was right and unfortunately I did not have a quick retort. However, after giving it some thought I believe I have found a way of emulating the CONNECT_BY_ISLEAF as well, using the new DEPTH FIRST ordering capabilities of the recursive subquery. I hope this will satisfy Anton as well.
Oracle RDBMS 11gR2 – new style hierarchical querying using Recursive Subquery Factoring
Sep 29th
Oracle Database 11g Release 2 introduces the successor to the good old Connect By based hierarchical querying, called Recursive Subquery Factoring. The basics are described in a previous article: http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it. This article will show some additional examples of using this recursive subquery factoring syntax.
The essence of this recursiveness: the subquery consists of two queries unioned together. The first query returns the root-nodes, the starting points in the tree or network. The second query is used to continually retrieve the next step or level: it refers to the subquery itself and finds the next node level based on the levels (root and zero or more previously retrieved levels) already retrieved by the subquery.
In code:
Oracle RDBMS 11gR2 – goodbye Connect By or: the end of hierarchical querying as we know it
Sep 1st
Many years ago, Oracle basically set the standard in SQL. Whatever was Oracle SQL could be seen as the standard. This has never been absolutely true – ANSI SQL was different from Oracle SQL. In some respects the differences can be traced back to lack of functional richness in the standard. However, in certain areas, Oracle has walked its own path with certain functions and features with the rest of the RDBMS pack following another route. It seems to be as if starting with Oracle RDBMS 9iR2, Oracle has made several important steps towards embracing the ANSI SQL standard syntax, usually while maintaining its own specific flavor of those same functions.
Some examples of Oracle specific syntax that are also available through their ANSI SQL counterpart – that in most instances has even more functionality – and that is almost always the preferred approach going forward:
- outer join syntax: in addition to Oracle’s (+) notation, 9iR2 introduced the left outer join, right outer join and full outer join (like (+) on both ends of the join condition, something that Oracle does not support)
- the Decode function that with 9iR2 can (and should) be replace by the CASE expression
- the to_char function that in many instances can be replace by extract {year|month|day|HOUR | MINUTE | SECOND} from date-value
- the ltrim and rtrim functions that can be replaced by the trim function with leading and trailing settings
The 11gR2 release has another example of Oracle specific SQL that is now complemented with the ANSI SQL standard approach:the CONNECT BY syntax for hierarchical querying that can now be replaced by the ANSI SQL standard Recursive Subquery Factoring clause, which is an extension to the WITH clause introduced as long ago as Oracle7 and still relatively underused by Oracle SQL developers.
