Splitting Strings: PL/SQL

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!

Splitting Strings: Proof!

In my previous post I used XMLTABLE and ora:tokenize to split a comma delimited string. Now I'll apply that technique to multiple rows, and show that it's faster than other methods.

Splitting Strings: Surprise!

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!

Spreadsheet-like Totals and Subtotals

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 CUBE function.

New, Improved IN Lists!

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

Kim Berg Hansen on “Use Cases of Row Pattern Matching in Oracle 12c”

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!

Chunking tables 8: second solution

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.

Chunking tables 7: prior sys_guid() ???

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.

Chunking tables 6: JOIN solution

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.