<

The Oracle MODEL clause (part I)

Published on
9,582 Points
3,382 Views
2 Endorsements
Last Modified:
Approved
Geert G
"Did i really beat the cancer ?"
Nope, i didn't . Not yet anyway.
Now it's waiting, waiting, waiting ... until they say something.
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.
 

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:
 
query-block.gifThe 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.gifThe model columns clauses has this definition:
model-column-clauses.gifand the model rules clause is like so :
model-rules-clause.gif


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.

Open in new window


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.

>

Open in new window


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.

>

Open in new window


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.

>

Open in new window

 

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.

>

Open in new window


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.gifWith 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.

>

Open in new window


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.

>

Open in new window



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.
cell-assignment-scol.gifsingle_column_for_loop:
single-column-for-loop.gifThis 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


>

Open in new window


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.

>

Open in new window

The data was changed for the employee_id from 100 to 105, but the value here is set to 1000. The selection criteria works. The relative calculation doesn't. So how do we get the actual value on the right side of the equals sign of the calculating row?
 

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.

>

Open in new window



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:
single-column-for-in.gifHere 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.

>

Open in new window



Addressing a range of cells (using a subquery)

single_column_for_loop:
single-column-for-subquery.gifWith 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.

>

Open in new window



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.

>

Open in new window


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.

>

Open in new window



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:

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
And yes, it would work:

>
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.

>

Open in new window


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.

>

Open in new window


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:
model-rules-clause-order.gif


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.
cell-assignment-expr.gifHere 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.

>

Open in new window


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

>

Open in new window

This can also be done with the model clause and the single column for loop. I'll start with the complete range: from 100 to 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.

>

Open in new window

There are a few tricks in play here:

  1. the measures column is optional in the colums of the select.
  2. a default value must be supplied or if using null you need to type cast it: "to_number(null)"
  3. the generated column can't be used in the where clause within this same query block.
Caution
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.

>

Open in new window

One way of solving this was by using the starting value or ... by changing the default model clause behaviour in what rows it returns.
 

Row returning behaviour

The model clause can return only the rows updated by the rules or all rows:
model-clause-returning.gifreturn_rows_clause:
return-rows-clause.gif
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.

>

Open in new window

The sample data contains employee_id from 100 to 206. The rules have generated (read "updated") rows with employee_id from 1 to 10.  Because "return updated rows" is in place the select only returns those rows. Be careful with this as the model actually accesses the table. When using an indexed column the index can be used; otherwise a full table scan is possible.

>
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>

Open in new window


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

>

Open in new window

 

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
2
Comment
Author:Geert G
1 Comment
 
 

Administrative Comment

by:Eric AKA Netminder
Geert,

Congratulations! Your article has been Accepted and has been awarded Approved status.

ericpete
Page Editor
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month