Marc Bleron and Todd Hershiser gave me some very valuable feedback through their comments on my recent "Splitting Strings" posts. The big news is: PL/SQL beats SQL!
In my previous post I used
ora:tokenizeto split a comma delimited string. Now I'll apply that technique to multiple rows, and show that it's faster than other methods.
In my post New, Improved IN Lists!, I split one string into multiple rows. Now I want to split multiple input strings - but first, I've rediscovered an even faster technique!
We very often make spreadsheets with subtotals for each row and for each column. Someone on the OTN forum asked how to produce data in this format. I answered using the cool
In SQL, the "varying IN list" problem comes up constantly and there are many published solutions. My new favorite solution is inspired from the video Synthesizing rows inside Oracle by Connor McDonald (start at the 6 minute mark).
As I write this, I am listening to Kim Berg Hansen explain the MATCH_RECOGNIZE clause. He was kind enough to give me credit for some of the examples and mention this blog. In addition to my blog posts on the subject, you may enjoy my presentation on SlideShare. Please download it to see the animations!
Here is the second complete solution to the problem of dividing a table into chunks of equal size. This is the solution whose first version was marked "correct" on OTN. It is the basis for what Bryn Llewellyn calls "Approx_Method_Sql_Ashton_2" in his white paper.
The second solution for dividing a table into equal chunks does not do a JOIN. Instead it expands extent rows that contain several chunk boundaries, using an obscure method that I need to explain.
At last, here is the first complete solution to the problem of dividing a table into 12 chunks with equal numbers of blocks. James Su and I proposed this solution on OTN, but without the "filtered_extents" optimisation.