Model clause for Oracle SQL

Here's the Model clause syntax:

select ....
from ....

model [main] 
   [ reference models ] 
   [ partition by (<cols>)] 
   dimension by (<cols>) 
   measures (<cols>)
    [ ignore nav ] | [ keep nav ]
  [ rules
    [ upsert | update]
    [ automatic order | sequential order ]
    [ iterate (n) [ until <condition>] 
  ]
    (   <cell_assignment> = <expression> ...)

Mapping of columns

When the model clause is used in a select statement, each of the columns selected must be mapped to either the partition, the dimension or the measure group.

Consider the following table:

create table sales (
  ctry  varchar2(20),
  prod  varchar2(20),
  yr    number  (4),
  qty   number  (5)
);

Now, we execute a model clause sql statement on that table where not all columns are mapped. As the rules are not important for this demonstration, none is specified.

select 
  ctry, prod, yr, qty 
from 
  sales
model
  partition by (prod)
  dimension by (yr)
  measures     (qty)
  rules (
  );

As can be seen, of the four selected columns (ctry, prod, yr and qty), only three have been mapped, ctry is not mapped. Promptly will Oracle answer with

ctry, prod, yr, qty
  *
  ERROR at line 2:
  ORA-32614: illegal MODEL SELECT expression

This error message is thrown because ctry is not mapped. It does not matter where it is going to be mapped, as long as it is mapped. For the example's sake, it is mapped into the partition group:

select 
  ctry, prod, yr, qty 
from 
  sales
model
  partition by (ctry, prod)
  dimension by (yr)
  measures     (qty)
  rules (
  );

Now, the query, albeit useless, doesn't throw an error anymore.

Partition columns

Partition columns divide the result set into blocks. Rules defined in the model clause are applied independently of other partitions to each partition.

Dimension columns

Dimension columns define how cells within a partition can be accessed.

Measure columns

The columns defined as measures can be assigned new values in the rules section of the model clause.

The sales table has only one dimension (yr). So, the measures (here: qty) is accessed like so (in the rules section):

qty[yr]

Examples

Forecast

Generating dates


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback