Main menu

Category Archives: v12

I noticed an interesting thread on OTN recently, Matching ( in a string. It's about using SQL to find matching bracket pairs (technically 'parentheses' but 'brackets' is shorter, and it makes no difference to the SQL). Incidentally, I found recently that nested bracket expressions are a nice way of compactly representing the structure of complex hash join execution plans, A Note on Oracle Join Orders and Hints.

I thought it would be interesting to run some of the solution queries through my benchmarking package to test performance (A Framework for Dimensional Benchmarking of SQL Performance). I decided to consider only the queries that addressed multiple records, and the form of the problem that requires returning record uid, opening and closing bracket positions, plus the substring enclosed. These were by 'mathguy' and 'Peter vd Zwan', and I made very minor tweaks for consistency. I also wrote a query myself using PL/SQL in an inline SQL function using the new (v12.1) 'WITH Function' functionality, and copied this to a version using a pipelined database function to check for any performance differences. The four queries tested were then:

CBL_QRY, mathguy: Connect By, Analytics, Regex

MRB_QRY, Peter vd Zwan: Connect By, Match_Recognize

WFB_QRY, me: With PL/SQL Function, Arrays

PFB_QRY, me: Pipelined PL/SQL Function, Arrays

Bracket Pair Definition

Consider a function (BrDiff) defined at each character position as the difference between the number of opening and closing brackets to the left of, or at, that position.

A closing bracket closes an opening bracket if it is the first closing bracket where BrDiff is 1 less than BrDiff at the opening bracket. If all brackets are in some pair, then the expression can be considered well-formed.

This can be illustrated with a diagram for the fourth functional example below.

Test Problem

BRACKET_STRINGS Table

CREATE TABLE bracket_strings (id NUMBER, str VARCHAR2(4000))
/

Functional Test Data

I took four of mathguy's test records, excluding the (deliberately) badly-formed strings, and which included some embedded returns:

Each test set consisted of 100 records with the str column containing the brackets expression dependent on width (w) and depth (d) parameters, as follows:

Each str column contains w bracket pairs

The str column begins with a 3-character record number

After the record number, the str column begins with d opening brackets with 3 characters of text, like: '(001', etc., followed by the d closing brackets, then the remaining w-d pairs in an unnested sequence, like '(001)'

When w=d the pairs are fully nested, and when d=0 there is no nesting, just a sequence of '(123)' stringss.

This choice of test data sets allows us to see if both number of brackets, and bracket nesting have any effect on performance.

The output from the test queries therefore consists of 100*w records with a record identifier and a bracketed string. For performance testing purposes the benchmarking framework writes the results to a file in csv format, while counting only the query steps in the query timing results.

All the queries showed strong time correlation with width, with smaller correlation with depth.

CBL_QRY is significantly faster than MRB_QRY, which appears to be rising quadratically

Both WFB_QRY and PFB_QRY are much faster than the non-PL/SQL queries

PFB_QRY is slightly faster than WFB_QRY. This could be regarded as too small a difference to be significant, but is consistent across the data points, despite the context switching with database function calls

I recently posted an article on Dimensional Benchmarking of Oracle v10-v12 Queries for SQL Bursting Problems. 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. 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.

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.

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 🙂 ), Complex sql.

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

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.

The data are partitioned by some key in general.

OTN-like Item Weights 'Bursting' Problem

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.

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.

In the Match_Recognize query proposed in the OTN thread the pattern is defined in terms of two categories, say s and t, where:

s denotes a record where the running sum < the limit

t denotes a record where the running sum >= the limit

The pattern to match can be written as (s* t?) meaning zero or more category s records, followed by zero or one category t records. This immediately suggests that any given match falls into one of the following scenarios for frequencies of (s, t):

(0, 0) - this looks like an empty set of records, but could be non-empty if null values were allowed for the weight

(1+, 0) - the case where the limit is not reached, which must be the last match if there are no null weights

(0, 1) - where the first record in a group reaches the limit by itself

(1+, 1) - where one or more records in a group are below the limit, followed by a record that reaches the limit

In the results above, we see that group 22 matches scenario 2, while groups 10, 16 and 17 match scenario 3, and the remainder match scenario 4. We take the weight to be not null so scenario 1 is not possible. This kind of 'scenario coverage' is much more important than the 'code coverage' that is often focussed on in testing, especially by object oriented programmers.

In the following sections for individual queries, the query (and other SQL) is listed first, followed by the execution plan for the largest problem (W40-D8000).

