Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Chacravarthi.

Asked: February 23, 2004 - 8:05 am UTC

Last updated: January 30, 2013 - 12:47 pm UTC

Version: 9.0.1

Viewed 10K+ times! This question is

You Asked

Dear Sir,
Have you started using Oracle 10g?If so,Could you please explain
"SQL Model clause" with a simple example?Expecting your reply
Yours sincerely,
R.Chacravarthi

and Tom said...

that is sort of like asking one to summarize "war and peace" in a sentence. cannot do it justice.

see
</code> http://otn.oracle.com/products/bi/pdf/10gr1_twp_bi_dw_sqlmodel.pdf <code>



Rating

  (18 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Book-a-munute

Mikito Harakiri, February 23, 2004 - 12:54 pm UTC

</code> http://rinkworks.com/bookaminute/classics.shtml <code>

They should open tech section

Upsert

A reader, November 12, 2004 - 6:37 pm UTC

Hi Tom,

Could you please explain the following:

1. I get two rows from this:

SELECT X1, X2, Y, Z FROM DUAL
MODEL
DIMENSION BY (1 X1, 1 X2)
MEASURES (1 Y, 1 Z)
RULES UPSERT
(
Y[2, 1] = Y[1, 1]
)

2. I also get two rows for this:
SELECT X1, X2, Y, Z FROM DUAL
MODEL
DIMENSION BY (1 X1, 1 X2)
MEASURES (1 Y, 1 Z)
RULES UPSERT
(
Y[2, 2] = Y[1, 1]
)

3. However I got only one row from either of the followings:

SELECT X1, X2, Y, Z FROM DUAL
MODEL
DIMENSION BY (1 X1, 1 X2)
MEASURES (1 Y, 1 Z)
RULES UPSERT
(
Y[2, any] = Y[1, 1]
)

SELECT X1, X2, Y, Z FROM DUAL
MODEL
DIMENSION BY (1 X1, 1 X2)
MEASURES (1 Y, 1 Z)
RULES UPSERT
(
Y[2, x2=1] = Y[1, 1]
)

Why? How can I write a rule so that for any occurance of X1=1 give me a copy of X1=2?

SQL Model

Marcio Portes, May 01, 2006 - 8:20 pm UTC

I think the only way to write query to come up with Fibonacci Sequence in SQL is using SQL Model right? I'm saying that because the formula: f(n)=f(n-1)+f(n-2) thus it needs two rows ago within set, neither lag() or lead() could help here.

I give it a shot.

select s seq
from dual
model return all rows
dimension by ( 0 d ) measures ( 0 s )
rules iterate (12) (
s[iteration_number ] = decode(
iteration_number, 0, 0, 1, 1, s[iteration_number-2]
) + nvl(s[iteration_number-1],0)
)
/

Do you see any other way?
Regards,

Tom Kyte
May 02, 2006 - 3:38 am UTC

not off the top of my head... haven't really thought about it too hard :)

Fibonacci

Sharuk, October 21, 2008 - 12:19 pm UTC

Way 1:

with data as (select level levels from dual
                     connect by level <= &how_may_rows) 
select f from data 
                     model dimension by (levels) 
                     measures ( 0 f) 
          rules (   f[1] = 0 , f[2] = 1 , f[levels>2]=f[cv(levels)-2]+f[cv(levels)-1]
);
Enter value for how_may_rows: 10
old   2:                       connect by level <= &how_may_rows)
new   2:                       connect by level <= 10)

         F
----------
         0
         1
         1
         2
         3
         5
         8
        13
        21
        34

10 rows selected.



Way 2: Using Math Formula 

SQL> select round ((power ((1 + sqrt (5)) / 2, level - 1) - power ((1 - sqrt (5)) / 2, level - 1)) / sqrt (5)) fib
      from dual
connect by level <=&n;
Enter value for n: 8
old   3: connect by level <=&n
new   3: connect by level <=8

       FIB
----------
         0
         1
         1
         2
         3
         5
         8
        13

8 rows selected.

Fibonacci / Connect By

Parthiban Nagarajan, February 10, 2010 - 10:55 pm UTC

Hi Tom

With 11g R2, using "recursive subquery factoring", we can generate the Fibonacci series easily. Using the MODEL clause is another option. With 9i R2, "connect by" is available as the option.
But, my request is a query to generate the series with "connect by" clause but without using the "Math Formula" as above (i.e., by using the recursive, hierarchical of "connect by" itself).
Is it possible?
Tom Kyte
February 16, 2010 - 8:13 am UTC

