Breadcrumb

Text alternative to video

The new groundbreaking SQL pattern matching capabilities brings the simplicity and efficiency of the most common data analysis language to the process of identifying patterns within a data set. It provides a completely new native SQL syntax that has adopted the regular expression capabilities of Perl by implementing a core set of rules to define patterns in sequences (streams of rows). In this first session will explore some of the main concepts and key words of the new syntax. Subsequent sessions will explore typical use cases for SQL pattern matching and how to debug your pattern matching process.

 

Recognizing patterns in a sequence of rows has been a capability that was widely desired, but not possible with SQL until now. There were many workarounds, but these were difficult to write, hard to understand, and inefficient to execute. With Oracle Database 12c Release 1 (12.1), you can use the MATCH_RECOGNIZE clause to perform pattern matching in SQL to do the following:

1. Logically partition and order the data that is used in the MATCH_RECOGNIZE clause with its PARTITION BY and ORDER BY clauses.
2. Define patterns of rows to seek using the PATTERN clause of the MATCH_RECOGNIZE clause. These patterns use regular expressions syntax, a powerful and expressive feature, applied to the pattern variables you define.
3. Specify the logical conditions required to map a row to a row pattern variable in the DEFINE clause.
4. Define output measures, which are expressions usable in the MEASURES clause of the SQL query.
5. Control the output (summary vs. detailed) from the pattern matching process

Recognizing patterns in a sequence of rows has been a capability that was widely desired, but not possible with SQL until now. There were many workarounds, but these were difficult to write, hard to understand, and inefficient to execute. With Oracle Database 12c Release 1 (12.1), you can use the MATCH_RECOGNIZE clause to perform pattern matching in SQL to do the following:

1. Logically partition and order the data that is used in the MATCH_RECOGNIZE clause with its PARTITION BY and ORDER BY clauses.
2. Define patterns of rows to seek using the PATTERN clause of the MATCH_RECOGNIZE clause. These patterns use regular expressions syntax, a powerful and expressive feature, applied to the pattern variables you define.
3. Specify the logical conditions required to map a row to a row pattern variable in the DEFINE clause.
4. Define output measures, which are expressions usable in the MEASURES clause of the SQL query.
5. Control the output (summary vs. detailed) from the pattern matching process

Learn how to use SQL for Pattern Matching. Row pattern matching in native SQL improve application and development productivity and query efficiency for row-sequence analysis.Recognizing patterns in a sequence of rows has been a capability that was widely desired, but not possible with SQL until now. With Oracle Database 12c Release 1 (12.1), you can use the MATCH_RECOGNIZE clause to perform pattern matching in SQL.

In this clip from OOW 2013, Oracle VP Andy Mendelsohn uses SQL Pattern Matching to detect fraudulent credit-card activity

Oracle Product Manager, Keith Laker, demonstrates how to work with SQL Pattern Matching in Oracle Database 12c in this second of three demos. See Keith's blog for more information.

Oracle Product Manager, Keith Laker, demonstrates how to work with SQL Pattern Matching in Oracle Database 12c in this third of three demos. See Keith's blog for more information.

Oracle Product Manager, Keith Laker, demonstrates how to work with SQL Pattern Matching in Oracle Database 12c in this first of three demos. See Keith's blog for more information.

In this OBE, you learn how to use  SQL for Pattern Matching. Row pattern matching in native SQL improves application and development productivity and query efficiency for row-sequence analysis.

This series features an OBE and a recorded webcast. Learn how to use  SQL for Pattern Matching. Row pattern matching in native SQL improves application and development productivity and query efficiency for row-sequence analysis.

You can group rows into five-minute intervals using row pattern matching (match_recognize).There are many different ways to define these groups. Ensure the variable definitions match the requirements!

When you want to find out if a given pattern exists within a group of rows or not, you have to be careful about your pattern definition if you want to show all your groups no matter if they have the pattern or not.

Introduced in 12.1, the MATCH_RECOGNIZE clause offers a powerful tool for detecting and reporting on patterns in your data.