SQL Pattern Matching Deep Dive - the book

November 27, 2017 | 2 minute read
Keith Laker
Senior Principal Product Manager
Text Size 100%:

Those of you with long memories might just be able to recall a whole series of posts I did on SQL pattern matching which were taken from a deep dive presentation that I prepared for the BIWA User Group Conference. The title of each blog post started with SQL Pattern Matching Deep Dive...and covered a set of 6 posts:

Part 1 - Overview

Part 2 - Using MATCH_NUMBER() and CLASSIFIER()

Part 3 - Greedy vs. reluctant quantifiers

Part 4 - Empty matches and unmatched rows?

Part 5 - SKIP TO where exactly?

Part 6 - State machines

There are a lot of related posts derived from that core set of 6 posts along with other presentations and code samples. One of the challenges, even when searching via Google, was tracking down all the relevant content.

Therefore, I have spent the last 6-9 months converting all my deep dive content into a book - an Apple iBook. I have added a lot of new content based discussions I have had at user conferences, questions posted on the developer SQL forum, discussions with my development team and some new presentations developed for the OracleCode series events.

To make it life easier for everyone I have split the content into two volumes and just in time for Thanksgiving Volume 1 is now available in the iBook Store - it's free to download!

Complete Guide to SQL Pattern Matching  Vol 1

This first volume covers the following topics:

Chapter 1: Introduction/br> Background to the book and explanation of how some of the features with the book are expected to work

Chapter 2: Industry specific use cases In this section we will review a series of uses cases and provide conceptual simplified SQL to solve these business requirements using the new SQL pattern matching functionality.

Chapter 3: Syntax for MATCH_RECOGNIZE The easiest way to explore the syntax of 12c’s new MATCH_RECOGNIZE clause is to look at a simple example...

Chapter 4: How to use built-in measures for debugging In this section I am going to review the two built-in measures that we have provided to help you understand how your data set is mapped to your pattern.

Chapter 5: Patterns and Predicates This chapter looks at how predicates affect the results returned by MATCH_RECOGNIZE.

Chapter 6: Next Steps This final section provides links to additional information relating to SQL pattern matching.

Chapter 7: Credits


My objective is that by the end of this two-part series you will have a good, solid understanding of how MATCH_RECOGNIZE works, how it can be used to simplify your application code and how to test your code to make sure it is working correctly.

In a couple of weeks I will publish information about the contents of Volume 2 and when I hope to have it finished!

As usual, if you have any comments about the contents of the book then let please email me directly at keith.laker@oracle.com

Keith Laker

Senior Principal Product Manager

Product Manager for Autonomous Database and Analytic SQL with extensive experience in data warehouse and business intelligence projects. Worked in various roles, including post-sales consultancy, customer support, and product management at locations across Europe and US.


Previous Post

Using Materialized Views with Big Data SQL to Accelerate Performance

Marty Gubar | 5 min read

Next Post

Advanced Data Protection using Big Data SQL and Database Vault - Introduction

Loic Lefevre | 5 min read
Everything you need to know about data warehousing with the world's leading cloud solution provider