Pattern Matching with Oracle Database 12c

By -
Now that Oracle Database 12c has launched you will see that we have updated the SQL Analytics home page on OTN, which is here: http://www.oracle.com/technetwork/middleware/bi-foundation/index-092760.html.

One of the new things listed on our OTN home page is a very exciting feature headlined "pattern matching". Those of you who are using Warehouse Builder will remember the regular expression syntax that was added into the Paris 10g release and you are probably thinking that "hey, I thought Oracle Database already did pattern matching". Of course, you are quite correct. Prior to Oracle Database 12c you could search for patterns within a specific row of data using all sorts of techniques. However, this new pattern matching feature is something is really quite special.

The Oracle Database 12c adds 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). This new inter-row pattern search capability complements the already existing capabilities of regular expressions that match patterns within character strings of a single record.

The 12c MATCH_RECOGNIZE feature allows the definition of patterns, in terms of characters or sets of characters, and provides the ability to search for those patterns across row boundaries. It offers significant gains in term of performance, maintainability and scalability compared to the continued use of bespoke code embedded within applications and executed either on the client side or within the middle-tier application server.

For business users and data scientists the key advantage of in-database pattern matching is that it allows them to incorporate pattern enrichment into their existing analytical workflows without having to resort to complex externally coded routines that increases network traffic and increase data latency. This whitepaper will explore this new SQL functionality and explain how different industries can leverage the full richness of Oracle’s new SQL pattern matching capabilities.
Moving forward developers really think seriously about switching their pattern matching logic to the new SQL-based pattern matching features offered by Oracle Database 12c so they can benefit from simplified code, more efficient processing and improved performance.

I have posted some articles and presentations on the updated SQL Analytics page on OTN.  When you visit the updated OTN page look for the section marked Technical Information . Here you will see links to a whitepaper and presentation from OpenWorld 2012 that will help you to understand this great new feature:

red-arrow-box SQL Pattern Matching: Patterns Everywhere - Find then fast! 
red-arrow-box Analyze This! Analytical Power in SQL, More Than You Ever Dreamed Of (OOW 2012)
 In the section marked Training you see a list of three podcasts:
red-arrow-box Podcasts: SQL Pattern Matching
Part 1 - Introduction to pattern matching
Part 2 - Concepts, syntax and demonstration
Part 3 - Uses Cases
I will posting three articles on this blog over the next week; one for each podcast (please note that the links on OTN for each podcast will come through to these upcoming blog posts so at the moment the OTN links are not actually working). These will introduce you to the new Database 12c pattern matching feature, explain how to use it and highlight some interesting real world use cases.  And there is a fully interactive Apple iBook edition of the pattern matching whitepaper coming soon! Stay tuned for more information! 
Technorati Tags: , , , ,

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 Star Trans
Keep reading

Oracle OpenWorld - Highlights from Day 2

By -
Image
Data Warehouse and Big Data - Highlights from Oracle OpenWorld Day 2 Welcome to this online review of day two of Oracle OpenWorld 2012. Our aim is to keep you up to date on all the press reports, blog posts, analyst reports and related activities from this year's conference. Oracle: Big Business Has Big Data Woes (New York Times Interview) Business, the way you’re working will kill you. Storage costs, social media awareness, software application migration — they are all big problems. And, not surprisingly, Oracle is trying to address them. Last week I sat down with Mark V. Hurd, Oracle’s co-president, for a talk about Oracle’s strategy. Most of his conversation had to do with the problems facing big companies as they adapt to a world of mobile devices, cloud computing and
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