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.
We still want to split a table into 12 chunks with equal numbers of blocks, so we can access each chunk using a rowid range. We have the extents we need and now we need to calculate the exact chunk boundaries.
After "only" 3 posts on chunking tables, we now have the raw materials for breaking up a table into 12 chunks with equal numbers of blocks, and for getting ROWID ranges that let us access the data. Now let's chunk!
So far we've decided to access a table chunk by chunk, each chunk containing one twelfth of the tables's blocks. But first, how do we access data in a chunk of blocks? How do we even know what blocks a table has?