Subscribe to this blog

Follow by Email

MATCH_RECOGNIZE: Can I use MATCH_NUMBER() as a filter?

Recently I spotted a post on OTN that asked the question: Can MATCH_RECOGNIZE skip out of partition? This requires a bit more detail because it raises all sorts of additional questions. Fortunately the post included more information which went something like this:

after a match is found I would like match_recognize to stop searching - I want at most one match per partition. I don’t want to filter by MATCH_NUMBER() in an outer query - that is too wasteful (or, in some cases, I may know in advance that there is at most one match per partition, and I don’t want match_recognize to waste time searching for more matches which I know don't exist).

Can MATCH_RECOGNIZE do this? Short answer is: NO.
Long answer is: Still NO.

Going back to the original question… you could interpret it as asking “is it possible to only return the first match”? The answer to this question is YES, it is possible.

There are a couple of different ways of doing it. Let’s use our good old “TICKER” data set. The point of this exercise is to simply show that there are different ways to achieve the same result but in the end you need to look beyond what is passed back from MATCH_RECOGNIZE to understand what is going on as we process the rows from our TICKER table…

In simple terms, I only want my query to return the first match. Here is my starting query:

BUT have we saved any processing? That is to say: did MATCH_RECOGNIZE stop searching for matches after the first match was found? NO! Checking the explain plan we can see that all 60 rows from our table where processed:

Anyway the original post pointed out that simply filtering was not what they wanted so we can discount using MATCH_NUMBER within the WHERE clause. Although it does sort of achieve the result we wanted.

Let’s try an alternative approach. Can we limit the number of rows that are processed by using the exclude syntax within the PATTERN clause?

I have added another pattern variable “c” but made it always true by not providing a definition within the DEFINE clause.

This is getting close to what we might need because now we have only one match for each symbol. Therefore, if we now use the exclude syntax around the pattern variable c we should be able to remove all matches except the first!

which does in fact return exactly the same result as our second query where we applied a filter on the column MATCH_NUMBER:

but if we check the explain plan we can see that yet again all 60 rows were processed.

Therefore, we have got the right result but we have not been able to actually halt the MATCH_RECOGNIZE processing after the first match has been found.

Returning only the 2nd match

What if we wanted to return only the 2nd match? Well for this use case the exclude syntax is not going to work. The only viable solution in this situation would be to using the match_number column and apply a filter to find the required match. However, all rows from input table will be processed!

…and the final answer is: Enhancement Request

Let’s start with the simple answer to our original problem: after a match is found I would like match_recognize to stop searching
Alas, there is definitely no way to stop MATCH_RECOGNIZE processing all the rows passed to it. To make this happen we would need to extend the AFTER MATCH SKIP TO syntax to include phrases that let us call a halt to the pattern matching process. What we need is something like “AFTER MATCH SKIP TO END”, however, this assumes that only the first match is important.

What if you wanted the first and the second or maybe it’s the second match that’s of most interest. What we really need then is something like the following: “AFTER MATCH ’N’ SKIP TO END” where ’N’ indicates the maximum number of matches that you want to process before jumping to the end of the partition.

Assuming I can find enough valid use cases I will put this on the “enhancement” list for MATCH_RECOGNIZE. If you some great use cases for this scenario then please send me the details (keith.laker@oracle.com).

Comments

Post a Comment

Popular posts from this blog

This post covers one of the new SQL performance enhancements that we incorporated into Database 12c Release 2. All of these enhancements are completely automatic, i.e. transparent to the calling app/developer code/query. These features are enabled by default because who doesn’t want their queries running faster with zero code changes?

So in this post I am going to focus on the new In-Memory “cursor duration” temporary table feature. Let’s start by looking at cursor duration temp tables…Above image courtesy of wikimedia.org
What is a cursor duration temp table?
This is a feature that has been around for quite a long time. Cursor duration temporary tables (CDTs) are used to materialize intermediate results of a query to improve performance or to support complex multi step query execution. The following types of queries commonly use cursor duration temp tables:WITH Clause and parallel recursive WITHGrouping SetsStar TransformationFrequent Item Set CountingXLATE
What happens during the …

….and now it’s here in PDF format as well!The free big data warehousing Must-See guide for OpenWorld 2017 is now available for download in PDF format - click here, and yes it’s completely free. This comprehensive guide covers everything you need to know about this year’s Oracle OpenWorld conference so that when you arrive at Moscone Conference Center you are ready to get the most out of this amazing conference. The guide contains the following information:Page 8 - On-Demand VideosPage 17 - JustifyYour tripPage 19 - KeyPresentersPage 41 - Must See SessionsPage 90 - Useful MapsChapter 1 - Introduction to the must-see guide.Chapter 2 - A guide to the key the highlights from last year’s conference so you can relive the experience or see what you missed. Catch the most important highlights from last year's OpenWorld conference with our on demand video service which covers all the major keynote sessions. Sit back and enjoy the highlights. The second section explains why you need to atte…

MATCH_RECOGNIZE and predicates
At a recent user conference I had a question about when and how predicates are applied when using MATCH_RECOGNIZE so that’s the purpose of this blog post. Will this post cover everything you will ever need to know for this topic? Probably!
Where to start….the first thing to remember is that the table listed in the FROM clause of your SELECT statement acts as the input into the MATCH_RECOGNIZE pattern matching process and this raises the question about how and where are predicates actually applied. I briefly touched on this topic in part 1 of my deep dive series on MATCH_RECOGNIZE: SQL Pattern Matching Deep Dive - Part 1.
In that first post I looked at the position of predicates within the explain plan and their impact on sorting. In this post I am going to use the built in measures (MATCH_NUMBER and CLASSIFIER) to show the impact of applying predicates to the results that are returned.
First, if you need a quick refresher course in how to use the MATCH…

Keith Laker

Keith Laker

Disclaimer

Opinions expressed are entirely my own and do not reflect the position of Oracle or any other corporation. Do NOT take anything written here, unless explicitly mentioned otherwise, to be Oracle policy or reflecting Oracle's support policy.

About Me

I have been working with Oracle data warehouse technology for over 20 years working on a wide variety of data warehouse projects both as a consultant and an onsite support engineer. I am now part of the Data Warehouse Product Management Team where I am responsible for analytical SQL. I am based in the UK at our Manchester office.

A key part of my role is to work with our sales teams to brief our customers on data warehousing and analytical SQL: explaining the wide variety of new and exciting opportunities that our DW and analytical solutions can support.

I regularly deliver sales training for data warehousing and analytical SQL across all our sales regions and provide competitive intelligence support across all the major data warehouse vendors.