Let Us Learn Oracle - Part 52 of N [ Model clause of Oracle 10g ]
Introduced in Oracle 10g, the Model clause builds a myraid number of dimensions with one or more matrices. The minimum requirement is to have atleast one dimension, one measure and one partition though the last is optional
Syntax:
SELECT COLUMN NAME(S)
FROM TABLE NAME(S)
[ WHERE CONDITION ]
MODEL
[
[REFERENCE MODELS ]
[PARTITION BY (<COLUMN(S)>)]
DIMENSION BY (<COLUMN(S)>)
MEASURES (<COLUMN(S)>)
[
RULES
[UPSERT | UPDATE]
[AUTOMATIC ORDER | SEQUENTIAL ORDER ]
[ITERATE(n)[ UNTIL (<CONDITION>) ] ]
]
(
[
<CELL ALIGNMENT> = <EXPRESSION(S)>
]
)
]
Let us see some example so that this will be clear
Example1: A simple select query
Select *
From tblEmployee;
EMPID EMPNAME SALARY BELONGSTO DEPTID
----- -------------------------------------------------- ---------- ---------- ----------
1 Deepak Kumar Goyal 6000 India 1
2 Shashi Dayal 5000 Singapore 2
3 Amitav Mallik 16000 India 1
4 Amit Ojha 650 HongKong 5
5 Sumanta Manik 6900 Australia 3
6 Deepak Singh 6890 India 1
7 Shashi Bhushan 5120 India 1
8 Amitav Salonki 7000 India 1
9 Deepak Singh 7900 Singapore 2
10 Shashi Bhushan 5680 Singapore 2
11 Amitav Salonki 1230 Singapore 2
12 Manish Bharat 1230 Singapore 2
13 Abhijeet Moshambique 1230 Singapore 2
14 Sarapati Babulal Apte 7900 Australia 3
15 Shinarayan Pande 520 Australia 3
16 Fatima Sarani 6780 Australia 3
16 rows selected.
If we re-write the same query using the model clause it will be
SQL> Select *
2 From tblEmployee
3 Model
4 Dimension By (EmpId)
5 Measures(EMPNAME,SALARY,BELONGSTO,DEPTID)
6 ();
OR
SQL> Select *
2 From tblEmployee
3 Model
4 Dimension By (EmpId)
5 Measures(EMPNAME,SALARY,BELONGSTO,DEPTID)
6 Rules
7 ();
OR
SQL> Select *
2 From tblEmployee
3 Model;
In both the cases we will get the same result. One thing to keep in mind that is whtever column we are using in the "Select " statement, must be in use in the Dimension, Measure or Partition group else we will receive error. So in the present case, we have one dimension(EmpID) and 4 measures(EMPNAME,SALARY,BELONGSTO,DEPTID). Oracle creates four 1-D array internally for those four measures and they are indexed by EmpId.
Example2: Partitioning records by Model clause
SQL> Select *
2 From tblEmployee
3 Model
4 Partition By(DEPTID)
5 Dimension By (EmpId,EmpName)
6 Measures(SALARY,BELONGSTO)
7 ()
8 Order by DeptId,EmpId,EmpName;
DEPTID EMPID EMPNAME SALARY BELONGSTO
---------- ---------- -------------------------------------------------- ---------- ----------
1 1 Deepak Kumar Goyal 6000 India
1 3 Amitav Mallik 16000 India
1 6 Deepak Singh 6890 India
1 7 Shashi Bhushan 5120 India
1 8 Amitav Salonki 7000 India
2 2 Shashi Dayal 5000 Singapore
2 9 Deepak Singh 7900 Singapore
2 10 Shashi Bhushan 5680 Singapore
2 11 Amitav Salonki 1230 Singapore
2 12 Manish Bharat 1230 Singapore
2 13 Abhijeet Moshambique 1230 Singapore
3 5 Sumanta Manik 6900 Australia
3 14 Sarapati Babulal Apte 7900 Australia
3 15 Shinarayan Pande 520 Australia
3 16 Fatima Sarani 6780 Australia
5 4 Amit Ojha 650 HongKong
16 rows selected.
In this query we are partitioning the tblEmployee by DeptID column which is having two dimensions viz EmpID,EmpName and two measures Salary and BelongsTo field.If we try to depict it we may get the below
|
SALARY [It is a measure] |
BELONGSTO [It is a measure] |
| DeptID = 1 [ It is the partition ] |
SALARY [1,Deepak Kumar Goyal = 6000 => (EMPID , EMPNAME) They are the dimensions ] |
BELONGSTO [1,Deepak Kumar Goyal = 6000 => (EMPID , EMPNAME) They are the dimensions ] |
| .......................................................................................................................... |
| .......................................................................................................................... |
| .......................................................................................................................... |
| .......................................................................................................................... |
| DeptID = 5 [ It is the partition ] |
SALARY [4,Amit Ojha = 650 => (EMPID , EMPNAME) They are the dimensions ] |
BELONGSTO [4,Amit Ojha = 650 => (EMPID , EMPNAME) They are the dimensions ] |
Example3: Adding a single row at runtime in the record set by Model clause
Consider the below example
SQL> Select
2 EmpId,EmpName,Salary
3 From tblEmployee
4 Model
5 Dimension By (EmpId)
6 Measures(EmpName,Salary)
7 Rules
8 (
9 EmpName[101] = 'New Employee',
10 Salary[101] =7000
11 );
EMPID EMPNAME SALARY
---------- -------------------------------------------------- ----------
1 Deepak Kumar Goyal 6000
2 Shashi Dayal 5000
3 Amitav Mallik 16000
4 Amit Ojha 650
5 Sumanta Manik 6900
6 Deepak Singh 6890
7 Shashi Bhushan 5120
8 Amitav Salonki 7000
9 Deepak Singh 7900
10 Shashi Bhushan 5680
11 Amitav Salonki 1230
12 Manish Bharat 1230
13 Abhijeet Moshambique 1230
14 Sarapati Babulal Apte 7900
15 Shinarayan Pande 520
16 Fatima Sarani 6780
101 New Employee 7000
17 rows selected.
We have added a new employee whose dimension value is 101 for the two measures EmpName,Salary. This record is added at runtime and does not physically exist in the table
Example4: Adding multiple rows at runtime to the record set by Model clause
SQL> Select
2 EmpId,EmpName,Salary
3 From tblEmployee
4 Model
5 Dimension By (EmpId)
6 Measures(EmpName,Salary)
7 Rules
8 (
9 EmpName[101] = 'New Employee',
10 EmpName[102] = 'Another New Employee',
11 Salary[101] =7000,
12 Salary[102] = 8000
13 )
14 Order By EmpId;
EMPID EMPNAME SALARY
---------- -------------------------------------------------- ----------
1 Deepak Kumar Goyal 6000
2 Shashi Dayal 5000
3 Amitav Mallik 16000
4 Amit Ojha 650
5 Sumanta Manik 6900
6 Deepak Singh 6890
7 Shashi Bhushan 5120
8 Amitav Salonki 7000
9 Deepak Singh 7900
10 Shashi Bhushan 5680
11 Amitav Salonki 1230
12 Manish Bharat 1230
13 Abhijeet Moshambique 1230
14 Sarapati Babulal Apte 7900
15 Shinarayan Pande 520
16 Fatima Sarani 6780
101 New Employee 7000
102 Another New Employee 8000
18 rows selected.
As can be seen that two new rows has been created at runtime and added to the recordset
We have seen how to add rows at runtime. But it projects both the newly inserted and the old rows. We can however project only the newly created rows by using the
Return Updated Rows
Example5: Project only newly inserted rows by using Return Updated Rows of Model clause
SQL> Select
2 EmpId,EmpName,Salary
3 From tblEmployee
4 Model
5 Return Updated Rows
6 Dimension By (EmpId)
7 Measures(EmpName,Salary)
8 Rules
9 (
10 EmpName[101] = 'New Employee',
11 EmpName[102] = 'Another New Employee',
12 Salary[101] =7000,
13 Salary[102] = 8000
14 )
15 Order By EmpId;
EMPID EMPNAME SALARY
---------- -------------------------------------------------- ----------
101 New Employee 7000
102 Another New Employee 8000
Example6: Insert a row after every record partitioned by Department id
SQL> Select
2 EmpID,EmpName,Salary,DeptID
3 From tblEmployee
4 Model
5 Partition By (DeptId)
6 Dimension By (EmpId)
7 Measures(EmpName,Salary)
8 Rules
9 (
10 EmpName[101] = 'New Employee',
11 Salary[101] =7000
12 )
13 Order By
14 DeptID,EmpID,EmpName;
EMPID EMPNAME SALARY DEPTID
---------- -------------------------------------------------- ---------- ----------
1 Deepak Kumar Goyal 6000 1
3 Amitav Mallik 16000 1
6 Deepak Singh 6890 1
7 Shashi Bhushan 5120 1
8 Amitav Salonki 7000 1
101 New Employee 7000 1
2 Shashi Dayal 5000 2
9 Deepak Singh 7900 2
10 Shashi Bhushan 5680 2
11 Amitav Salonki 1230 2
12 Manish Bharat 1230 2
13 Abhijeet Moshambique 1230 2
101 New Employee 7000 2
5 Sumanta Manik 6900 3
14 Sarapati Babulal Apte 7900 3
15 Shinarayan Pande 520 3
16 Fatima Sarani 6780 3
101 New Employee 7000 3
4 Amit Ojha 650 5
101 New Employee 7000 5
20 rows selected.
We can figure out that after every department a new recod has been added
Hope we get some idea about the model clause, it's powerful benefit and how to use it.
Reference:SQL for Modeling
Thanks for reading