Kim Berg Hansen on “Use Cases of Row Pattern Matching in Oracle 12c”

As I write this, I am listening to Kim Berg Hansen explain the MATCH_RECOGNIZE clause. He was kind enough to give me credit for some of the examples and mention this blog.

In addition to my blog posts on the subject, you may enjoy my presentation on SlideShare. Please download it to see the animations!

If you have questions arising from the presentation, please add a comment here.

Bravo to Kim for his remarkably interactive webinar!


4 thoughts on “Kim Berg Hansen on “Use Cases of Row Pattern Matching in Oracle 12c”

  1. I used match_recognize recently to find procedure calls that use a particular parameter, a use I haven’t otherwise come across. It’s really quite handy.

    SELECT *
    FROM dba_source MATCH_RECOGNIZE(
    PARTITION BY owner, name, type
    ORDER BY line
    MEASURES NVL(proc.line, both.line) AS proc_line,
    NVL(proc.text, both.text) AS proc_text,
    param.line AS param_line,
    param.text AS param_text
    PATTERN (proc anything{0, 20} param | both)
    DEFINE proc AS LOWER(text) LIKE ‘%some_procedure%’,
    param AS LOWER(text) LIKE ‘%p_parameter%’,
    both AS LOWER(text) LIKE ‘%some_procedure%(%p_parameter%’)
    WHERE (owner, name) IN (SELECT /*+ MATERIALIZE */
    owner, name
    FROM dba_dependencies
    WHERE referenced_owner = ‘THE_OWNER’
    AND referenced_name = ‘SOME_PROCEDURE’)
    ORDER BY owner, name, proc_line;

    • I admit I don’t yet understand MATCH_RECOGNIZE, so my understanding of your query might be incorrect; but it looks like you might be able to accomplish the same thing with a simple query against DBA_ARGUMENTS

  2. DBA_ARGUMENTS will tell me the parameters for a given API. In my case I was interested in finding the client code that uses a specific, optional parameter for a specific procedure (or equivalently a function).

    1. DEFINE “proc” to match any line containing the procedure of interest
    2. “param” matches the parameter name
    3. “both” matches the procedure & parameter ON THE SAME LINE

    Then the full PATTERN is:
    1. the proc, followed by 0-20 other lines, followed by the param
    2. OR both: the proc & param on the same line

    N.B. Because I do not defined “anything” it matches any line. I could have used “applesauce” equivalently. This is useful, but not well documented. Maybe I should have been more explicit with something like: “DEFINE anything AS 1=1”.

    • Thanks for the clarification. I’ve been reading up on Match_recognize and I have to say your examples are the best I’ve found on the internet so far!
      By the way – one more question, hows the performance difference of the way you’ve done it with the WHERE clause after the MATCH_RECOGNIZE vs putting the where clause in a subselect in the FROM, before the MATCH_RECOGNIZE?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s