|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
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 divide the result set into blocks. Rules defined in the model clause are applied independently of other partitions to each partition.
Dimension columns define how cells within a partition can be accessed.
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):