Using the New MODEL Clause in Oracle Database 10g

One of the great new features of Oracle's flagship database software, Oracle Database 10g, is its new MODEL clause, which you can use in SELECT statements. In this article we'll look at some examples of the MODEL clause in action, and show how you can use MODEL to manipulate your data.

A Basic MODEL Clause Example

The simplest MODEL clause example does nothing more than a regular SELECT statement. Here's an example:

select empno,ename,sal
  from emp;

EMPNO ENAME             SAL
----- ---------- ----------
 7369 SMITH             800
 7499 ALLEN            1600
 7521 WARD             1250
 7566 JONES            2975
 7654 MARTIN           1250
 7698 BLAKE            2850
 7782 CLARK            2450
 7788 SCOTT            3000
 7839 KING             5000
 7844 TURNER           1500
 7876 ADAMS            1100
 7900 JAMES             950
 7902 FORD             3000
 7934 MILLER           1300

select empno,ename,sal
  from emp
 model 
   dimension by (empno) 
   measures (ename,sal)
   rules ();

EMPNO ENAME             SAL
----- ---------- ----------
 7369 SMITH             800
 7499 ALLEN            1600
 7521 WARD             1250
 7566 JONES            2975
 7654 MARTIN           1250
 7698 BLAKE            2850
 7782 CLARK            2450
 7788 SCOTT            3000
 7839 KING             5000
 7844 TURNER           1500
 7876 ADAMS            1100
 7900 JAMES             950
 7902 FORD             3000
 7934 MILLER           1300

Related Reading Mastering Oracle SQL By Sanjay Mishra, Alan Beaulieu Table of Contents Index Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

The MODEL clause example simply returns all the employee numbers, names, and salaries from the emp table. Nothing out of the ordinary happened, but the syntax is obviously more than just "select ... from ... ."

The measures, ename, and sal are our arrays. So, when using the MODEL clause, the attributes that make up our tables can be treated like arrays. Each row and column can be manipulated independently just like an array.

The dimension clause is used to identify a specific array value. So, in the example above, we have two arrays named ename and sal whose default values are the names and salaries of the employees. The way to access an individual name or salary is to reference the "dimension"--in this case the employee number.

For example, how would you reference the name King or King's salary? You would use ename[7839] or sal[7839], respectively. The array that holds the employee names is ename[], and referencing ename[7839] returns a specific name, KING.

Since we can treat our rows like arrays, we can easily modify their values through assignment. Let's change King's name to HOMER and his salary to 0:

select empno,ename,sal
  from emp
 model 
   dimension by (empno) 
   measures (ename,sal)
   rules (
     ename[7839] = 'HOMER',
       sal[7839] = 0
   );

   EMPNO ENAME             SAL
-------- ---------- ----------
    7369 SMITH             800
    7499 ALLEN            1600
    7521 WARD             1250
    7566 JONES            2975
    7654 MARTIN           1250
    7698 BLAKE            2850
    7782 CLARK            2450
    7788 SCOTT            3000
    7844 TURNER           1500
    7876 ADAMS            1100
    7900 JAMES             950
    7902 FORD             3000
    7934 MILLER           1300
    7839 HOMER               0

Not only can we modify existing values in our result set, but we can also add values that don't exist. (Please note that we are not performing DML (Data Manipulation Language) on the table; we're just modifying the result set.)

select empno,ename,sal
  from emp
 model 
   dimension by (empno) 
   measures (ename,sal)
   rules (
     ename[7839] = 'HOMER',
       sal[7839] = 0,
     ename[9999] = 'MR.BURNS',
       sal[9999] = 250
   );

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7844 TURNER           1500
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300
      7839 HOMER               0
      9999 MR.BURNS          250

MR.BURNS with a salary of 250 does not exist in the emp table, but we easily added it to the result set.

Using DECODE or CASE, we can easily change values in a result set just like we did in the example with HOMER, but the MODEL clause makes it easier to add new rows to the result set.

The Oracle documentation explains how to use the MODEL clause detail. The point of the simple examples above is to introduce you to the syntax and how the MODEL clause allows you to manipulate your data.

So, What's It Really Good For?

After getting familiar with the MODEL clause, you may be wondering what I was thinking after trying it out for the first time: "Cool, but what do I need this for?" According to the white papers available on the Oracle Technology Network, the MODEL clause's main purpose is to bring spreadsheetlike power to your SQL and to let you perform your more complex calculations without the need for a third-party tool. If you test some of the examples in the Oracle doc, you can see how useful the MODEL clause is in forecasting, for example.

A practical use of this forecasting (for any DBA or database developer) could be to determine future tablespace growth based on past growth during the last n months. An example of calculating exponential growth is included in the documentation. Because of the flexibility of the MODEL clause, you can easily forecast more accurate growth patterns using, say, best-fit polynomials rather than just calculating exponential growth patterns (which may not be realistic).

Another useful feature of the MODEL clause is that it lets you embed procedural logic directly in your SQL. This can let you perform some of your complex code directly in SQL. The power of SQL lies in its ability to process data in a set-oriented fashion. The MODEL clause retains this set-based nature and also introduces procedural power and flexibility directly into your SQL. The aim of this paper is to introduce you to the procedural capabilities of the 10g MODEL clause and its effect on performance and problem solving.