Posts

Showing posts from November, 2016

SQL Pattern Matching Deep Dive - Part 5, SKIP TO where exactly?

Image
Image courtesy of flicker.comSo far in this series we looked at how to ensure query consistency, how correctly use predicates, managing sorting, using the built-in measures to help with optimise your code and the impact of different types of quantifiers:SQL Pattern Matching deep dive - Part 1SQL Pattern Matching Deep Dive - Part 2, using MATCH_NUMBER() and CLASSIFIER()SQL Pattern Matching Deep Dive - Part 3, greedy vs. reluctant quantifiersSQL Pattern Matching Deep Dive - Part 4, Empty matches and unmatched rows?In this post I am going to review what MATCH_RECOGNIZE does after a match has been found i.e. where the search begins for the next match. It might seem obvious, i.e. you start at the next record, but MATCH_RECOGNIZE provides a lot of flexibility in this specific area (as you would expect).Basic SyntaxWe use the AFTER MATCH SKIP clause to determine the precise point to resume row pattern matching after a non-empty match is found. If you don’t supply an AFTER MATCH SKIP clause t…