Row Pattern Match in Oracle 12c Standard Edition?

Row Pattern Match – Standard Edition proof


Note: Oracle licensing is a complicated business. The notes here are only a guide. You should always discuss your licensing with Oracle License Management Services or any other third-party company, who are professionals on this business.


The Oracle12c Database has provided the Oracle community with many new features, and one of them is  “the Row Pattern Match“.

In this video by Tom Kyte from December 2013, we can se that he put this feature on the Top 12 features list.

What is Row Pattern Match?

Oracle now provides a completely new native SQL syntax for pattern matching.

It has adopted the regular expression capabilities of Perl by implementing a core set of rules to define patterns in sequences (streams of rows) using SQL.

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.


This is a nice feature, and nice features usually are delivered through some options or management packs, and only to the Enterprise Edition database.

This post asks the question is: “the Row Pattern Match feature Standard Edition Proof? Can I use it?”

You can check this article by Tom Kyte with an example on how to use this feature. Here is the example from that article:

SQL> SELECT *
  2  FROM stocks MATCH_RECOGNIZE
  3  ( PARTITION BY symbol
  4    ORDER BY tstamp
  5    MEASURES
  6       STRT.tstamp AS start_tstamp,
  7       LAST(DOWN.tstamp) AS 
          bottom_tstamp,
  8       LAST(UP.tstamp) AS end_tstamp
  9    ONE ROW PER MATCH
 10    AFTER MATCH SKIP TO LAST UP
 11    PATTERN (STRT DOWN+ UP+)
 12    DEFINE
 13      DOWN AS 
         DOWN.price < PREV(DOWN.price),
 14      UP AS UP.price > PREV(UP.price)
 15  ) MR
 16   ORDER BY MR.symbol, 
               MR.start_tstamp;

SYMBOL  START_TST BOTTOM_TS END_TSTAM
——————  ————————— ————————— —————————
XYZ     01-SEP-12 03-SEP-12 07-SEP-12
XYZ     07-SEP-12 10-SEP-12 13-SEP-12

Question: PARTITION – so I cannot use the “Row Pattern Matching”-feature?  Or is this referring to something else and not the Oracle Partitioning context?

By checking the Oracle12c License Document and the Enterprise Manager License documents, I cannot find anything referring to the keywords. There is though one row on Oracle Advanced Analytics (=OAA) option, and a second row on Oracle On-Line Analytic Processing (OLAP). Both options are only available in an Enterprise Edition Database.

  1. Oracle OTN site on OLAP, you can find this video explaining the features of OLAP. After looking at this video, OLAP option can to my understanding, make use of any available Analytic Functions as well as basic SQL commands together with its own Cube/OLAP specific “language”, so I am tempted to assume that “Row Pattern Matching” is not  part of the OLAP option.
  2. Oracle OTN site on Oracle Advanced Analytics 

o_advanced_analyze

“Analyze “market baskets” to discover associations, PATTERNS and relationships”

Can it be so, that “Row Pattern Matching” is part of OAA option?


Reaching out for help from an Advanced Analytic Expert

Since the documentations is not very straightforward on answering  my question, (or maybe its the fact that English is not my mother tongue), I thought I better reach out to one of my peers, Brendan Tierney – who is an expert on Advanced Analytics, and see what kind of thoughts he might have about this new feature:

“As far as I am aware of, the Row Pattern Matching is part of the suite of Analytic Function in Oracle, and as far as I know, it is not part of the Advanced Analytics Option.  It has never been mentioned or associated with Oracle Advanced Analytics.”

Promising….. How about PARTITION?

“The  PARTITION clause is a special feature of the Analytics Functions that allows for the processing/analytics to be performed on different groupings of attributes and their values.”

This is also verified in the above Analytic Function document (Note that the term “partitions” used within the context of analytic functions is unrelated to Oracle Partitioning.)

BTW: Brendan is going to speak about “Predictive Analytics”,  “Data Miner”, and”Predictive Queries” at OUGE Harmony15 Conference in Tallinn. Why not join?

Thanks for sharing your knowledge, and helping me out!


Summary

How cool would it be to find a link within the Oracle License Documentation to a  list on for example the OAA options all packages, functions, tables, views, procedures etc that it is using – It would give a very quick answer to Standard Edition specific questions. Now finding an answer to the question “SE Proof?”, can be pretty time consuming, because the information is scattered around. Or a Standard Edition Spokesperson, to whom you could send your question(s) to? Wouldn’t that also be pretty cool?

The “Row Pattern Matching” feature seems to be part of the general Analytic Functions Suite that seems to be a standard functionality of the Oracle database,  and I couldn’t find any text referring to the Enterprise Edition options mentioned above.  I am tempted to say “yes, it’s SE proof”

What’s your thoughts? Is this new feature Standard Edition Proof or not?

June: Message from an Oracle License Expert: Yes it is Standard Edition Proof.


Take care (also remember your data), stay well and lets enjoy the long and bright Summer Nights of Scandinavia!

– Ann

Turku 1st June 2015

2 thoughts on “Row Pattern Match in Oracle 12c Standard Edition?”

  1. The “partitioning” in Analytic has nothing to do with “the Partitioning Option”. The latter will physically distribute/group rows for faster/smaller access. The former is just a “grouping” function for calculating the analytic function.

    (Usual disclaimers apply)

Leave a Reply

Your email address will not be published. Required fields are marked *