Some questions are about sequences of rows: runs of consecutive delayed flights, a price that falls and then recovers, a session of clicks without a gap of more than ten minutes. Writing them with analytic functions and self-joins gets complicated fast. MATCH_RECOGNIZE finds such patterns directly, the way a regular expression finds patterns in text, except that the pattern is made of rows.
Code for This Guide
The main examples are in the examples/pivot-model folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
select ...
from table_or_subquery
match_recognize (
[partition by ...]
order by ...
measures expr as alias [, ...]
{one row | all rows} per match
[after match skip {past last row | to next row | to [first | last] variable}]
pattern (regular_expression_of_variables)
define variable as condition [, ...]
)| Part | Meaning |
|---|---|
| PARTITION BY, ORDER BY | Split the rows into independent sequences and order each one |
| DEFINE | The condition a row must meet to match each pattern variable |
| PATTERN | A regular expression over the variables: +, *, ?, {n,}, |, and grouping |
| MEASURES | The columns computed for each match |
| ONE ROW / ALL ROWS PER MATCH | A summary row per match, or every matched row |
| AFTER MATCH SKIP | Where the search for the next match starts |
Find Runs of Delayed Flights
This query looks at the arrived flights of route 1 in departure order and finds runs of at least two consecutive departures delayed by 15 minutes or more. The variable late is defined by the delay condition, and the pattern late{2,} means two or more late rows in a row.
Example:
-- runs of consecutive delayed departures (15 minutes or more) on route 1
select *
from (select flight_no, scheduled_departure as sched,
extract(hour from (actual_departure - scheduled_departure)) * 60
+ extract(minute from (actual_departure - scheduled_departure)) as delay
from flights
where route_id = 1 and status = 'ARRIVED')
match_recognize (
order by sched
measures first(late.sched) as run_start, count(late.*) as flights,
max(late.delay) as worst_delay
one row per match
pattern (late{2,})
define late as late.delay >= 15
);Output:
RUN_START FLIGHTS WORST_DELAY __________________________________ __________ ______________ 02-JAN-2026 02:05:00 ASIA/DUBAI 3 74 23-JAN-2026 02:05:00 ASIA/DUBAI 2 34 01-FEB-2026 02:05:00 ASIA/DUBAI 3 54 08-FEB-2026 02:05:00 ASIA/DUBAI 2 82
Each output row summarizes one run: FIRST(late.sched) is the first flight of the run, COUNT(late.*) the number of flights in it, and MAX(late.delay) the worst delay. Aggregates in MEASURES work over the rows matched by a variable.
See Every Matched Row
ALL ROWS PER MATCH returns the matched rows themselves, with the measures added. MATCH_NUMBER() numbers the matches and CLASSIFIER() returns the variable each row matched.
Example:
-- every flight of the first two runs, labelled with its match
select *
from (select flight_no, scheduled_departure as sched,
extract(hour from (actual_departure - scheduled_departure)) * 60
+ extract(minute from (actual_departure - scheduled_departure)) as delay
from flights
where route_id = 1 and status = 'ARRIVED')
match_recognize (
order by sched
measures match_number() as run, classifier() as var
all rows per match
pattern (late{2,})
define late as late.delay >= 15
)
where run <= 2
order by sched;Output:
SCHED RUN VAR FLIGHT_NO DELAY __________________________________ ______ _______ ____________ ________ 02-JAN-2026 02:05:00 ASIA/DUBAI 1 LATE NM101 34 03-JAN-2026 02:05:00 ASIA/DUBAI 1 LATE NM101 74 04-JAN-2026 02:05:00 ASIA/DUBAI 1 LATE NM101 19 23-JAN-2026 02:05:00 ASIA/DUBAI 2 LATE NM101 34 24-JAN-2026 02:05:00 ASIA/DUBAI 2 LATE NM101 16
The first run has three late flights and the second two, matching the counts of the summary query.
Find V Shapes
Patterns can combine several variables. This query finds V shapes in weekly bookings: a starting week, one or more weeks of falling bookings, then one or more weeks of rising bookings.
Example:
-- V-shapes in weekly bookings: a fall followed by a rise
select *
from (select trunc(booked_at, 'IW') as week, count(*) as bookings
from bookings group by trunc(booked_at, 'IW'))
match_recognize (
order by week
measures strt.week as from_week, last(down.week) as bottom_week, last(up.week) as to_week,
classifier() as last_step, match_number() as match_no
one row per match
after match skip to last up
pattern (strt down+ up+)
define down as down.bookings < prev(down.bookings),
up as up.bookings > prev(up.bookings)
)
where rownum <= 4;Output:
FROM_WEEK BOTTOM_WEEK TO_WEEK LAST_STEP MATCH_NO ______________ ______________ ______________ ____________ ___________ 17-NOV-2025 01-DEC-2025 15-DEC-2025 UP 1 15-DEC-2025 22-DEC-2025 05-JAN-2026 UP 2 05-JAN-2026 26-JAN-2026 02-FEB-2026 UP 3 02-FEB-2026 02-MAR-2026 09-MAR-2026 UP 4
A few details make it work:
- strt has no definition, so any row matches it; it is the week the fall starts from.
- PREV(down.bookings) reads the previous row, so down matches a week with fewer bookings than the week before, and up one with more.
- LAST(down.week) and LAST(up.week) return the last row matched by each variable: the bottom of the V and its end.
- AFTER MATCH SKIP TO LAST up starts the next search at the last rising week, so the end of one V can be the start of the next, as the from_week of each match shows.
Things to Know
- Quantifiers are greedy by default, matching as many rows as possible; add ? to make one reluctant, as in down+?.
- ORDER BY inside MATCH_RECOGNIZE orders the rows for matching; it does not sort the output. Add an ORDER BY to the query for that.
- Use PARTITION BY to search each group separately, such as each route or each customer, so a pattern never spans two groups.
Related Guides
- How to Define Window Frames in Oracle Analytic Queries
- Oracle SQL Query to Implement Lead/Lag Analysis for Time Series Data
Conclusion
MATCH_RECOGNIZE finds patterns in ordered rows: DEFINE says what each variable matches, PATTERN combines the variables like a regular expression, MEASURES computes the result columns, and ONE ROW or ALL ROWS PER MATCH chooses between summaries and detail. Use PREV, FIRST, LAST, CLASSIFIER, and MATCH_NUMBER to describe each match, and AFTER MATCH SKIP to control overlapping matches.
