The Oracle MODEL clause (part I)
These are my last weeks. If the doctors can reduce the pain I'll still get to september. Pity, but the Delphi ACE level is out of reach.
Published:
Browse All Articles > The Oracle MODEL clause (part I)
I'm working on giving a course in Oracle advanced SQL. One of the topics is the MODEL clause. I needed documentation to explain this clause, so I made an article for this.
This clause allows you to use your data like an array, do calculations on that array and then return the calculated data. This sounds a lot like working with a spreadsheet.
In this part I I'll be looking at the model clause with a single dimension.
![query-block.gif]()
The model clause itself has the MODEL keyword, three optional parts and the main model section:
![main-model.gif]()
The main model section has two mandatory sections: the model column clause and the model rules clause:
![main-model.gif]()
The model columns clauses has this definition:
![model-column-clauses.gif]()
and the model rules clause is like so :
![model-rules-clause.gif]()
When trying to understand a new clause, I always try to find the smallest piece of code that works and then gradually add to that. I'll use the hr.employees table from the samples. For testing i create the table in my TEST schema. That way I don't have to worry about foreign keys, triggers, etc. ...
To ease copy and paste, I'll put the SELECT statements on a new line and also remove the line count number. This helps copying when a statement spans multiple lines. The test data looks like this:
The smallest SELECT with a working model clause shows that within the model rules clause the cell assignment is also optional. The image of the model rules clause doesn't reflect this!
This is rather odd, why would you use the model clause if you don't actually use model ... ??? In this SELECT, no calculations are done, so we have the minimal clause to work with: an employee_id and the salary column
I'll limit the data to 10 rows from here-on for readability. (That's a new feature of 12c: you can fetch first 10 rows only.)
The salary for employee_id 100 is changed from 24000 to 25000. The table data itself is not changed.
To do this with the model we need to figure out how addressing cells and calculations work.
Calculations are done in the RULES section. The RULES keyword is optional. In our minimal statement it's cut down to two parentheses : " () ". With the keyword in place, it's "RULES () ", which makes identifying the RULES section easier. I'll leave the RULES keyword in place to make identification easier.
The cell assignment (the non-optional part that seemed to be optional) is defined like this
![cell-assignment-cond.gif]()
With the column(s) in the DIMENSION BY it should be possible to uniquely identify a row. Since employee_id is the primary key of the table, this is ok. The data in colums defined in the MEASURES section can be modified through the RULES. Thus our cell value selection for the salary of employee_id = 100.
Just to confirm the data hasn't changed in the table: (employee_id 100 has a salary of 24000)
What if we were to add 1000 to salary for employee_id from 100 to 105? The cell_assignment section allows for a selection in a column via a loop.
![cell-assignment-scol.gif]()
single_column_for_loop:
![single-column-for-loop.gif]()
This translates to :
If we change the calculation so we have the criteria in only the left of the assignment, it works:
This CV() can be used in a calculation expression:
The loop can also be supplied with a specific set of values (or a subquery).
single_column_for_loop:
![single-column-for-in.gif]()
Here the employee_ids 100, 102, 104 and 105 are the criteria.
single_column_for_loop:
![single-column-for-subquery.gif]()
With a subquery, we can generate the criteria and then use that subquery as the list
Updating all the values in the salary column can be done use another pseudo keyword: ANY. Here we update all salaries (adding 1000 to the existing salary); we use CV() to find the existing salary:
Off course, the above SELECT is easier in the classic way:
What if someone asked (in a dataset ordered by employee_id) to add the next salary and the third next salary to the current salary?
A probable answer could be:
Or ... using the model clause
Caution
That's why the order by clause in the model_rules_clause after cell_assignment is used here:
![model-rules-clause-order.gif]()
The model clause can also be used to generate rows. There is an "expr" branch in the cell_assignment clause that I haven't addressed yet.
![cell-assignment-expr.gif]()
Here the model clause creates a row if it doesn't exist, and then does the calculation.
The "single column for loop" clause and "multiple column for loop" clause have this behaviour too. Earlier I used a hierarchical query to generate some rows:
When using a starting value of 0 (zero), that value will also be shown in the generated rows
![model-clause-returning.gif]()
return_rows_clause:
![return-rows-clause.gif]()
A sample of generating data and using an existing table can be confusing.
As mentioned earlier, filtering the values can't be done in the WHERE of same query block. A subquery construction can solve this. So to construct a value list (100, 102, 104, 105) generate from 100 to 105 and exclude 101 and 103:
The sql modeling in the warehouse guide:
http://docs.oracle.com/database/121/DWHSG/sqlmodel.htm#DWHSG022
The select clause and all its subclauses from the SQL language reference:
http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702
The possible model expressions:
http://docs.oracle.com/database/121/SQLRF/expressions011.htm#SQLRF52086
This clause allows you to use your data like an array, do calculations on that array and then return the calculated data. This sounds a lot like working with a spreadsheet.
In this part I I'll be looking at the model clause with a single dimension.
Prerequisites: A running database, data and a user.
To get everything out of this, I installed my very first 12c Oracle enterprise database with DBCA. It's a single instance (non-plugged) enterprise edition 12.1.0.1 database on a Windows Server 2008 machine named TESTU12. As data I used the separate samples download from oracle.com that contains shemas hr, pm, sh, oe, ix and bi. I created user TEST with the DBA role and create session granted. (It's for testing so the DBA role is least troublesome). For testing I'm using SQLplus.The model clause syntax
Syntactically the model clause sits behind the group by clause in the query block:
The model clause itself has the MODEL keyword, three optional parts and the main model section:
The main model section has two mandatory sections: the model column clause and the model rules clause:
The model columns clauses has this definition:
and the model rules clause is like so :
Minimal select statement with model clause
When trying to understand a new clause, I always try to find the smallest piece of code that works and then gradually add to that. I'll use the hr.employees table from the samples. For testing i create the table in my TEST schema. That way I don't have to worry about foreign keys, triggers, etc. ...
d:\orasql>sqlplus test@ora12c
SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 11 11:23:00 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
>set lines 100
>descr hr.employees
Name Null? Type
----------------------------------------------------- -------- ---------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
>create table employees as select * from hr.employees;
Table created.To ease copy and paste, I'll put the SELECT statements on a new line and also remove the line count number. This helps copying when a statement spans multiple lines. The test data looks like this:
>
select employee_id, first_name, last_name, salary, manager_id, department_id
from employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ---------- ---------- -------------
198 Donald OConnell 2600 124 50
199 Douglas Grant 2600 124 50
200 Jennifer Whalen 4400 101 10
201 Michael Hartstein 13000 100 20
202 Pat Fay 6000 201 20
203 Susan Mavris 6500 101 40
204 Hermann Baer 10000 101 70
205 Shelley Higgins 12008 101 110
206 William Gietz 8300 205 110
100 Steven King 24000 90
101 Neena Kochhar 17000 100 90
102 Lex De Haan 17000 100 90
103 Alexander Hunold 9000 102 60
104 Bruce Ernst 6000 103 60
105 David Austin 4800 103 60
106 Valli Pataballa 4800 103 60
107 Diana Lorentz 4200 103 60
108 Nancy Greenberg 12008 101 100
109 Daniel Faviet 9000 108 100
110 John Chen 8200 108 100
111 Ismael Sciarra 7700 108 100
112 Jose Manuel Urman 7800 108 100
113 Luis Popp 6900 108 100
114 Den Raphaely 11000 100 30
115 Alexander Khoo 3100 114 30
116 Shelli Baida 2900 114 30
117 Sigal Tobias 2800 114 30
118 Guy Himuro 2600 114 30
119 Karen Colmenares 2500 114 30
120 Matthew Weiss 8000 100 50
121 Adam Fripp 8200 100 50
122 Payam Kaufling 7900 100 50
123 Shanta Vollman 6500 100 50
124 Kevin Mourgos 5800 100 50
125 Julia Nayer 3200 120 50
126 Irene Mikkilineni 2700 120 50
127 James Landry 2400 120 50
128 Steven Markle 2200 120 50
129 Laura Bissot 3300 121 50
130 Mozhe Atkinson 2800 121 50
131 James Marlow 2500 121 50
132 TJ Olson 2100 121 50
133 Jason Mallin 3300 122 50
134 Michael Rogers 2900 122 50
135 Ki Gee 2400 122 50
136 Hazel Philtanker 2200 122 50
137 Renske Ladwig 3600 123 50
138 Stephen Stiles 3200 123 50
139 John Seo 2700 123 50
140 Joshua Patel 2500 123 50
141 Trenna Rajs 3500 124 50
142 Curtis Davies 3100 124 50
143 Randall Matos 2600 124 50
144 Peter Vargas 2500 124 50
145 John Russell 14000 100 80
146 Karen Partners 13500 100 80
147 Alberto Errazuriz 12000 100 80
148 Gerald Cambrault 11000 100 80
149 Eleni Zlotkey 10500 100 80
150 Peter Tucker 10000 145 80
151 David Bernstein 9500 145 80
152 Peter Hall 9000 145 80
153 Christopher Olsen 8000 145 80
154 Nanette Cambrault 7500 145 80
155 Oliver Tuvault 7000 145 80
156 Janette King 10000 146 80
157 Patrick Sully 9500 146 80
158 Allan McEwen 9000 146 80
159 Lindsey Smith 8000 146 80
160 Louise Doran 7500 146 80
161 Sarath Sewall 7000 146 80
162 Clara Vishney 10500 147 80
163 Danielle Greene 9500 147 80
164 Mattea Marvins 7200 147 80
165 David Lee 6800 147 80
166 Sundar Ande 6400 147 80
167 Amit Banda 6200 147 80
168 Lisa Ozer 11500 148 80
169 Harrison Bloom 10000 148 80
170 Tayler Fox 9600 148 80
171 William Smith 7400 148 80
172 Elizabeth Bates 7300 148 80
173 Sundita Kumar 6100 148 80
174 Ellen Abel 11000 149 80
175 Alyssa Hutton 8800 149 80
176 Jonathon Taylor 8600 149 80
177 Jack Livingston 8400 149 80
178 Kimberely Grant 7000 149
179 Charles Johnson 6200 149 80
180 Winston Taylor 3200 120 50
181 Jean Fleaur 3100 120 50
182 Martha Sullivan 2500 120 50
183 Girard Geoni 2800 120 50
184 Nandita Sarchand 4200 121 50
185 Alexis Bull 4100 121 50
186 Julia Dellinger 3400 121 50
187 Anthony Cabrio 3000 121 50
188 Kelly Chung 3800 122 50
189 Jennifer Dilly 3600 122 50
190 Timothy Gates 2900 122 50
191 Randall Perkins 2500 122 50
192 Sarah Bell 4000 123 50
193 Britney Everett 3900 123 50
194 Samuel McCain 3200 123 50
195 Vance Jones 2800 123 50
196 Alana Walsh 3100 124 50
197 Kevin Feeney 3000 124 50
107 rows selected.
>The smallest SELECT with a working model clause shows that within the model rules clause the cell assignment is also optional. The image of the model rules clause doesn't reflect this!
This is rather odd, why would you use the model clause if you don't actually use model ... ??? In this SELECT, no calculations are done, so we have the minimal clause to work with: an employee_id and the salary column
>
select employee_id, salary
from employees
model
dimension by (employee_id)
measures (salary)
();
EMPLOYEE_ID SALARY
----------- ----------
198 2600
199 2600
200 4400
201 13000
202 6000
203 6500
204 10000
205 12008
206 8300
100 24000
101 17000
102 17000
103 9000
104 6000
105 4800
106 4800
107 4200
108 12008
109 9000
110 8200
111 7700
112 7800
113 6900
114 11000
115 3100
116 2900
117 2800
118 2600
119 2500
120 8000
121 8200
122 7900
123 6500
124 5800
125 3200
126 2700
127 2400
128 2200
129 3300
130 2800
131 2500
132 2100
133 3300
134 2900
135 2400
136 2200
137 3600
138 3200
139 2700
140 2500
141 3500
142 3100
143 2600
144 2500
145 14000
146 13500
147 12000
148 11000
149 10500
150 10000
151 9500
152 9000
153 8000
154 7500
155 7000
156 10000
157 9500
158 9000
159 8000
160 7500
161 7000
162 10500
163 9500
164 7200
165 6800
166 6400
167 6200
168 11500
169 10000
170 9600
171 7400
172 7300
173 6100
174 11000
175 8800
176 8600
177 8400
178 7000
179 6200
180 3200
181 3100
182 2500
183 2800
184 4200
185 4100
186 3400
187 3000
188 3800
189 3600
190 2900
191 2500
192 4000
193 3900
194 3200
195 2800
196 3100
197 3000
107 rows selected.
>I'll limit the data to 10 rows from here-on for readability. (That's a new feature of 12c: you can fetch first 10 rows only.)
>
select employee_id, salary
from employees
model
dimension by (employee_id)
measures (salary)
()
order by employee_id
fetch first 10 rows only;
EMPLOYEE_ID SALARY
----------- ----------
100 24000
101 17000
102 17000
103 9000
104 6000
105 4800
106 4800
107 4200
108 12008
109 9000
10 rows selected.
>Addressing a specific cell
The model clause should allow me to do calculations on a cell. Here is our first query actually doing something.
Query: Add a 1000 to the salary of employee_id = 100 and leave the rest as is.
In a classic SELECT this can be done with a DECODE (or a CASE).
The salary for employee_id 100 is changed from 24000 to 25000. The table data itself is not changed.
>
select employee_id,
decode(employee_id, 100, salary+1000, salary) salary
from employees
order by employee_id
fetch first 10 rows only;
EMPLOYEE_ID SALARY
----------- ----------
100 25000
101 17000
102 17000
103 9000
104 6000
105 4800
106 4800
107 4200
108 12008
109 9000
10 rows selected.
>To do this with the model we need to figure out how addressing cells and calculations work.
Calculations are done in the RULES section. The RULES keyword is optional. In our minimal statement it's cut down to two parentheses : " () ". With the keyword in place, it's "RULES () ", which makes identifying the RULES section easier. I'll leave the RULES keyword in place to make identification easier.
The cell assignment (the non-optional part that seemed to be optional) is defined like this
With the column(s) in the DIMENSION BY it should be possible to uniquely identify a row. Since employee_id is the primary key of the table, this is ok. The data in colums defined in the MEASURES section can be modified through the RULES. Thus our cell value selection for the salary of employee_id = 100.
SALARY[EMPLOYEE_ID=100]
The calculation is similar to the SET syntax of an update clause. SET column=value. We want to set the value of employee_id 100 from 24000 to 24000+1000 or in syntax:
SALARY[employee_id = 100] = SALARY[employee_id = 100] + 1000
>
select employee_id, salary
from employees
model
dimension by (employee_id)
measures (salary)
RULES (
SALARY[employee_id = 100] = SALARY[employee_id = 100] + 1000
)
order by employee_id
fetch first 10 rows only;
EMPLOYEE_ID SALARY
----------- ----------
100 25000
101 17000
102 17000
103 9000
104 6000
105 4800
106 4800
107 4200
108 12008
109 9000
10 rows selected.
>Just to confirm the data hasn't changed in the table: (employee_id 100 has a salary of 24000)
>
select employee_id, salary
from employees
order by employee_id
fetch first 10 rows only;
EMPLOYEE_ID SALARY
----------- ----------
100 24000
101 17000
102 17000
103 9000
104 6000
105 4800
106 4800
107 4200
108 12008
109 9000
10 rows selected.
>
Addressing a range of cells (using a loop)
What if we were to add 1000 to salary for employee_id from 100 to 105? The cell_assignment section allows for a selection in a column via a loop.
single_column_for_loop:
This translates to :
FOR employee_id FROM 100 to 105 INCREMENT 1
This criteria identifies 6 rows of data. If we write this condition in a statement we get an ORA-32622 error message:
>
select employee_id, salary
from employees
model
dimension by (employee_id)
measures (salary)
RULES (
salary[for employee_id from 100 to 105 INCREMENT 1] =
salary[for employee_id from 100 to 105 INCREMENT 1] + 1000
)
order by employee_id
fetch first 10 rows only;
select employee_id, salary
*
ERROR at line 1:
ORA-32622: illegal multi-cell reference
>If we change the calculation so we have the criteria in only the left of the assignment, it works:
>
select employee_id, salary
from employees
model
dimension by (employee_id)
measures (salary)
RULES (
salary[for employee_id from 100 to 105 INCREMENT 1] = 1000
)
order by employee_id
fetch first 10 rows only;
EMPLOYEE_ID SALARY
----------- ----------
100 1000
101 1000
102 1000
103 1000
104 1000
105 1000
106 4800
107 4200
108 12008
109 9000
10 rows selected.
>The pseudo function CV()
A pseudo function CV() has been added so this value can be obtained in the model clause. It's a shorthand for Current Value. The parentheses are not optional!This CV() can be used in a calculation expression:
>
select employee_id, salary
from employees
model
dimension by (employee_id)
measures (salary)
RULES (
salary[for employee_id from 100 to 105 INCREMENT 1] = salary[cv()] + 1000
)
order by employee_id
fetch first 10 rows only;
EMPLOYEE_ID SALARY
----------- ----------
100 25000
101 18000
102 18000
103 10000
104 7000
105 5800
106 4800
107 4200
108 12008
109 9000
10 rows selected.
>
Addressing a range of cells (using a list of values)
The loop can also be supplied with a specific set of values (or a subquery).
single_column_for_loop:
Here the employee_ids 100, 102, 104 and 105 are the criteria.
>
select employee_id, salary
from employees
model
dimension by (employee_id)
measures (salary)
RULES (
salary[for employee_id in (100, 102, 104, 105)] = salary[cv()] + 1000
)
order by employee_id
fetch first 10 rows only;
EMPLOYEE_ID SALARY
----------- ----------
100 25000
101 17000
102 18000
103 9000
104 7000
105 5800
106 4800
107 4200
108 12008
109 9000
10 rows selected.
>
Addressing a range of cells (using a subquery)
single_column_for_loop:
With a subquery, we can generate the criteria and then use that subquery as the list
>
select level+99 criteria from dual where level not in (2, 4) connect by level <= 6;
CRITERIA
----------
100
102
104
105
>
select employee_id, salary
from employees
model
dimension by (employee_id)
measures (salary)
RULES (
salary[for employee_id in (
select level+99 from dual where level not in (2, 4) connect by level <= 6 )] =
salary[cv()] + 1000
)
order by employee_id
fetch first 10 rows only;
EMPLOYEE_ID SALARY
----------- ----------
100 25000
101 17000
102 18000
103 9000
104 7000
105 5800
106 4800
107 4200
108 12008
109 9000
10 rows selected.
>
The pseudo keyword ANY
Updating all the values in the salary column can be done use another pseudo keyword: ANY. Here we update all salaries (adding 1000 to the existing salary); we use CV() to find the existing salary:
>
select employee_id, salary
from employees
model
dimension by (employee_id)
measures (salary)
RULES (
salary[any] = salary[cv()] + 1000
)
order by employee_id
fetch first 10 rows only;
EMPLOYEE_ID SALARY
----------- ----------
100 25000
101 18000
102 18000
103 10000
104 7000
105 5800
106 5800
107 5200
108 13008
109 10000
10 rows selected.
>Off course, the above SELECT is easier in the classic way:
>
select employee_id, salary + 1000 salary
from employees
order by employee_id
fetch first 10 rows only;
EMPLOYEE_ID SALARY
----------- ----------
100 25000
101 18000
102 18000
103 10000
104 7000
105 5800
106 5800
107 5200
108 13008
109 10000
10 rows selected.
>
Addressing cells relative to the current cell
What if someone asked (in a dataset ordered by employee_id) to add the next salary and the third next salary to the current salary?
A probable answer could be:
And yes, it would work:
Use lead/lag for next salary and ... um, use first_value (since lead/lag doesn't have a windowing clause) for third next salary, wrap that inside a with clause (also called a cte), and then select from that cte and calculate the result
>
with cte as (
select employee_id, salary,
lead(salary) over (order by employee_id) next_salary,
-- lead(salary) over (order by employee_id
-- rows
-- between 3 following
-- and unbounded following) third_next_salary
first_value(salary) over (order by employee_id
rows
between 3 following
and unbounded following) third_next_salary
from employees
)
select employee_id, salary, next_salary, third_next_salary,
salary + next_salary + third_next_salary as calculated_salary
from cte
order by employee_id
fetch first 10 rows only;
EMPLOYEE_ID SALARY NEXT_SALARY THIRD_NEXT_SALARY CALCULATED_SALARY
----------- ---------- ----------- ----------------- -----------------
100 24000 17000 9000 50000
101 17000 17000 6000 40000
102 17000 9000 4800 30800
103 9000 6000 4800 19800
104 6000 4800 4200 15000
105 4800 4800 12008 21608
106 4800 4200 9000 18000
107 4200 12008 8200 24408
108 12008 9000 7700 28708
109 9000 8200 7800 25000
10 rows selected.
>Or ... using the model clause
>
select employee_id, salary
from employees
model
dimension by (employee_id)
measures (salary)
RULES (
salary[any] order by employee_id asc =
salary[cv()] + salary[cv() +1] + salary[cv() +3]
)
order by employee_id
fetch first 10 rows only;
EMPLOYEE_ID SALARY
----------- ----------
100 50000
101 40000
102 30800
103 19800
104 15000
105 21608
106 18000
107 24408
108 28708
109 25000
10 rows selected.
>Caution
salary[any] = salary[cv()] + salary[cv()+1] + salary[cv() + 3] gives an error:
ORA-32637: Self cyclic rule in sequential order MODEL
That's why the order by clause in the model_rules_clause after cell_assignment is used here:
Generating data
The model clause can also be used to generate rows. There is an "expr" branch in the cell_assignment clause that I haven't addressed yet.
Here the model clause creates a row if it doesn't exist, and then does the calculation.
salary[] 1 ] = 1000 creates a row with employee_id = 1 and set 1000 as salary
>
select employee_id, salary
from employees
model
dimension by (employee_id)
measures (salary)
RULES (
salary[ 1 ] = 1000
)
order by employee_id
fetch first 10 rows only;
EMPLOYEE_ID SALARY
----------- ----------
1 1000
100 24000
101 17000
102 17000
103 9000
104 6000
105 4800
106 4800
107 4200
108 12008
10 rows selected.
>The "single column for loop" clause and "multiple column for loop" clause have this behaviour too. Earlier I used a hierarchical query to generate some rows:
>
select level+99 criteria from dual where level not in (2, 4) connect by level <= 6;
CRITERIA
----------
100
102
104
105
>>
select criteria
from dual
model
dimension by (100 criteria)
measures (0 x)
RULES (
x[for criteria from 100 to 105 increment 1] = 0
);
CRITERIA
----------
100
101
102
103
104
105
6 rows selected.
>- the measures column is optional in the colums of the select.
- a default value must be supplied or if using null you need to type cast it: "to_number(null)"
- the generated column can't be used in the where clause within this same query block.
When using a starting value of 0 (zero), that value will also be shown in the generated rows
>
select criteria
from dual
model
dimension by (0 criteria)
measures (0 x)
RULES (
x[for criteria from 100 to 105 increment 1] = 0
);
CRITERIA
----------
0
100
101
102
103
104
105
7 rows selected.
>Row returning behaviour
The model clause can return only the rows updated by the rules or all rows:
return_rows_clause:
A sample of generating data and using an existing table can be confusing.
>
select employee_id
from employees
model
return updated rows
dimension by (employee_id)
measures (0 x)
RULES (
x[for employee_id from 1 to 10 increment 1] = 0
);
EMPLOYEE_ID
-----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
>>
select employee_id
from employees
model
return updated rows
dimension by (employee_id)
measures (0 x)
RULES (
x[for employee_id from 1 to 10 increment 1] = 0
);
EMPLOYEE_ID
-----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
test@TESTU12>
explain plan for
select employee_id
from employees
model
return updated rows
dimension by (employee_id)
measures (0 x)
RULES (
x[for employee_id from 1 to 10 increment 1] = 0
);
Explained.
test@TESTU12>
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 1448787533
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 3 (0)| 00:00:01 |
| 1 | SQL MODEL ORDERED | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| EMPLOYEES | 1 | 4 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPLOYEE_ID"<=10 AND "EMPLOYEE_ID">=1)
14 rows selected.
test@TESTU12>
explain plan for
select employee_id
from employees
model
return updated rows
dimension by (employee_id)
measures (0 x)
RULES (
x[for employee_id from 1 to 10 increment 1] = 0
);
Explained.
test@TESTU12>
create index emp_ix on employees (employee_id);
Index created.
test@TESTU12>
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1519784151
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SQL MODEL ORDERED| | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| EMP_IX | 1 | 4 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID">=1 AND "EMPLOYEE_ID"<=10)
14 rows selected.
test@TESTU12>As mentioned earlier, filtering the values can't be done in the WHERE of same query block. A subquery construction can solve this. So to construct a value list (100, 102, 104, 105) generate from 100 to 105 and exclude 101 and 103:
>
select criteria from (
select criteria
from dual
model
dimension by (100 criteria)
measures (0 x)
RULES (
x[for criteria from 100 to 105 increment 1] = 0
)
)
where criteria not in (101, 103);
CRITERIA
----------
100
102
104
105
>References
Here is a list of references used for writing this article. The model clause has been included since version 10.2. All the references are for version 12.1.The sql modeling in the warehouse guide:
http://docs.oracle.com/database/121/DWHSG/sqlmodel.htm#DWHSG022
The select clause and all its subclauses from the SQL language reference:
http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702
The possible model expressions:
http://docs.oracle.com/database/121/SQLRF/expressions011.htm#SQLRF52086
These are my last weeks. If the doctors can reduce the pain I'll still get to september. Pity, but the Delphi ACE level is out of reach.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)