MATCH_RECOGNIZE for Row Pattern Recognition and Event-Sequence Analysis

Learning outcomes

ServiceHub collects machine-state events. Operations wants to flag “three or more rising-temperature observations ending in an ALERT” and measure how changing the restart point after a match affects alerts found on an overlapping timeline. This is a sequence problem, not merely an aggregate problem.

1. A pattern match starts with a stable timeline

PARTITION BY machine_id prevents one machine’s events from matching another’s. ORDER BY event_ts, event_id gives each partition a total order. Oracle documentation warns that omitting row-pattern ordering makes results nondeterministic; an incomplete order with tied timestamps can create the same practical problem.

SELECT machine_id, event_ts, event_id, state_code, temperatureFROM servicehub_sql_machine_eventORDER BY machine_id, event_ts, event_id;

2. PATTERN names the shape; DEFINE gives variables meaning

The following pattern starts with any row A, then requires two or more rows B whose temperature is higher than the previous row, and finally an ALERT row C. The quantifier {2,} means two or more B rows.

SELECT *FROM servicehub_sql_machine_eventMATCH_RECOGNIZE (  PARTITION BY machine_id  ORDER BY event_ts, event_id  MEASURES    FIRST(A.event_ts) AS start_ts,    LAST(C.event_ts) AS alert_ts,    FIRST(A.temperature) AS start_temp,    LAST(C.temperature) AS alert_temp,    COUNT(B.*) AS rising_steps,    MATCH_NUMBER() AS match_no  ONE ROW PER MATCH  AFTER MATCH SKIP PAST LAST ROW  PATTERN (A B{2,} C)  DEFINE    B AS B.temperature > PREV(B.temperature),    C AS C.state_code = 'ALERT')ORDER BY machine_id, match_no;

DEFINE is evaluated with running semantics. PREV navigates relative to the current pattern evaluation; it is not a generic table lag detached from the match.

3. MEASURES turns a matched row sequence into explainable evidence

With ONE ROW PER MATCH, each match becomes one summary row. Measures such as first/last timestamps, temperatures, MATCH_NUMBER(), and counts make the match auditable. With ALL ROWS PER MATCH, Oracle can emit one row for each row participating in a match, useful for explaining which events were classified as A, B, or C.

SELECT machine_id, event_ts, event_id, state_code, temperature,       classifier, match_noFROM servicehub_sql_machine_eventMATCH_RECOGNIZE (  PARTITION BY machine_id  ORDER BY event_ts, event_id  MEASURES    CLASSIFIER() AS classifier,    MATCH_NUMBER() AS match_no  ALL ROWS PER MATCH  PATTERN (A B+)  DEFINE B AS B.temperature > PREV(B.temperature))ORDER BY machine_id, match_no, event_ts, event_id;

4. AFTER MATCH SKIP changes which later matches are eligible

The default SKIP PAST LAST ROW resumes after the last row of a match, preventing overlap with rows already consumed. SKIP TO NEXT ROW resumes after the first row of the current match, allowing more overlapping candidate matches. That can increase match count dramatically.

-- Non-overlapping restart... AFTER MATCH SKIP PAST LAST ROW    PATTERN (A B+)    DEFINE B AS B.temperature > PREV(B.temperature)-- Overlap allowed from the next input row... AFTER MATCH SKIP TO NEXT ROW    PATTERN (A B+)    DEFINE B AS B.temperature > PREV(B.temperature)

The abbreviated blocks show only the changed clause; use the full lab queries below for execution. Match-skip behavior is part of the business definition, not a tuning switch.

5. Deliberately wrong: order only by a timestamp that can tie

If two events for one machine share the same timestamp and the pattern order specifies only event_ts, their relative order is not fully defined. A rising sequence can appear or disappear depending on which tied row is considered first. The fix is a deterministic event key such as event_id in the row-pattern ORDER BY.

-- Incomplete order when timestamps can tie:ORDER BY event_ts-- Deterministic order:ORDER BY event_ts, event_id

This is a correctness repair. Adding the key solely to the final query ORDER BY is too late because matching has already happened.

6. Hands-on lab: build an overlapping timeline

DROP TABLE servicehub_sql_machine_event IF EXISTS PURGE;CREATE TABLE servicehub_sql_machine_event (  event_id NUMBER PRIMARY KEY,  machine_id NUMBER NOT NULL,  event_ts TIMESTAMP NOT NULL,  state_code VARCHAR2(20) NOT NULL,  temperature NUMBER(6,2) NOT NULL);INSERT INTO servicehub_sql_machine_event VALUES (1,10,TIMESTAMP '2026-08-25 08:00:00','RUN',60);INSERT INTO servicehub_sql_machine_event VALUES (2,10,TIMESTAMP '2026-08-25 08:05:00','RUN',62);INSERT INTO servicehub_sql_machine_event VALUES (3,10,TIMESTAMP '2026-08-25 08:10:00','RUN',65);INSERT INTO servicehub_sql_machine_event VALUES (4,10,TIMESTAMP '2026-08-25 08:15:00','ALERT',67);INSERT INTO servicehub_sql_machine_event VALUES (5,10,TIMESTAMP '2026-08-25 08:20:00','RUN',68);INSERT INTO servicehub_sql_machine_event VALUES (6,10,TIMESTAMP '2026-08-25 08:25:00','ALERT',70);INSERT INTO servicehub_sql_machine_event VALUES (7,20,TIMESTAMP '2026-08-25 08:00:00','RUN',55);INSERT INTO servicehub_sql_machine_event VALUES (8,20,TIMESTAMP '2026-08-25 08:05:00','RUN',54);COMMIT;

SELECT machine_id, start_id, end_id, match_noFROM servicehub_sql_machine_eventMATCH_RECOGNIZE (  PARTITION BY machine_id  ORDER BY event_ts, event_id  MEASURES FIRST(event_id) AS start_id,           LAST(event_id) AS end_id,           MATCH_NUMBER() AS match_no  ONE ROW PER MATCH  AFTER MATCH SKIP PAST LAST ROW  PATTERN (A B+)  DEFINE B AS B.temperature > PREV(B.temperature))ORDER BY machine_id, match_no;SELECT machine_id, start_id, end_id, match_noFROM servicehub_sql_machine_eventMATCH_RECOGNIZE (  PARTITION BY machine_id  ORDER BY event_ts, event_id  MEASURES FIRST(event_id) AS start_id,           LAST(event_id) AS end_id,           MATCH_NUMBER() AS match_no  ONE ROW PER MATCH  AFTER MATCH SKIP TO NEXT ROW  PATTERN (A B+)  DEFINE B AS B.temperature > PREV(B.temperature))ORDER BY machine_id, match_no;

DROP TABLE servicehub_sql_machine_event PURGE;

7. Execution evidence and production judgment

MATCH_RECOGNIZE can replace complicated self-joins or procedural scans, but patterns can be difficult to review when quantifiers, navigation, and skip rules become dense. Build a small timeline with hand-verifiable expected matches, include tied timestamps and boundary cases, and assert match counts/measure values before scaling. Use estimated plans with DBMS_XPLAN if helpful, but treat pattern semantics as the primary acceptance criterion.

No paid option, management pack, topology, restart, or COMPATIBLE change is required for this mandatory 26ai lab. Later performance chapters address spill/parallel/resource evidence on larger inputs.

Authoritative references

• SELECT — MATCH_RECOGNIZE — partition/order, MEASURES, PATTERN, DEFINE and AFTER MATCH SKIP semantics
• Data Warehousing Guide — Pattern Matching — row-pattern design, examples, rules and restrictions