In some cases, Oracle Database may not be able to ascertain that your model is acyclic even though there is no cyclical dependency among the rules. This can happen if you have complex expressions in your cell references. Oracle Database assumes that the rules are cyclic and employs a CYCLIC algorithm that evaluates the model iteratively based on the rules and data. Iteration stops as soon as convergence is reached and the results are returned. Convergence is defined as the state in which further executions of the model will not change values of any of the cell in the model. Convergence is certain to be reached when there are no cyclical dependencies.

When we specify automatic order, the solution is obtained without error using Oracle's cyclic algorithm (operation SQL MODEL CYCLIC). Unfortunately, in this case there is a large performance impact, and we will see in the results section that execution time varies as the square of the number of records within a partition, i.e. quadratically.

In the query above, the rules order clause is omitted, thus defaulting to sequential, while avoiding the ORA-32637 error. This is achieved by specifying ORDER BY rn DESC on the left side of the second rule. The solution, via operation SQL MODEL ORDERED is much faster, and we will see in the results section that execution time now varies linearly with the number of records within a partition.

The query above is essentially the same as one of the posters proposed on the OTN thread, with a slight tweak to the pattern that does not alter its meaning, and also changing it to return one row per match. The query performs much more efficiently than any of the other queries, using the Match_Recognize clause introduced in Oracle 12.1 SQL for Pattern Matching.

This is based on the second of the recursive subquery factor queries in the OTN thread, and we can see the performance issue in the plan above. The recursive branch of the UNION ALL executes once for each record within a partition and performs a full scan on the items table each time. This results in execution time varying as the square of the number of records within a partition, as can be seen in the results section later. The performance can be much improved by using a temporary table, as in the next query.

In this solution, the initial subquery from the previous query is written to a temporary table that is indexed on the join column. This means that the join in the recursive branch of the UNION ALL is indexed and much quicker, resulting in linear variation in execution time with the number of records in a partition.

Notice that it was necessary to hint the index usage. It is possible to achieve the indexed join without a hint by including a call to gather statistics in the pre-query SQL. Unfortunately, Oracle's DBMS_Stats procedure performs a commit - which clears the data from the temporary table. Although we could get around the clearing of the table by making it a normal table and manually truncating it, it is probably better to accept this as a valid use-case for a hint - after all, the whole purpose of the temporary table is to permit index use.

Performance Testing Results

The 'width' parameter is taken to be the number of cat values partitioning the dataset, while the 'depth' parameter is taken to be the number of records within each category. The weight is assigned a random integer between 1 and 100, and the weight limit is 5,000.

Record Counts Table

Input Record Counts

Depth

W10

W20

W40

D1000

10,000

20,000

40,000

D2000

20,000

40,000

80,000

D4000

40,000

80,000

160,000

D8000

80,000

160,000

320,000

Output Record Counts

Depth

W10

W20

W40

D1000

105

209

422

D2000

205

411

829

D4000

406

815

1,625

D8000

808

1,618

3,229

Elapsed Times Table (elapsed seconds)

MOD_QRY

Elapsed Seconds

Depth Ratios to Prior

Width Ratios to Prior

Depth

W10

W20

W40

W10

W20

W40

W20

W40

D1000

16

47

99

2.9

2.1

D2000

62

190

397

3.9

4.0

4.0

3.1

2.1

D4000

243

762

1,390

3.9

4.0

3.5

3.1

1.8

D8000

962

2,082

5,566

4.0

2.7

4.0

2.2

2.7

Average

3.9

3.6

3.8

2.8

2.2

MOD_QRY_D

Elapsed Seconds

Depth Ratios to Prior

Width Ratios to Prior

Depth

W10

W20

W40

W10

W20

W40

W20

W40

D1000

0.08

0.16

0.31

2.0

1.9

D2000

0.16

0.30

0.61

2.0

1.9

2.0

1.9

2.0

D4000

0.30

0.59

1.19

1.9

2.0

2.0

2.0

2.0

D8000

0.59

1.20

2.42

2.0

2.0

2.0

2.0

2.0

Average

1.9

2.0

2.0

2.0

2.0

MTH_QRY

Elapsed Seconds

Depth Ratios to Prior

Width Ratios to Prior

Depth

W10

W20

W40

W10

W20

W40

W20

W40

D1000

0

0.016

0.016

#DIV/0!

1.0

D2000

0.016

0.016

0.047

#DIV/0!

1.0

2.9

1.0

2.9

D4000

0.016

0.031

0.078

1.0

1.9

1.7

1.9

2.5

D8000

0.047

0.094

0.172

2.9

3.0

2.2

2.0

1.8

Average

2.0

2.0

2.3

1.6

2.1

RSF_QRY

Elapsed Seconds

Depth Ratios to Prior

