@ Oracle Database 10g Express Edition Release 10.2.0.1.0
SQL> create table test(id varchar2(2), des varchar2(4), t number);
Table created
SQL> INSERT INTO test values(‘A’,'a1′,12);
1 row inserted
SQL> INSERT INTO test values(‘A’,'a2′,3);
1 row inserted
SQL> INSERT INTO test values(‘A’,'a3′,1);
1 row inserted
SQL> INSERT INTO test values(‘B’,'a1′,10);
1 row inserted
SQL> INSERT INTO test values(‘B’,'a2′,23);
1 row inserted
SQL> INSERT INTO test values(‘C’,'a3′,45);
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT * FROM test;
ID DES T
– —- ———-
A a1 12
A a2 3
A a3 1
B a1 10
B a2 23
C a3 45
6 rows selected
SQL> select distinct i, A1, A2, A3
2 from test c
3 model
4 ignore nav
5 dimension by(c.id i,c.des d)
6 measures(c.t t, 0 A1, 0 A2, 0 A3)
7 rules(
8 A1[any,any] = t[cv(i),d = 'a1'],
9 A2[any,any] = t[cv(i),d = 'a2'],
10 A3[any,any] = t[cv(i),d = 'a3']
11 );
I A1 A2 A3
– ———- ———- ———-
C 0 0 45
B 10 23 0
A 12 3 1
SQL> select distinct d, A, B, C
2 from test c
3 model
4 ignore nav
5 dimension by(c.id i,c.des d)
6 measures(c.t t, 0 A, 0 B, 0 C)
7 rules(
8 A[any,any] = t[i = 'A', cv(d)],
9 B[any,any] = t[i = 'B', cv(d)],
10 C[any,any] = t[i = 'C', cv(d)]
11 );
D A B C
—- ———- ———- ———-
a1 12 10 0
a3 1 0 45
a2 3 23 0
SQL> explain plan set statement_id ‘menn’ for
2 select distinct d, A, B, C
3 from test c
4 model
5 ignore nav
6 dimension by( c.id i,c.des d)
7 measures(c.t t, 0 A, 0 B, 0 C)
8 rules(
9 A[any,any] = t[i = 'A', cv(d)],
10 B[any,any] = t[i = 'B', cv(d)],
11 C[any,any] = t[i = 'C', cv(d)]
12 );
Explained
SQL> select plan_table_output from table(dbms_xplan.display(‘plan_table’,'menn’));
PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 160770444
—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | 6 | 120 | 3 (34)| 00:00:01 |
| 1 | HASH UNIQUE | | 6 | 120 | 3 (34)| 00:00:01 |
| 2 | SQL MODEL ORDERED | | 6 | 120 | | |
| 3 | TABLE ACCESS FULL| TEST | 6 | 120 | 2 (0)| 00:00:01 |
—————————————————————————-
Note
—–
- dynamic sampling used for this statement
14 rows selected
Mennan Tekbir, OracleTURK forum sample
For a more complex example: http://technology.amis.nl/blog/index.php?p=300
This code requires Oracle Database 10g and uses the SQL Model clause to determine the first 12 elements of a Fibonacci Sequence;
SQL> select s seq
2 from dual
3 model return all rows
4 dimension by ( 0 d ) measures ( 0 s )
5 rules iterate (12) (
6 s[iteration_number ] = decode(
7 iteration_number, 0, 0, 1, 1, s[iteration_number-2]
8 ) + nvl(s[iteration_number-1],0)
9 )
10 /
SEQ
————-
0
1
1
2
3
5
8
13
21
34
55
89
12 rows selected.
Oracle® Database Data Warehousing Guide 10g Release 2 (10.2)
Chapter 21 SQL for Analysis and Reporting
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm#i1007488
Chapter 22 SQL for Modeling
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/sqlmodel.htm#sthref1855