Projections, budgets, and what-if calculations are often done in a spreadsheet because each cell can be computed from other cells by a formula. Oracle's MODEL clause brings that way of working into SQL. It treats a query result as an array of cells, addressed by dimension values, and lets you write rules that compute cells from other cells, including cells that do not exist yet.
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 (source_query) model [partition by (...)] dimension by (...) measures (...) rules [upsert | update] [automatic order | sequential order] [iterate (n) [until (condition)]] ( measure[dimension_values] = expression, ... )
| Part | Role |
|---|---|
| PARTITION BY | Splits the result into independent arrays |
| DIMENSION BY | The columns that address a cell, like row and column labels |
| MEASURES | The columns that hold the cells' values |
| RULES | Formulas that assign cells, referring to other cells by dimension value |
Project Future Months
The source query counts bookings per month of 2026. The model uses the month as its dimension and the count as its measure, and two rules create April and May, each 10% above the month before.
Example:
-- project the next two months of bookings: each month = previous month plus 10%
select month, bookings
from (select to_number(to_char(booked_at, 'YYYYMM')) as month, count(*) as bookings
from bookings where booked_at >= date '2026-01-01'
group by to_number(to_char(booked_at, 'YYYYMM')))
model
dimension by (month)
measures (bookings)
rules (
bookings[202604] = round(bookings[202603] * 1.1),
bookings[202605] = round(bookings[202604] * 1.1)
)
order by month;Output:
MONTH BOOKINGS _________ ___________ 202601 222 202602 154 202603 72 202604 79 202605 87
bookings[202603] reads a cell like a spreadsheet reference. The second rule uses the cell the first rule just created, so the rules chain like formulas.
UPSERT Versus UPDATE
By default, rules run in UPSERT mode: a rule for a cell that does not exist creates it, which is how April and May appeared. In UPDATE mode, rules change existing cells only and silently skip the rest.
Example:
-- RULES UPDATE changes existing cells only: the projected months are not created
select month, bookings
from (select to_number(to_char(booked_at, 'YYYYMM')) as month, count(*) as bookings
from bookings where booked_at >= date '2026-01-01'
group by to_number(to_char(booked_at, 'YYYYMM')))
model
dimension by (month)
measures (bookings)
rules update (
bookings[202603] = bookings[202603] + 10,
bookings[202604] = round(bookings[202603] * 1.1)
)
order by month;Output:
MONTH BOOKINGS _________ ___________ 202601 222 202602 154 202603 82
March was adjusted from 72 to 82, but no April row was created. Use UPDATE when the rules must never add rows.
One Rule for Many Cells with CV and PRESENTV
Several functions work only inside MODEL rules. CV() returns the current value of a dimension on the left side of a rule, so one rule written with FOR ... IN can compute many cells. PRESENTV(cell, expr1, expr2) returns expr1 if the cell existed before the rules ran, and expr2 otherwise.
This model counts tickets per cabin for 2026 and creates 2027 cells for three cabins: existing cabins grow by 20%, and the new PREMIUM cabin, which has no 2026 cell, gets a default of 50.
Example:
select year, cabin, tickets
from (select 2026 as year, cabin, count(*) as tickets from tickets group by cabin)
model
dimension by (year, cabin)
measures (tickets)
rules upsert (
tickets[2027, for cabin in ('BUSINESS', 'ECONOMY', 'PREMIUM')] =
presentv(tickets[2026, cv()], round(tickets[2026, cv()] * 1.2), 50)
)
order by year, cabin;Output:
YEAR CABIN TICKETS _______ ___________ __________ 2026 BUSINESS 270 2026 ECONOMY 799 2027 BUSINESS 324 2027 ECONOMY 959 2027 PREMIUM 50
PRESENTNNV works like PRESENTV but also treats a cell that exists with a NULL value as missing.
Iterate with ITERATION_NUMBER
ITERATE (n) runs the rules n times, and ITERATION_NUMBER returns the current pass, starting at 0. PREVIOUS(cell) returns a cell's value at the start of the current pass, for rules that repeat until a value settles, with ITERATE (n) UNTIL (condition). This example uses the pass number to build a small report per cabin, one measure per pass.
Example:
select cabin, measure, value
from (select cabin, count(*) as tickets, round(avg(fare)) as avg_fare
from tickets group by cabin)
model
partition by (cabin)
dimension by (0 as measure_no)
measures (tickets, avg_fare, cast(null as varchar2(20)) as measure, 0 as value)
rules iterate (3) (
measure[iteration_number] = case iteration_number when 0 then 'TICKETS'
when 1 then 'AVG_FARE' else 'REVENUE' end,
value[iteration_number] = case iteration_number
when 0 then tickets[0]
when 1 then avg_fare[0]
else tickets[0] * avg_fare[0] end
)
order by cabin, measure;Output:
CABIN MEASURE VALUE ___________ ___________ _________ BUSINESS AVG_FARE 2290 BUSINESS REVENUE 618300 BUSINESS TICKETS 270 ECONOMY AVG_FARE 641 ECONOMY REVENUE 512159 ECONOMY TICKETS 799 6 rows selected.
PARTITION BY (cabin) makes each cabin its own array, so the same rules run separately for business and economy.
Things to Know
- A dimension value must address one cell. If DIMENSION BY does not make cells unique within a partition, the query fails, so include enough columns.
- SEQUENTIAL ORDER, the default, runs the rules in the order written. AUTOMATIC ORDER sorts them by their dependencies, so a rule can use a cell that a later rule computes.
- MODEL suits calculations between rows that would otherwise need many self-joins or procedural code. For running totals and moving averages, analytic functions are simpler.
Related Guides
- How to Pivot Rows into Columns with PIVOT in Oracle
- How to Define Window Frames in Oracle Analytic Queries
Conclusion
The MODEL clause turns a query result into arrays of cells, addressed by DIMENSION BY columns and holding MEASURES, and computes cells with spreadsheet-like RULES. UPSERT creates missing cells and UPDATE only changes existing ones, CV and FOR loops let one rule fill many cells, PRESENTV handles cells that may be missing, and ITERATE with ITERATION_NUMBER and PREVIOUS runs rules repeatedly.
