Database 12c MATCH_RECOGNIZE: Read all about it

I plan to update this post as new information appears on the Web.


Good grief, I forgot Tom Kyte! He presented MATCH_RECOGNIZE in the November 2013 issue of Oracle Magazine.

In April 2014, Jiang Zhou used MATCH_RECOGNIZE to find bank accounts whose balance decreases 3 months in a row.

In May 2014, Keith Laker announced a new workshop on MATCH_RECOGNIZE in the Oracle Learning Library.

David Allan on July 23d, 2014, explained how to use the MATCH_RECOGNIZE clause in ODI via “SQL override”: ODI 12c – Sessionizing Data with MATCH_RECOGNIZE and SQL Override.


Galo Balda has published his presentation “SQL Pattern Matching in Oracle 12c”, made at RMOUG on February 6th. He shows three different use cases so it’s pretty concrete. One thing I saw for the first time was using the FINAL keyword with something other than LAST.

I forgot Lucas Jellema’s article back in June 2013, which uses original and interesting examples, and his article about “the most valuable player” in July 2013.

In July 2013, André Araújo of Pythian blogged about analyzing archived log history.

Vijay at has three articles on SQL pattern matching, the most recent in January 2014 >


The MATCH_RECOGNIZE clause is documented here >
SQL Language Reference: row_pattern_clause

and explained here >
Data Warehousing Guide: SQL for Pattern Matching

There is also a White paper on >
SQL Pattern Matching: Patterns Everywhere – Find then fast!

The number one source of information is Keith Laker. His post “Sessionization with 12c SQL pattern matching is super fast” starts with a list of everything he has published so far, and it continues with a concrete example. Keith presented a session in the Virtual Developer Day in February and early March.

There is also a video from the Oracle Learning Library on YouTube >

Hermann Baer made a very good presentation at the NOCOUG conference in February 2014 > SQL the Best Analysis Language for Big Data.

If you search for MATCH_RECOGNIZE on or the Oracle forums, you will find a few examples (from me).

Warning: if you search the Oracle forums (or Google!), be aware that MATCH_RECOGNIZE is also in another Oracle Product: “Complex Event Processing” or CEP. This has nothing to do with the database version, and the syntax is not the same.

Up next: MATCH_RECOGNIZE compared to the Tabibitosan method


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s