why? you have a solution??? what is the goal here?

@Parthiban

Sokrates, February 18, 2010 - 6:04 am UTC

probably
with phi as
(
select (sqrt(5)+1)/2 as phi
from dual
)
,
n as
(select level n from dual connect by level <= 10000)
select n
from n, phi
connect by abs((1 - phi) * prior n + power(phi, level) - n) < 0.01
start with n=1



comes close to what you want (?)
but really: what is the goal here ?

or, provided someone doesn't like sqrt, power ...

Sokrates, February 20, 2010 - 7:31 am UTC

with n as
(select level n from dual connect by level <= 1000)
select m
from
(
select n1.n n, n2.n m
from n n1, n n2
connect by prior n1.n + prior n2.n = n1.n and prior n1.n = n2.n
start with n1.n=1 and n2.n = 1
)

is even worse !

SubFactoring in 11g

srinivas, June 01, 2012 - 5:54 pm UTC

With Fibo(n1,n2,n,ctr)
as
(Select -1 n1,1 n2,0 n,1 ctr from dual
Union All
Select n2 n1,n n2,n+n2 n,ctr+1 ctr from Fibo
Where ctr<8)
Select * From Fibo

some more

venkat, June 25, 2012 - 5:49 am UTC

Tom,

Your future release on Oracle will be of no use, if you can't provide or help us with examples on MODEL clause, because this MODEL clause is really intimidating, even oracle has not provided us with any good examples. As usual we were expecting some basic examples, but could not find any in oracle documentation.Hope to get some examples on MODEL cluase and how to use this.
Tom Kyte
June 25, 2012 - 9:55 am UTC

you must be pretty bad at searching the documentation then?

http://docs.oracle.com/cd/E11882_01/server.112/e25554/sqlmodel.htm


Guess what that link is - the very first link you get when you search for.....


MODEL CLAUSE

http://www.oracle.com/pls/db112/search?word=model+clause&partno=

:)

LMGTFY

Steve H, June 25, 2012 - 10:51 am UTC

Tom Kyte
June 25, 2012 - 11:19 am UTC

neat, even google brought that same exact link to the top for me as well. the *same* exact link (might not do it for everyone in every country....)

RE: if you can't provide or help us with examples on MODEL clause...

Duke Ganote, June 25, 2012 - 2:35 pm UTC

Tom already provided very useful examples here: download.oracle.com/owsf_2003/40166.ppt

Some other useful links are here:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:21685363725298#4465476000346497689
Tom Kyte
June 25, 2012 - 9:06 pm UTC

there are literally millions of examples out there. In the documentation, on other sites, on this site.

It is simply a matter of actually looking for them to find them.

very nice

venkat, July 16, 2012 - 1:05 am UTC

thank you everyone for your valuable inputs especially Duke Ganote. i hope sql MODEL clause will find a place in http://www.howstuffworks.com/ very soon

A reader, August 13, 2012 - 9:43 am UTC

In the code below , how can I calculate c6 ( which is based on c5) .



SQL> DROP TABLE t purge;

Table dropped.

SQL> CREATE TABLE t
  2    (
  3      i  INT PRIMARY KEY,
  4      c1 INT ,
  5      c2 INT ,
  6      c3 INT ,
  7      c4 INT ,
  8      c5 INT
  9    ) ;

Table created.

SQL> INSERT INTO t VALUES
  2    ( 1 , 3 , 2 , 1 , NULL , NULL
  3    ) ;

1 row created.

SQL> INSERT INTO t VALUES
  2    ( 2 , 23 , 43 , 29 , NULL , NULL
  3    ) ;

1 row created.

SQL> INSERT INTO t VALUES
  2    ( 3 , 7 , 10 , 5 , NULL , NULL
  3    ) ;

1 row created.

SQL> INSERT INTO t VALUES
  2    ( 4 , 5 , 5 , 5 , NULL , NULL
  3    ) ;

1 row created.

SQL> INSERT INTO t VALUES
  2    ( 5 , 0 , 0 , 0 , NULL , NULL
  3    ) ;

1 row created.

SQL> INSERT INTO t VALUES
  2    ( 6 , -1 , -7 , -99 , NULL , NULL
  3    ) ;

1 row created.

SQL> SELECT * FROM t ;

         I         C1         C2         C3         C4         C5
