Notes On SQL MODEL Clause In Oracle

Oracle has introduced a very powerful mechanism called SQL Model to improve performance on SQL statements. Main idea of SQL Model clause is, to make possible some complex queries via SQL statements.

Model is based on arrays. MEASURES keyword identifies which arrays will be used. Indice of arrays are iddentified in DIMENSION BY statement. As a result, you can make array-based calculations in SQL easily.

In Model clause, business rules are taken into SQL. It is possible not to use a procedural language with model clause. One example of this can be found at the end of this entry with fibonacci numbers.

To make clear understanding of Model clause it'd better make some exercises:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> DROP TABLE worker;

Table dropped

SQL> CREATE TABLE worker( id number primary key, name varchar2(32), salary number);

Table created

SQL> INSERT INTO worker VALUES (1, 'Mennan', 1200);

1 row inserted

SQL> INSERT INTO worker VALUES (2, 'Ali', 1500);

1 row inserted

SQL> INSERT INTO worker VALUES (3, 'Selim', 900);

1 row inserted

SQL> INSERT INTO worker VALUES (4, 'Ayse', 1450);

1 row inserted

SQL> INSERT INTO worker VALUES (5, 'Seyyah', 2900);

1 row inserted

SQL> SELECT *
  2    FROM worker wr
  3  MODEL
  4  DIMENSION BY(wr.id i)
  5  MEASURES(wr.NAME n, wr.salary sa)
  6  RULES();

         I N                                        SA
---------- -------------------------------- ----------
         1 Mennan                                 1200
         2 Ali                                    1500
         3 Selim                                   900
         4 Ayse                                   1450
         5 Seyyah                                 2900

In example above  n[i] descr,bes ith worker's name and sa[i] describes ith worker's salary. For instance n[1] is Mennan, sa[4] is 1450

SQL> SELECT *
  2    FROM worker wr
  3  MODEL
  4  DIMENSION BY(wr.id i)
  5  MEASURES(wr.NAME n, wr.salary sa)
  6  RULES(
  7  sa[any] = sa[cv()] + 10000 );

         I N                                        SA
---------- -------------------------------- ----------
         1 Mennan                                11200
         2 Ali                                   11500
         3 Selim                                 10900
         4 Ayse                                  11450
         5 Seyyah                                12900

CV() only can be used RIGHT-HAND SIDE and ANY only can be used LEFT-HAND SIDE of operators. sa[any] describes all employes' salaries.

SQL> SELECT *
  2    FROM worker wr
  3  MODEL
  4  DIMENSION BY(wr.id i)
  5  MEASURES(wr.NAME n, 0 temp, wr.salary sa)
  6  RULES(
  7  temp[any] = sa[cv()] + 10000 );

         I N                                      TEMP         SA
---------- -------------------------------- ---------- ----------
         1 Mennan                                11200       1200
         2 Ali                                   11500       1500
         3 Selim                                 10900        900
         4 Ayse                                  11450       1450
         5 Seyyah                                12900       2900

It is possible to use temporary arrays in MODEL clause.

SQL> SELECT *
  2    FROM worker wr
  3  MODEL
  4  DIMENSION BY(wr.id i)
  5  MEASURES(wr.NAME n, wr.salary sa, cast('' as varchar2(32)) names )
  6  RULES(
  7  names[any] =  n[cv()] || '....' );

         I N                                        SA NAMES
---------- -------------------------------- ---------- --------------------------------
         1 Mennan                                 1200 Mennan....
         2 Ali                                    1500 Ali....
         3 Selim                                   900 Selim....
         4 Ayse                                   1450 Ayse....
         5 Seyyah                                 2900 Seyyah....

It is also possible to use string arrays with CAST operator.

SQL> SELECT *
  2    FROM worker wr
  3  MODEL
  4  DIMENSION BY(wr.id i)
  5  MEASURES(wr.NAME n, wr.salary sa, cast('' as varchar2(32)) names )
  6  RULES ITERATE( 2 )(
  7  names[any] = names[CV()] || n[cv()] || '....' );

         I N                                        SA NAMES
---------- -------------------------------- ---------- --------------------------------
         1 Mennan                                 1200 Mennan....Mennan....
         2 Ali                                    1500 Ali....Ali....
         3 Selim                                   900 Selim....Selim....
         4 Ayse                                   1450 Ayse....Ayse....
         5 Seyyah                                 2900 Seyyah....Seyyah....

Iteration can be used in RULE section.

SQL> SELECT *
  2    FROM worker wr
  3  MODEL IGNORE NAV
  4  DIMENSION BY(wr.id i)
  5  MEASURES( wr.salary sa)
  6  RULES UPSERT
  7  (
  8    sa[99999] = MAX(sa)[i BETWEEN 1 AND 3]
  9  );

         I         SA
---------- ----------
         4       1450
         5       2900
         1       1200
         2       1500
         3        900
     99999       1500

6 rows selected

UPSERT keyword is used for getting all rows. Some aggregate functions can be used in Model clause.

Example below shows how to calculate fibonacci series.

SQL> SELECT fi
  2    FROM dual
  3  MODEL
  4  DIMENSION BY( 1 i)
  5  MEASURES(1 b1, 1 b2, 0 fi )
  6  RULES ITERATE( 5 )(
  7  fi[any] = b1[cv()] + b2[cv()],
  8  b2[any] = b1[cv()],
  9  b1[any] = fi[cv()]
 10  );

        FI
----------
        13