## Question and Answer

## 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

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>

see

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

## Rating

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

# Comments

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?

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?

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,

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,

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

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.

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?

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?

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

probably

comes close to what you want (?)

but really: what is the goal here ?

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 ?

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 !

(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 !

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

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

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.

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.

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=

:)

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=

:)

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....)

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

Some other useful links are here:

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:21685363725298#4465476000346497689

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.

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

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

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>

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

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

Hi Tom,

I have a query like as below

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 ...

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 ...

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.

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>

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.

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.

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.

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.