{"id":1872,"date":"2016-11-26T09:11:53","date_gmt":"2016-11-26T09:11:53","guid":{"rendered":"http:\/\/aprogrammerwrites.eu\/?p=1872"},"modified":"2025-08-04T14:58:10","modified_gmt":"2025-08-04T14:58:10","slug":"dimensional-benchmarking-of-general-sql-bursting-problems","status":"publish","type":"post","link":"http:\/\/aprogrammerwrites.eu\/?p=1872","title":{"rendered":"Dimensional Benchmarking of General SQL Bursting Problems"},"content":{"rendered":"

[This article was migrated to the author’s GitHub Pages site here<\/a> in August 2025]<\/strong><\/p>\n

I recently posted an article on Dimensional Benchmarking of Oracle v10-v12 Queries for SQL Bursting Problems<\/a>. This article added an Oracle v12 SQL solution, involving Match_Recognize to benchmark against some v10 and v11 solutions that I had posted on Scribd a few years ago. A few days before posting it I noticed an OTN thread with a problem that struck me as being of a similar type, Amalgamating groups to be beyond a given size threshold<\/a>. Where in my original ‘bursting’ problem a group is defined by a maximum interval from its starting date, in the OTN problem a group is defined by the cumulative sum of a numeric attribute from the group starting record.<\/p>\n

I added a comment on the thread at the time mentioning the results that I had got on the original problem, and adding a model solution for the problem raised on the new thread. I have now taken this second ‘bursting’-type problem and have benchmarked both the main two solutions proposed on that thread (by other posters), as well as two versions of my own model solution, and a variant of the recursive subquery factor solution that uses a temporary table to achieve much faster performance. <\/p>\n

Also, I noticed a question just yesterday on AskTom that is posing essentially the same problem as in my earlier article (which itself came from AskTom several years ago \ud83d\ude42 ), Complex sql<\/a>.<\/p>\n

The results show that Match_Recognize, as before, is by far the most efficient solution. They also show that the faster solutions vary linearly with dataset size (within a given partition), while the slow ones vary quadratically. One interesting finding is that the solution by the Model clause can be changed from very slow, and quadratically varying, to linearly varying, and second in performance only to Match_Recognize, by using a rule ordering clause (which avoids the need for automatic rules ordering).<\/p>\n

I obtained these results on my Windows 10 home computer with Oracle 12.1, and used my own benchmarking framework, available on GitHub, A Framework for Dimensional Benchmarking of SQL Query Performance<\/a>.<\/p>\n

Generalised ‘Bursting’ Problem Definition<\/strong><\/p>\n

The problem is to determine break groups using a running aggregate based on some function of the record attributes, with a defined ordering, starting from the group starting record, and with a group’s end record defined by the aggregate reaching (or exceeding) some limit. One may consider the first record reaching (or exceeding) the limit to define the first record in the next group, as in the original bursting problem, or to be the last record in the current group, as in the OTN example.<\/p>\n

The data are partitioned by some key in general. <\/p>\n

OTN-like Item Weights ‘Bursting’ Problem<\/strong><\/p>\n

The data structure used in this article is based on that of the original poster in the OTN thread, but with more generic table and column names.<\/p>\n

ITEMS Table<\/em><\/p>\n

CREATE TABLE items (\r\n    id          NUMBER NOT NULL, \r\n    cat         VARCHAR2(30) NOT NULL, \r\n    seq         NUMBER NOT NULL, \r\n    weight      NUMBER NOT NULL,\r\n    CONSTRAINT itm_pk PRIMARY KEY (id)\r\n)\r\n\/\r\nCREATE INDEX items_N1 ON items (cat, seq)\r\n\/\r\nDROP  SEQUENCE itm_s\r\n\/\r\nCREATE SEQUENCE itm_s\r\n\/\r\n<\/pre>\n
Functional Test Data<\/em><\/p>\n
I created test data with a test weight limit of 10, as follows, with groups shown at detailed level. The first two categories are taken from the OTN problem, while I added a third category to test the case where the limit is not reached.<\/p>\n
 ID CAT                                   SEQ     WEIGHT SUB_WEIGHT  FINAL_GRP\r\n--- ------------------------------ ---------- ---------- ---------- ----------\r\n  1 Rural                                  10          2          2          4\r\n  2                                         9          3          5          4\r\n  3                                         8          1          6          4\r\n  4                                         7          4         10          4\r\n  5                                         6         11         11          5\r\n  6                                         5          2          2          9\r\n  7                                         4          2          4          9\r\n  8                                         3          4          8          9\r\n  9                                         2         30         38          9\r\n 10                                         1         12         12         10\r\n\r\n 11 Urban                                  10          1          1         12\r\n 12                                         9         12         13         12\r\n 13                                         8          2          2         15\r\n 14                                         7          5          7         15\r\n 15                                         6          7         14         15\r\n 16                                         5         15         15         16\r\n 17                                         4         25         25         17\r\n 18                                         3          2          2         20\r\n 19                                         2          1          3         20\r\n 20                                         1          8         11         20\r\n\r\n 21 Suburban                               10          2          2         22\r\n 22                                         9          1          3         22\r\n\r\n22 rows selected.\r\n<\/pre>\n
The queries shown later give the groups at summary level, as follows:<\/p>\n
CAT                              # Groups\r\n------------------------------ ----------\r\nRural                                   4\r\nSuburban                                1\r\nUrban                                   5\r\n\r\n<\/pre>\n
A Note on Functional Testing and ‘Scenario Coverage’<\/em><\/p>\n
In the Match_Recognize query proposed in the OTN thread the pattern is defined in terms of two categories, say s and t, where:<\/p>\n