Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c

 

I’ve spent the last couple of evenings playing with the new SQL pattern matching feature in Oracle 12c.

I’m doing some sessions on analytic functions in some upcoming conferences and I thought I should look at this stuff. I’m not really going to include much, if anything, about it as my sessions are focussed on beginners and I don’t really want to scare people off. The idea is to ease people in gently, then let them scare themselves once they are hooked on analytics. 🙂 I’m thinking about Hooked on Monkey Fonics now…

At first glance the pattern matching seems pretty scary. There are a lot of options and as soon as you throw regular expressions into the mix it does make your head swim a little. After a couple of half-baked attempts, where I found convenient excuses to give in when the going got tough, I finally sat down and plugged through the docs. If you actually RTFM it is a lot easier than hoping to wing it. Who’da thunk it? 🙂

I’ve tried to keep the article really light. The docs are pretty good for this stuff (if you read them) and they have a lot of examples. I started adding more and more detail to the article, then chopped most of it out. There is no point regurgitating all the options when it is in the docs. Most of the examples I’ve seen before just talk about basic patterns, like V and W shapes, but it’s quite simple to do complicated stuff once you start playing. In fact it takes more time to set up the example data than it does to figure out the queries to bring it back.

In the near future I will be copy/pasting examples and adjusting them or just sitting with my article and the docs when trying to use this stuff. I think it’s going to take a long time before I can type this stuff from memory. Partly that’s because I can’t see myself having lots of cause to use it. I can’t think of any scenarios I’ve experienced where this would have been a natural fit. Having said that, I’ve never worked in things like stock markets, betting and stuff like that where I can imagine this sort of pattern matching is needed all the time. I seem to remember one person at a conference, who shall remain nameless, saying this feature was one of their drivers for upgrading to 12c. I wonder if that was for real or an exaggeration?

Anyway, if you need this sort of analysis, I think it’s worth checking out, but try to remember it’s not as scary as it first looks. 🙂

Cheers

Tim…

Author: Tim...

DBA, Developer, Author, Trainer.

3 thoughts on “Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c”

  1. “I can’t think of any scenarios I’ve experienced where this would have been a natural fit.”

    It is a simpler alternative to the “Tabibitosan” method and what Timur Akhmadeev calls the “start of group” method; these have many uses pre-12c, especially when uniting date ranges that meet or overlap.

    It can also solve bin fitting problems much faster than the MODEL clause.

    Shameful plug: finding “natural fits” was the theme of my OOW14 presentation. It’s at http://www.slideshare.net/stewashton/row-patternmatching12coow14

    (If you’re interested, please download to see the animations.)

Comments are closed.