SQL Pattern Matching Deep Dive - the book

By -
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:


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!

 UPDATE: this book is now available in PDF format but unfortunately  most of the really nice iBook features are turned off :-(

 Complete Guide to SQL Pattern Matching Vol 1

This first volume covers the following topics:
  • Chapter 1: Introduction 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
Location: San Francisco, CA, USA

Comments

Post a Comment

Popular posts from this blog

My query just got faster - brief introduction to 12.2 in-memory cursor duration temp tables

By -
Image
Image courtesy of wikipedia This post covers one of the new SQL performance enhancements that we incorporated into Database 12c Release 2 . All of these enhancements are completely automatic, i.e. transparent to the calling app/developer code/query. These features are enabled by default because who doesn’t want their queries running faster with zero code changes? So in this post I am going to focus on the new In-Memory “cursor duration” temporary table  feature. Let’s start by looking at cursor duration temp tables… Above image courtesy of wikimedia.org What is a cursor duration temp table? This is a feature that has been around for quite a long time. Cursor duration temporary tables (CDTs) are used to materialize intermediate results of a query to improve performance or to support complex multi step query execution. The following types of queries commonly use cursor duration temp tables: WITH Clause and parallel recursive WITH Grouping Sets S...
Keep reading

SQL Pattern Matching Deep Dive - Part 1

By -
Image
There has been quite a lot of interest in the new 12c MATCH_RECOGNIZE feature for SQL pattern matching. Therefore, I thought now would be the perfect time to start a series of quick deep dive posts that explain how SQL pattern matching works. Over the coming weeks I will cover the following topics in a series of posts. This is the start of a series of posts based on a presentation that I put together for the recent annual  BIWA conferenc e at Oracle HQ. The Oracle BI, DW and Analytics user community always puts on a great conference and this year was the best yet. You can download any or all of the presentations from this year’s conference by following this link . My pattern matching deep dive presentation started life about a year ago as a post covering some of the new keywords in the explain plan that are linked to pattern matching, see here . It has now expanded to cover a much wider range of topics. Firstly, a huge vote of thanks to my development team (Lei Sheng and Chun-Chie...
Keep reading

SQL Pattern Matching Deep Dive - Part 6, state machines

By -
Image
The obvious way to start this particular post is to pose a couple of simple questions: what is a state machine and why should you care? In general I would say that you don't need to know about or care about state machines. That's the beauty of using SQL for pattern matching. The MATCH_RECOGNIZE clause encapsulates all the deep technical modelling and processing that has to be performed to run pattern matching on a data set. However, there are times when it is useful, probably vital, that you understand what is going on behind the scenes and one of the most obvious situations is when backtracking happens. Therefore, the content covered in this post is a going to be a gently lead-in into my next post where I am going to discuss the concept of “backtracking”  and the dreaded  ORA-30009 error. Let’s start our voyage of discovery…when you attempt to run a SQL statement containing a MATCH_RECOGNIZE clause during the compilation phase we generate a finite state machine ba...
Keep reading