---------- ---------- ---------- ---------- ---------- ----------
         1          3          2          1
         2         23         43         29
         3          7         10          5
         4          5          5          5
         5          0          0          0
         6         -1         -7        -99

6 rows selected.

SQL>     Select i , c1 , c2 , c3 , c4   , c5  , c6  from t model
  2    Partition by  (i)
  3    dimension by ( c1 , c2 , c3)
  4    measures ( c1 c4 , c1  c5  , 0 c6)
  5    ( c4 [c1,c2,c3] = 100
  6    , c5[c1,c2,c3] = cv(c1)* 5
  7   ,  c6[c1,c2,c3] = c5[c1,c2,c3]  * 2 )
  8    order by i ;
 ,  c6[c1,c2,c3] = c5[c1,c2,c3]  * 2 )
                      *
ERROR at line 7:
ORA-32622: illegal multi-cell reference


SQL>

c6? see example

Duke Ganote, August 28, 2012 - 11:06 am UTC

WITH t AS (
SELECT 1 AS i, 3 AS c1, 2 AS c2, 1 AS c3
, cast(null as int) AS c4, cast(null as number) AS c5
FROM DUAL UNION ALL
SELECT 2 , 23 , 43 , 29 , NULL , NULL
FROM DUAL UNION ALL
SELECT 3 , 7 , 10 , 5 , NULL , NULL
FROM DUAL UNION ALL
SELECT 4 , 5 , 5 , 5 , NULL , NULL
FROM DUAL UNION ALL
SELECT 5 , 0 , 0 , 0 , NULL , NULL
FROM DUAL UNION ALL
SELECT 6 , -1 , -7 , -99 , NULL , NULL
FROM DUAL
)
SELECT i , c1 , c2 , c3 , c4 , c5 , c6
FROM t MODEL
DIMENSION BY (i)
MEASURES ( c1, c2, c3, c4, c5, 0 as c6 )
( c4 [i] = 100
, c5 [i] = c1[cv()] * 5
, c6 [i] = c5[cv()] * 2 )
ORDER BY i
/

I C1 C2 C3 C4 C5 C6
----- ---------- ---------- ---------- ---------- ---------- ----------
1 3 2 1 100 15 30
2 23 43 29 100 115 230
3 7 10 5 100 35 70
4 5 5 5 100 25 50
5 0 0 0 100 0 0
6 -1 -7 -99 100 -5 -10

model clause

venkat, January 24, 2013 - 12:54 am UTC

Hi Tom,

I have a query like as below

select empno,deptno, sum(sal) over (partition by deptno) tot_sal from scott.emp;


will give result like this

EMPNO DEPTNO TOT_SAL
---------- ---------- ----------
7934 10 8750
7782 10 8750
7839 10 8750
7902 20 10875
7876 20 10875
7566 20 10875
7369 20 10875
7788 20 10875

8 rows selected.

but as you can see the total sal and deptno repeats , what i want is dept wise total salary as shown below

EMPNO DEPTNO TOT_SAL
---------- ---------- ----------
7934
7782
7839
Total for deptno 10 = 8750
7902
7876
7566
7369
7788
Total for deptno 20 = 10875

i want to use model clause and get the above result. hope i am clear with my query

I hope you don't need create table script fo emp table ...

Tom Kyte
January 30, 2013 - 12:47 pm UTC

you do not want the model clause. that would be like using 14 tiers and J2EE entity beans with soa interfaces to write asktom. it would be "not the right tool" for the job.

ops$tkyte%ORA11GR2> select empno,
  2         case
  3             when grouping( empno ) = 1
  4             then 'Total for deptno ' || deptno || ' = ' || sum(sal)
  5              end tot_sal
  6    from scott.emp
  7   group by grouping sets( (empno,deptno), (deptno) )
  8  /

     EMPNO TOT_SAL
---------- ------------------------------
      7782
      7839
      7934
           Total for deptno 10 = 8750
      7369
      7566
      7788
      7876
      7902
           Total for deptno 20 = 10875
      7900
      7499
      7521
      7654
      7698
      7844
           Total for deptno 30 = 9400

17 rows selected.

to venkat

igor, February 13, 2013 - 3:04 pm UTC

venkat, here is almost exactly what you wanted .. enjoy :)

