SQL for Data Warehousing and Analytics

It’s  not long until the doors open at the Moscone Center in San Francisco for the biggest and most exciting tech conference in the calendar. Looking at the online content catalog it’s hard to know where to start because there are so many sessions and just not enough time! Which is here my Must-See Guide comes to the rescue. It lists all the most important data warehouse, big data, analytics, machine learning sessions and labs at #oow18 is now available as a comprehensive Day-by-Day version: My Day-by-Day guide for OpenWorld 2018 is available in both old-fashioned PDF format as a free download from here  and the more useful Apple Book format as a free download   from here   ( open this link in a browser and then click on the blue “View in iBooks” button below the cover-picture ). As usual, all feedback is welcome and if I have missed a session or a hands-on lab that you think should be included in this guide then send me a quick email with the details: keith.lak...

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 and 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...

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 ...

MATCH_RECOGNIZE and predicates At a recent user conference I had a question about when and how  predicates are applied when using MATCH_RECOGNIZE so that’s the purpose of this blog post. Will this post cover everything you will ever need to know for this topic? Probably! Where to start….the first thing to remember is that the table listed in the FROM clause of your SELECT statement acts as the input into the MATCH_RECOGNIZE pattern matching process and this raises the question about how and where are predicates actually applied. I briefly touched on this topic in part 1 of my deep dive series on MATCH_RECOGNIZE: SQL Pattern Matching Deep Dive - Part 1 . In that first post I looked at the position of predicates within the explain plan and their impact on sorting. In this post I am going to use the built in measures (MATCH_NUMBER and CLASSIFIER) to show the impact of applying predicates to the results that are returned. First, if you need a quick refresher course in...

I will be presenting at a number of the Oracle Code events over the coming months on the subject of…..( drum roll please ) SQL pattern matching. Oracle Code is a great series of conferences dedicated to developers who want to get the absolute maximum benefit from using today's cutting edge technologies. If you want to register for any of the dates listed below then follow this link to the registration page . North and Latin America San Francisco ,  March 1, 2017 Austin ,  March 8, 2017 New York City ,  March 21, 2017 Washington DC ,  March 27, 2017 Toronto ,  April 18, 2017 Atlanta June 22, 2017 Sao Paulo , June 27, 2017 Mexico City ,  June 29, 2017 Europe and Middle East London , April 20, 2017 Berlin , April 24, 2017 Prague , April 28, 2017 Moscow , May 22, 2017 Brussels , June 6, 2017 Tel Aviv , July 11, 2017 Asia New Delhi , May ...

Recently I spotted a post on OTN that asked the question:  Can MATCH_RECOGNIZE skip out of partition? This requires a bit more detail because it raises all sorts of additional questions. Fortunately the post included more information which went something like this: after a match is found I would like match_recognize to stop searching - I want at most one match per partition. I don’t want to filter by MATCH_NUMBER() in an outer query - that is too wasteful (or, in some cases, I may know in advance that there is at most one match per partition, and I don’t want match_recognize to waste time searching for more matches which I know don't exist). Can MATCH_RECOGNIZE do this? Short answer is:  NO . Long answer is: Still NO . Going back to the original question… you could interpret it as asking “is it possible to only return the first match”? The answer to this question is YES, it is possible. There are a couple of different ways of doing it. Let’s use our good old “T...

Image courtesy of wikipedia This post is the result of reviewing a post on stackoverflow.com: http://stackoverflow.com/questions/41649178/getting-error-ora-00918-when-using-match-recognize . Here is my version of the code which includes the same issues/errors as the original, however, I am using the TICKER schema table that I always use for the tutorials that I post on liveSQL  : SELECT symbol, tstamp, price FROM ticker MATCH_RECOGNIZE( PARTITION BY symbol ORDER BY symbol, tstamp MEASURES a.symbol AS a_symbol, a.tstamp AS a_date, a.price AS a_price ALL ROWS PER MATCH PATTERN(A B*) DEFINE B AS (price < PREV(price)) ); The above example will not run because of the following error: ORA-00918: column ambiguously defined 00918. 00000 - "column ambiguously defined" *Cause: *Action: Error at Line: 1 Column: 8 So what is wrong with our code? As MATHGUY pointed out in his reply on stackoverflow.com - quite a lot actually! Let’s start by differe...

Image courtesy of flicker.com   So 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 1 SQL Pattern Matching Deep Dive - Part 2, using MATCH_NUMBER() and CLASSIFIER() SQL Pattern Matching Deep Dive - Part 3, greedy vs. reluctant quantifiers SQL 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 Syntax We 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...

image courtesy of flicker: https://c1.staticflickr.com/1/55/185807556_21c547c02e_b.jpg I have been asked a number of times during and after presenting on this topic ( SQL Pattern Matching Deep Dive ) what is the difference between the various matching options such as EMPTY MATCHES and UNMATCHED ROWS . This is the area that I am going to cover in this particular blog post, which is No 4 in this deep dive series . When determining the type of output you want MATCH_RECOGNIZE to return most developers will opt for one of the following: ONE ROW PER MATCH - each match produces one summary row. This is the default. ALL ROWS PER MATCH - a match spanning multiple rows will produce one output row for each row in the match. The default behaviour for MATCH_RECOGNIZE is to return one summary row for each match. In the majority of use cases this is probably the ideal solution. However, there are also many use cases that require more detailed information to be returned. ...