Book-a-munute
Mikito Harakiri, February 23, 2004 - 12:54 pm UTC
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,
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?
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.
LMGTFY
Steve H, June 25, 2012 - 10:51 am UTC
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
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 ...
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.