Oracle Illustrated

Feb 21 2010   7:42AM GMT

Migrating from 9i to 11g – MODEL

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Yet another exciting feature – haven’t really had a requirement to apply it though – waiting !!

SQL MODEL clause enables to define a multidimensional array on query results – then can apply rules on the array to calculate new values. The rules clause can be applied for calculations. This enables to perform advanced calculations without using views or calculating the same in spreadsheets.

It defines a multidimensional array by mapping the columns of a query into three groups – partitioning, dimension, and measure columns.

Partitions - define blocks of the result set (similar to analytical functions)
Dimensions - identify each measure cell within a partition
Measures - they are similar to the measures of a fact table in a star schema (data warehousing). They contain numeric values.

Use of Model clause – Model clause uses a subset of the available columns from your FROM clause. It contains at least one dimension, at least one measure and optionally one or more partitions.

Simple Model clause – without any rule

select empno,empname,sal
from emp where deptno = 630 and rownum < 6
model
dimension by (empno)
measures (empname,sal)
rules () ;

EMPNO EMPNAME SAL
———- —————————— ———-
26003 /edfa72de_SimpleContentModel 26664
26005 /c024bdbb_CMStateSet 26666
26007 /1def91f2_CMNode 26668
26009 /5f106d9a_DTDGrammar 26670
26011 /761002ee_XMLDTDDescription 26672

Elapsed: 00:00:00.06

The above is just same as-

select empno,empname,sal
from emp where deptno = 630 and rownum < 6

Using Rules

select empno,empname,comm
from emp WHERE rownum < 6
model
dimension by (empno)
measures (empname,comm)
rules (
empname[770] = ‘UNDO$’,
comm[770] = 5000
);

EMPNO EMPNAME COMM
———- —————————— ———-
1 ICOL$ 110929.5
2 I_USER1 132064.5
3 CON$ 14350.5
4 UNDO$ 35766
5 C_COBJ# 10611
770 UNDO$ 5000

6 rows selected.

Elapsed: 00:00:00.01

select empno,empname,comm
from emp WHERE rownum < 6
model
dimension by (empno)
measures (empname,comm)
rules (
empname[990] = ‘UNDO$’,
comm[990] = 5000
);

EMPNO EMPNAME COMM
———- —————————— ———-
1 ICOL$ 110929.5
2 I_USER1 132064.5
3 CON$ 14350.5
4 UNDO$ 35766
5 C_COBJ# 10611
990 UNDO$ 5000

The rule mentioned above (highlighted in red) shows how measure empname is expanded with dimension 990 and gets the value ‘UNDO$’. As EMP contains empno 990 already, the data is overridden and the value is displayed.

Say another example – lets check for a value where data does not exist-

select empno,empname,comm
from emp WHERE rownum < 6
model
dimension by (empno)
measures (empname,comm)
rules (
empname[102222990] = ‘UNDO$’,
comm[102222990] = 5000
);

In this case 102222990 does not exist in EMP table, so now a new cell has been created, which shows itself as a new row in the result set. Note – this row is not inserted into the table, but only to the result set of the query. Note the second rule COMM enables to display the commission also!!

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: