MODEL Clause Concepts, Spreadsheet-Like Calculations, and Appropriate Use Cases

Learning outcomes

ServiceHub finance wants a six-month forecast where months 4–6 are derived sequentially from the prior three months. The calculation looks like a spreadsheet and can be expressed compactly with Oracle’s MODEL clause. The challenge is not making MODEL work; it is maintaining clear cell coordinates, unique dimensions, and rules that future engineers can safely review.

1. From rows to cells

Suppose each ServiceHub row is (region_code, month_no, demand). In MODEL, region_code can partition independent forecasts, month_no can be the dimension coordinate, and demand the measure. By default, each dimension combination should identify a unique cell.

SELECT region_code, month_no, demandFROM servicehub_sql_forecastMODEL  PARTITION BY (region_code)  DIMENSION BY (month_no)  MEASURES (demand)  RULES (    demand[4] = 100  )ORDER BY region_code, month_no;

A rule references cells by dimension. Depending on rule mode, a missing target cell can be created; that is different from updating a cell that exists but contains null.

2. Sequential rules can feed later rules

SELECT region_code, month_no, demandFROM servicehub_sql_forecastMODEL  PARTITION BY (region_code)  DIMENSION BY (month_no)  MEASURES (demand)  RULES UPSERT SEQUENTIAL ORDER (    demand[4] = ROUND((demand[1] + demand[2] + demand[3]) / 3, 2),    demand[5] = ROUND((demand[2] + demand[3] + demand[4]) / 3, 2),    demand[6] = ROUND((demand[3] + demand[4] + demand[5]) / 3, 2)  )ORDER BY region_code, month_no;

SEQUENTIAL ORDER is essential here because month 5 consumes the modeled month 4, and month 6 consumes modeled months 4–5. Make these dependencies obvious in code review rather than relying on clever rule ordering.

3. Deliberately wrong: duplicate dimension coordinates

If the source contains two rows for the same (region_code, month_no), the model has two candidate values for one cell. Under the default unique-dimension semantics Oracle can raise ORA-32638: Non unique addressing in MODEL dimensions.

SELECT region_code, month_no, demandFROM (  SELECT region_code, month_no, demand  FROM servicehub_sql_forecast  UNION ALL  SELECT 'NORTH', 1, 110  FROM dual)MODEL  PARTITION BY (region_code)  DIMENSION BY (month_no)  MEASURES (demand)  RULES (demand[4] = demand[3]);-- ORA-32638: Non unique addressing in MODEL dimensions

The repair is to establish the intended cell grain before MODEL. If multiple fact rows legitimately contribute to one month, aggregate them in an inline view.

SELECT region_code, month_no, demandFROM (  SELECT region_code, month_no, SUM(demand) AS demand  FROM (    SELECT region_code, month_no, demand    FROM servicehub_sql_forecast    UNION ALL    SELECT 'NORTH', 1, 110    FROM dual  )  GROUP BY region_code, month_no)MODEL  PARTITION BY (region_code)  DIMENSION BY (month_no)  MEASURES (demand)  RULES UPSERT SEQUENTIAL ORDER (    demand[4] = ROUND((demand[1]+demand[2]+demand[3])/3,2)  )ORDER BY region_code, month_no;

4. NULL and missing cells are not the same modeling state

A coordinate can exist with a null measure, or the coordinate can be absent entirely. MODEL provides navigation and rule semantics for both cases. Do not use a single NVL habit to erase that distinction unless the business rule really equates “unknown existing value” with “missing cell.”

5. When MODEL is a good fit—and when it is not

MODEL is strong when calculations naturally use multidimensional cell addressing, sequential spreadsheet-like dependencies, what-if rules, or iterative calculations that would otherwise be awkward. It is weaker when a simple window function, join, conditional aggregate, recursive CTE, or procedural step communicates the logic more directly.

6. Hands-on lab

DROP TABLE servicehub_sql_forecast IF EXISTS PURGE;CREATE TABLE servicehub_sql_forecast (  region_code VARCHAR2(20) NOT NULL,  month_no NUMBER NOT NULL,  demand NUMBER(12,2),  CONSTRAINT sh06_forecast_pk PRIMARY KEY (region_code, month_no));INSERT INTO servicehub_sql_forecast VALUES ('NORTH',1,90);INSERT INTO servicehub_sql_forecast VALUES ('NORTH',2,120);INSERT INTO servicehub_sql_forecast VALUES ('NORTH',3,150);INSERT INTO servicehub_sql_forecast VALUES ('SOUTH',1,60);INSERT INTO servicehub_sql_forecast VALUES ('SOUTH',2,75);INSERT INTO servicehub_sql_forecast VALUES ('SOUTH',3,90);COMMIT;

SELECT region_code, month_no, demandFROM servicehub_sql_forecastMODEL  PARTITION BY (region_code)  DIMENSION BY (month_no)  MEASURES (demand)  RULES UPSERT SEQUENTIAL ORDER (    demand[4] = ROUND((demand[1]+demand[2]+demand[3])/3,2),    demand[5] = ROUND((demand[2]+demand[3]+demand[4])/3,2),    demand[6] = ROUND((demand[3]+demand[4]+demand[5])/3,2)  )ORDER BY region_code, month_no;EXPLAIN PLAN SET STATEMENT_ID='SH06L4' FORSELECT region_code, month_no, demandFROM servicehub_sql_forecastMODEL  PARTITION BY (region_code)  DIMENSION BY (month_no)  MEASURES (demand)  RULES UPSERT SEQUENTIAL ORDER (demand[4] = demand[3]);SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,'SH06L4','BASIC'));

DROP TABLE servicehub_sql_forecast PURGE;

7. Production judgment

MODEL logic deserves the same engineering discipline as procedural code: deterministic source grain, named assumptions, tests for missing/null cells, boundary cases, and explainable dependencies. Avoid turning MODEL into a language-within-a-language that only one specialist can modify. Measure memory/TEMP behavior on representative data and retain a simpler reference implementation for critical calculations when practical. No paid option, pack, restart, or COMPATIBLE change is required for the mandatory 26ai example.

Authoritative references

• Data Warehousing Guide — SQL for Modeling — MODEL partitions, dimensions, measures, rules and performance guidance
• SELECT — model_clause — MODEL grammar and rule semantics