Width Ratios to Prior

Depth

W10

W20

W40

W10

W20

W40

W20

W40

D1000

6

12

24

2.0

2.0

D2000

23

46

94

3.8

3.8

3.9

2.0

2.0

D4000

92

185

377

4.0

4.0

4.0

2.0

2.0

D8000

369

750

1,513

4.0

4.1

4.0

2.0

2.0

Average

3.9

4.0

4.0

2.0

2.0

RSF_TMP

Elapsed Seconds

Depth Ratios to Prior

Width Ratios to Prior

Depth

W10

W20

W40

W10

W20

W40

W20

W40

D1000

0.09

0.19

0.36

2.1

1.9

D2000

0.19

0.38

0.73

2.1

2.0

2.0

2.0

1.9

D4000

0.39

0.77

1.53

2.1

2.0

2.1

2.0

2.0

D8000

0.77

1.55

3.49

2.0

2.0

2.3

2.0

2.3

Average

2.0

2.0

2.1

2.0

2.0

Slice Graphs

Performance Discussion

Variation with Width

The width parameter represents the number of categories here, and category (CAT) is the query partitioning key. We might therefore expect that the execution time would be proportional to the width when the depth parameter is fixed. The width values used were 10, 20 and 40, so we would expect times to double between W10 and W20, and again between W20 and W40.

In fact, we see from the width ratios columns in the tables that this expectation is very closely matched in the cases of MOD_QRY_D, RSF_QRY, AND RSF_TMP.

For MOD_QRY, the ratios are quite variable, and mostly above 2, so that the CYCLIC Model algorithm does not meet our expectation.

For MTH_QRY (Match_Recogize), the elapsed times are very small, 0.17 for the largest problem (14 times faster than the next best, MOD_QRY_D), and that likely explains the variance.

Variation with Depth

The depth parameter represents the number of of records for each category. The depth ratios show that two of the queries show very close to quadratic variation of time with depth, while three show very close to linear variation, and the linear queries are unsurprisingly much faster.

MOD_QRY and RSF_QRY vary quadratically with depth (number of records per partition key).

As in the earlier article, the new v12.1 feature Match_Recogize proved to be much faster than the other techniques for this problem

The solution using Model clause with the operation SQL MODEL CYCLIC showed quadratic variation in execution times with size, but a very simple change to allow SQL MODEL ORDERED operation produced linear variation, and was second only to Match_Recogize in performance

Recursive subquery factoring had timings that increased quadratically with number of records; this was due to a combination of the number of starts of a subquery, and full scans within it

What we call the beginning is often the end
And to make an end is to make a beginning.
The end is where we start from. And every phrase
And sentence that is right (where every word is at home,
Taking its place to support the others,
The word neither diffident nor ostentatious,
An easy commerce of the old and the new,
The common word exact without vulgarity,
The formal word precise but not pedantic,
The complete consort dancing together)
Every phrase and every sentence is an end and a beginning,
Every poem an epitaph

- from Little Gidding by T.S. Eliot

A few years ago I wrote some SQL queries to assign dated records into groups defined by each record being within a fixed window of its starting record (the original Scribd document I wrote is embedded at the bottom). This is a bit harder than it sounds in pure SQL, without using PL/SQL, and I could only do it using new features from versions 10 and 11 of Oracle. With companies increasingly migrating to version 12, I thought it might be interesting to compare these queries with a query using the new 12c feature MATCH_RECOGNIZE. It turns out that the 12c query is both simpler and faster than the earlier queries. I'll describe the problem with a simple functional test data set first, then will give the SQL for each of four methods with the execution plan for a larger data set. At the end I summarise the results from the four methods across a range of problem sizes.

The problem is to determine the break groups using distance from the group start point. In other words, once a group starts, all records that start within a fixed distance from the group start are in the group, and the first record after the end of a group defines the next group start. The data are partitioned by some key in general (here person_id). The problem data structure is based on a question posed in Tom Kyte’s Oracle forum, Activities and breaks, while the test data are my own.

500w records are generated for each of three persons, where w is a 'width' parameter, with start dates randomized across a century, and a depth parameter is passed to the query for the number of days group limit via a system context.

The new 12c feature MATCH_RECOGNIZE is a very powerful technique, and was much faster than the other techniques for this problem

The results above showed that recursive subquery factoring had timings that increased quadratically with number of records; this was due to a product between the number of starts and full scans on a subquery

This kind of unscaleable quadratic resource usage can often be avoided by the use of a temporary table with appropriate indexes, as demonstrated

The depth parameter had little effect on timing, but I included it for the purpose of demonstration of the benchmarking framework