Have you ever used "recursive subquery factoring"? It is a fantastic new feature available since 11g release 2 and basically is subquery factoring with ... well, recursion! :-)
If you have never heard before about "subquery factoring", then maybe you have used a "with subquery", which is the same thing. If you haven't ever used a "with subquery" then you should probably read some relevant infο first before proceeding to this post, e.g., this post.
Many say that recursive subquery factoring is the replacement of "connect by" queries (aka "hierarchical queries"), since it is included in the SQL ANSI standard and indeed one can implement any type of hierarchical query with it. However, recursive factored subqueries are much more powerful and can be used in a much broader range of use cases.
In this post, we will give a couple of simple examples of recursive subquery factoring (RSF) and then use it in order to solve an interesting problem (one that is not a "hierarchical query" problem). The problem is how to "undo" a LISTAGG operation.
In other words, we will show how we can use recursive subquery factoring in order to turn a list of string elements into a set of individual rows, where each row stores a single string element, This solution, because of the use of recursion is really elegant!
If you have never heard before about "subquery factoring", then maybe you have used a "with subquery", which is the same thing. If you haven't ever used a "with subquery" then you should probably read some relevant infο first before proceeding to this post, e.g., this post.
Many say that recursive subquery factoring is the replacement of "connect by" queries (aka "hierarchical queries"), since it is included in the SQL ANSI standard and indeed one can implement any type of hierarchical query with it. However, recursive factored subqueries are much more powerful and can be used in a much broader range of use cases.
In this post, we will give a couple of simple examples of recursive subquery factoring (RSF) and then use it in order to solve an interesting problem (one that is not a "hierarchical query" problem). The problem is how to "undo" a LISTAGG operation.
In other words, we will show how we can use recursive subquery factoring in order to turn a list of string elements into a set of individual rows, where each row stores a single string element, This solution, because of the use of recursion is really elegant!