SQL> SET PAGESIZE 50
SQL> SELECT a AS "EMPNO     DEPTNO    TOT_SAL"
  2    FROM emp
  3   MODEL
  4     PARTITION BY (deptno)
  5     DIMENSION BY (ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY empno DESC) rn)
  6     MEASURES (empno, sal, LPAD(' ',27) a)
  7     RULES ( a[rn>0] = empno[CV()],
  8             a[0] = 'Total for deptno '||CV(deptno)||' = '||SUM(sal)[rn]
  9           )
 10  ORDER BY deptno, rn DESC;

EMPNO     DEPTNO    TOT_SAL
---------------------------
7782
7839
7934
Total for deptno 10 = 8750
7369
7566
7788
7876
7902
Total for deptno 20 = 10875
7499
7521
7654
7698
7844
7900
Total for deptno 30 = 9400

17 rows selected.

SQL>

How model execution works?

Zilvinas, October 16, 2014 - 3:39 pm UTC

Hello,

I think Model Clause is working this way:
first load all data into array in the memory,
then executes rules
and finally outputs results.

But what if my query will return huge amount of data?
It will start swapping to disk and performance will be killed.

Theoreticaly it is possible to stream data and not to load whole array into memory of course if rules has no correlation between lines(dimension)

That would be very attractive for data warehouses to write mapping logic using model clause.
Somthing like this:

SELECT *
FROM Some_Stage_Table
MODEL REFERENCE Universal_Map ON (SELECT Clasificator_Name, Clasificator_Num_Val, Clasificator_Str_Val FROM Universal_Classificator)
DIMENSION BY (Clasificator_Name, Clasificator_Str_Val)
MEASURES (Clasificator_Num_Val)
MAIN ETL
DIMESNION BY ROWNUM rn
MEASURES (Field1, ..., NULL Mapped_Field1, ...)
RULES(
Mapped_Field_1 = Universal_Map.Clasificator_Num_Val['CLASSIFICATOR_NAME_1', Field_1[CV()]],
Mapped_Field_2 = Universal_Map.Clasificator_Num_Val['CLASSIFICATOR_NAME_2', Field_2[CV()]],
.........................................,
Mapped_Field_N = Universal_Map.Clasificator_Num_Val['CLASSIFICATOR_NAME_N', Field_2[CV()]]
)

This method seems to be better than

SELECT *
FROM Some_Stage_Table t
LEFT JOIN Universal_Classificator cl_1 on Clasificator_Name = 'CLASSIFICATOR_NAME_1' AND Clasificator_Str_Val = t.Field_1
LEFT JOIN Universal_Classificator cl_2 on Clasificator_Name = 'CLASSIFICATOR_NAME_2' AND Clasificator_Str_Val = t.Field_2
....
LEFT JOIN Universal_Classificator cl_N on Clasificator_Name = 'CLASSIFICATOR_NAME_N' AND Clasificator_Str_Val = t.Field_N

With latter method you must join Universal_Classificator n times to stage table.
When classificator is big it not seems attractive to hash-join it N times, using indexes also do not seems attractive.

Model Clause on the other hand only one full scans classificator table to load it into memory.
It is something like multidimensional hash join. And it wokrs perfectly ... until data gets big :(
When cursor do not fits in memory it slows down terribly and becomes not even an option.

Using PL/SQL for ETL is order of magnitude slower than (insert .. select approach) even using in-memory index-by tables or result cashed functions.
Besides index-by tables are indexed only by one field. Of cause you can concatenate few fields into one varchar, but it is ugly and has many other drawbacks.
With model clause it is possible to write INSERT /*+ append*/... FROM SELECT ... MODEL ... RULES (a = map(...), b = map(...)) in verry readable(not worse than PL/SQL) fashion.
And I believe it would be faster even than bulk collect, map using result-cashed funcions and bulk insert approach.

If it is possible for Oracle Model clause to stream data?
Or maybe in next version it will be?
It would be VERY VERY VERY nice.



UPSERT and RETURN UPDATED ROWS

Asim, September 15, 2022 - 11:02 pm UTC

In MODEL clause, instead of

RETURN {ALL|UPDATED} ROWS
where UPDATED means inserted and updated rows both

wouldnt this be better

RETURN {ALL|UPDATED|INSERTED|UPSERTED} ROWS

where
Updated means only updated not inserted
inserted means only inserted not updated
upserted means both updated and inserted

Also in RULES oracle means only update by UPDATE,and, means update and insert by UPSERT.

I know RETURN.. and RULES are different things
but language must be consistent ie meaning of the word UPDATE must be consistent, ie should have same intutive meaning in both RETURN and RULES.

I request all of you for your comments.