Date Time Related Data Types And Functions In Oracle

Developers always use time related data types in their applications to audit information. Oracle give additional functionality supplied functions and data types. One of my previous posts i mentioned how to use interval data types. Now i want to give some examples how to use some of them.
First it is possible to convert interval data from string values:
SQL> SELECT to_yminterval('02-10') FROM dual;

TO_YMINTERVAL('02-10')
----------------------
+000000002-10

SQL> SELECT to_dsinterval('02 01:00:30') FROM dual;

TO_DSINTERVAL('0201:00:30')
---------------------------
+000000002 01:00:30

Second, i think more powerful, convert datetime strings from external programing languages such as C# or Java, Oracle timestamps and manipulate easiliy with them. Suppose you want to convert format of a datetime string. ( '23:12:54.899 +02:00 Thu Nov 29 2001' to '011129' )With oracle's supplied functions you are not need to make conversions such find-replace. Look at example below:

SQL> SELECT tz_offset('Asia/Istanbul') FROM dual;

TZ_OFFSET('ASIA/ISTANBUL')
--------------------------
+02:00

SQL> SELECT to_timestamp_tz('23:12:54.899 +02:00 Thu Nov 29 2001','HH24:MI:SS.FF3 TZH:TZM DY Mon DD YYYY') FROM dual;

TO_TIMESTAMP_TZ('23:12:54.899+'
-------------------------------------------------
29-NOV-01 11.12.54.899000000 PM +02:00

SQL> SELECT to_char( to_timestamp_tz('23:12:54.899 +02:00 Thu Nov 29 2001','HH24:MI:SS.FF3 TZH:TZM DY Mon DD YYYY'), 'YYMMDD') FROM dual;

TO_CHAR(TO_TIMESTAMP_TZ('23:12'
------------------------------
011129

Rows To Columns Or Pivot Table with SQL Model Clause In Oracle

A few days ago a question was asked in oracleturk mail group. It was about how to convert rows into columns. In my previous post, i have showed how to convert rows to columns. Now i studied SQL Model clause...

Demonstration below shows how to achieve it with Oracle's Model Clause.
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS


SQL>
SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0    Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> drop table test;

Table dropped

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

SQL>

System Triggers In Oracle

One of the features of Oracle database is to create global triggers called system triggers. They are executed in spesific situations. For more information check docs.
Below a simple demonstration that shows how to create this type of triggers. A LOGON trigger created to audit logins of users:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS


SQL>
SQL> drop table user_logs;

Table dropped

SQL> create table user_logs( osuser varchar2(32), datetime date );

Table created

SQL> CREATE OR REPLACE TRIGGER log_on_trg
2 AFTER logon ON hr.SCHEMA
3 BEGIN
4 INSERT INTO user_logs
5 VALUES
6 ((SELECT distinct ss.OSUSER FROM v$session ss WHERE ss.SID = userenv('sid')), SYSDATE);
7 END;
8 /

Trigger created

SQL> show err;
No errors for TRIGGER SYS.LOG_ON_TRG

SQL> conn hr/hr;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> conn sys/0000@XE as sysdba;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS

SQL> SELECT * FROM user_logs;

OSUSER                            DATETIME
-------------------------------- -----------
DELL-\Administrator               28.11.2006
DELL-\Administrator               28.11.2006

SQL>

Handling Deadlocks With SELECT FOR UPDATE SKIP LOCKED In Oracle

In my previous post I have mentioned sample deadlock scenario in Oracle database.
For data integrity, oracle locks the row which you tend to update with FOR UPDATE keywords in SELECT statement. There is an undocumented feature of oracle that skips locked rows. It can be useful to pevent deadlocks. Look at example below:


Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as SYS

SQL> drop table a;

Table dropped

SQL> create table a( x number, y varchar2(1) );

Table created

SQL> INSERT INTO a VALUES (1, 'X');

1 row inserted

SQL> INSERT INTO a VALUES (2, 'Y');

1 row inserted

SQL> commit;

Commit complete

SQL>

------

--SESSION1
SQL> SELECT * FROM a WHERE x = 1 for update;
X          Y
---------- -
1          X

--SESSION2
SQL> SELECT * FROM a WHERE x = 2 for update;
X          Y
---------- -
2          Y

SQL> SELECT * FROM a WHERE x = 1 for update;
--Now SESSION2 waits...

--SESSION1
SQL> SELECT * FROM a WHERE x = 2 for update;
X          Y
---------- -
2          Y
--Now SESSION1 also waits...

--SESSION2
--Oracle selects SESSION2 victim and terminate.

SELECT * FROM a WHERE x = 1 for update

ORA-00060: deadlock detected while waiting for resource

Other is using SKIP LOCKED as described below:
--SESSION1
SQL> SELECT * FROM a WHERE x = 1 for update skip locked;
X          Y
---------- -
1          X

--SESSION2
SQL> SELECT * FROM a WHERE x = 2 for update skip locked;
X          Y
---------- -
2          Y

SQL> SELECT * FROM a WHERE x = 1 for update skip locked;
--No WAIT. But return no rows!
X          Y
---------- -

--SESSION1
SQL> SELECT * FROM a WHERE x = 2 for update skip locked;
--No WAIT. But return no rows again!
X          Y
---------- -

Large Objects as BLOB, CLOB In Oracle Database

In Oracle database users are allowed to manipulate and store binary objects like images or files. It is a very powerful side of Oracle that use SQL and PL/SQL language to play with binary data. In large systems only small data types such as VARCHAR2 are not satisfy requirements. Only 4000 bytes of characters can be stored in Oracle in CHAR types. If requirements are met, it can be possible to use extended data types as objects.

There is a small demostration that shows simply how to play with BLOBs:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS

SQL>
SQL> drop table docs;
Table dropped
SQL> create table docs( id number, content clob, add_date date);
Table created
SQL> INSERT INTO docs VALUES (1, empty_clob(), SYSDATE);
1 row inserted
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM DOCS;
ID         CONTENT                                 ADD_DATE
---------- --------------------------------------- -----------
1                                                  28.11.2006
SQL> DECLARE
  2    blob_pointer     CLOB;
  3    temp_string      VARCHAR2(1024);
  4    length_of_string NUMBER;
  5    length_of_blob   NUMBER;
  6  BEGIN
  7    SELECT content INTO blob_pointer FROM docs WHERE id = 1 FOR UPDATE;
  8    temp_string      := 'aassddffggh';
  9    length_of_string := length(temp_string);
 10    dbms_lob.OPEN(blob_pointer, dbms_lob.lob_readwrite);
 11    dbms_lob.WRITE(blob_pointer, length_of_string, 1, temp_string);
 12    length_of_blob := dbms_lob.getlength(blob_pointer);
 13 
 14    temp_string := NULL;
 15    dbms_lob.READ(blob_pointer, length_of_blob, 1, temp_string);
 16    dbms_output.put_line('Length of the BLOB is ' || length_of_blob);
 17    dbms_output.put_line('Content of BLOB is    ' || temp_string);
 18 
 19    temp_string      := 'qqwweerrtt';
 20    length_of_string := length(temp_string);
 21    dbms_lob.writeappend(blob_pointer, length_of_string, temp_string);
 22    length_of_blob := dbms_lob.getlength(blob_pointer);
 23 
 24    temp_string := NULL;
 25    dbms_lob.READ(blob_pointer, length_of_blob, 1, temp_string);
 26    dbms_output.put_line('Length of the BLOB is ' || length_of_blob);
 27    dbms_output.put_line('Content of BLOB is    ' || temp_string);
 28 
 29    dbms_lob.CLOSE(blob_pointer);
 30    COMMIT;
 31  END;
 32  /
Length of the BLOB is 11
Content of BLOB is    aassddffggh
Length of the BLOB is 21
Content of BLOB is    aassddffgghqqwweerrtt
PL/SQL procedure successfully completed
SQL> SELECT * FROM docs;
ID         CONTENT                                 ADD_DATE
---------- --------------------------------------- -----------
1          aassddffgghqqwweerrtt                   28.11.2006
SQL>

ROWNUM and Aggregate Functions

When working on aggregate functions, you must be aware of rownum. Resultset firstly taken with rownum and then aggregate functions are processed.
Look at example below:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL>
SQL> drop table testtab;
Table dropped
SQL> CREATE TABLE testtab AS  SELECT o.object_name n, mod(rownum, 5) i, rownum r FROM all_objects o WHERE rownum < 11;
Table created
SQL> SELECT * FROM testtab;
N                                       I          R
------------------------------ ---------- ----------
ICOL$                                   1          1
I_USER1                                 2          2
CON$                                    3          3
UNDO$                                   4          4
C_COBJ#                                 0          5
I_OBJ#                                  1          6
PROXY_ROLE_DATA$                        2          7
I_IND1                                  3          8
I_CDEF2                                 4          9
I_PROXY_ROLE_DATA$_1                    0         10
10 rows selected
SQL> SELECT * FROM testtab WHERE i = 0;
N                                       I          R
------------------------------ ---------- ----------
C_COBJ#                                 0          5
I_PROXY_ROLE_DATA$_1                    0         10
SQL> SELECT max(r) FROM testtab WHERE i = 0 and rownum = 1;
    MAX(R)
----------
         5
SQL> SELECT max(r) FROM testtab WHERE i = 0;
    MAX(R)
----------
        10
SQL> SELECT * FROM (SELECT max(r) FROM testtab WHERE i = 0)  WHERE rownum = 1;
    MAX(R)
----------
        10
SQL>

Performance Analyze Of Query ReWrite and Pre Aggregated or Joined Materialized Views In Oracle

In large datawarehouse systems queries take long time to execute, generally. To solve these problems Oracle has improved its queries and tools. One of them is materialized views as called MVs. Main idea of this MVs are to pre calculate or join tables. Data has formed and stored inside MVs in database, like an object such as  a table.

Suppose that you want to get average of a table that has two millions of records. As you predict, it is a long query. When you create a MVs to store averages, it will be very esay to read from MV instead of calculating all two millions of data. When a modification has performed on that table, MV has to update itself. This is a very comon use of MVs in Oracle.

Query rewrite takes place on calling this precalculated average. When you specify on create materialized view statement to enable query rewrite, before execution, it will be checked whether it can be rewrited. If so, instead of calculating again, precalculated table -MV- will selected.

For joining you may create a materialized view log to access the tables records.


To make more clear, there is demonstration below:



SQL> DROP TABLE base_table;
DROP TABLE base_table
ORA-00942: table or view does not exist
SQL> CREATE TABLE base_table AS
2 SELECT MOD(rownum, 20) id, round(dbms_random. VALUE(1000, 100000)) tot
3 FROM all_objects, (SELECT * FROM all_tab_cols WHERE ROWNUM < 4);
Table created
Executed in 47,703 seconds
SQL> DROP TABLE lookup_table;
DROP TABLE lookup_table
ORA-00942: table or view does not exist
SQL> CREATE TABLE lookup_table AS
2 SELECT MOD( rownum, 20) id, object_type nam FROM all_objects WHERE rownum < 20 ;
Table created
Executed in 0,672 seconds

Inserting without MV with "REFRESH FAST ON COMMIT"
SQL> INSERT INTO base_table
2 SELECT MOD(rownum, 20) id, round(dbms_random.VALUE(1000, 100000)) tot
3 FROM all_objects;
22109 rows inserted
Executed in 49,032 seconds
SQL> commit;
Commit complete
Executed in 0,016 seconds

Without MV a query
SQL> SELECT l.nam, AVG(b.tot)
2 FROM base_table b, lookup_table l
3 WHERE b.id = l.id
4 GROUP BY l.nam;
NAM AVG(B.TOT)
------------------- ----------
CLUSTER 50766,7546
INDEX 50567,1634
TABLE 50658,3567
Executed in 0,984 seconds

SQL> CREATE MATERIALIZED VIEW LOG ON base_table WITH SEQUENCE, ROWID
2 (id,tot)
3 INCLUDING NEW VALUES;
Materialized view log created
Executed in 0,5 seconds
SQL> CREATE MATERIALIZED VIEW LOG ON lookup_table WITH SEQUENCE, ROWID
2 (id,nam)
3 INCLUDING NEW VALUES;
Materialized view log created
Executed in 0,156 seconds
SQL> DROP MATERIALIZED VIEW base_lookup_mv;
DROP MATERIALIZED VIEW base_lookup_mv
ORA-12003: materialized view "SYSADM"."BASE_LOOKUP_MV" does not exist

Creating MV with given options
SQL> CREATE MATERIALIZED VIEW base_lookup_mv
2 PARALLEL
3 BUILD IMMEDIATE
4 REFRESH FAST ON COMMIT
5 ENABLE QUERY REWRITE AS
6 SELECT l.nam
7 ,COUNT(b.tot) count_tot
8 ,SUM(b.tot) sum_tot
9 ,AVG(b.tot) avg_tot
10 FROM base_table b, lookup_table l
11 WHERE b.id = l.id
12 GROUP BY l.nam;
Materialized view created
Executed in 3,297 seconds

Selecting v$parameter view to see values of query_rewrite:
SQL> SELECT name, value FROM v$parameter WHERE name like 'query_rewrite_%';
NAME VALUE
-------------------------------------------------------------------------------- -----------------
query_rewrite_enabled TRUE
query_rewrite_integrity enforced
Executed in 0,031 seconds

Query With MV
SQL> SELECT l.nam, AVG(b.tot)
2 FROM base_table b, lookup_table l
3 WHERE b.id = l.id
4 GROUP BY l.nam;
NAM AVG(B.TOT)
------------------- ----------
INDEX 50567,1634
TABLE 50658,3567
CLUSTER 50766,7546
Executed in 0,422 seconds

Inserting with MV
SQL> INSERT INTO base_table
2 SELECT MOD(rownum, 20 ) id, round(dbms_random.VALUE(1000, 100000)) tot
3 FROM all_objects;
22114 rows inserted
Executed in 71,829 seconds
SQL> commit ;
Commit complete
Executed in 8,672 seconds

As you see, queries become faster but inserts become slower. As a result, for OLTP based systems it is not a good idea to use MVs. With 3 times executions performance results (in ms) are below:
Normal Insert : 60
MatViw Insert : 70
Normal Select : 1,015
MatViw Select : 0.557

Lets look at execution plans:
SQL> explain plan set statement_id = 'menn' for
2 SELECT l.nam, AVG(b.tot)
3 FROM base_table b, lookup_table l
4 WHERE b.id = l.id
5 GROUP BY l.nam;
Explained
SQL> @E:\oracle\product\10.2.0\db_2\rdbms\admin\utlxpls.sql menn
Cannot SET MARKUP
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2191832405
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 72 | 2
| 1 | PX COORDINATOR | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 3 | 72 | 2
| 3 | PX BLOCK ITERATOR | | 3 | 72 | 2
| 4 | MAT_VIEW REWRITE ACCESS FULL| BASE_LOOKUP_MV | 3 | 72 | 2
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
15 rows selected
SQL> DROP MATERIALIZED VIEW base_lookup_mv;
Materialized view dropped
SQL>
SQL> explain plan set statement_id = 'menn' for
2 SELECT l.nam, AVG(b.tot)
3 FROM base_table b, lookup_table l
4 WHERE b.id = l.id
5 GROUP BY l.nam;
Explained
SQL> @E:\oracle\product\10.2.0\db_2\rdbms\admin\utlxpls.sql menn
Cannot SET MARKUP
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1936153920
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107K| 5268K| 110 (46)| 00:00:
| 1 | SORT GROUP BY | | 107K| 5268K| 110 (46)| 00:00:
|* 2 | HASH JOIN | | 107K| 5268K| 73 (18)| 00:00:
| 3 | TABLE ACCESS FULL| LOOKUP_TABLE | 19 | 456 | 3 (0)| 00:00:
| 4 | TABLE ACCESS FULL| BASE_TABLE | 113K| 2883K| 64 (11)| 00:00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."ID"="L"."ID")
Note
-----
- dynamic sampling used for this statement
20 rows selected
SQL>
As it is seen with MV although you write the same statement, Oracle rewrites the query to use MV.

Notes On SQL MODEL Clause In Oracle

Oracle has introduced a very powerful mechanism called SQL Model to improve performance on SQL statements. Main idea of SQL Model clause is, to make possible some complex queries via SQL statements.

Model is based on arrays. MEASURES keyword identifies which arrays will be used. Indice of arrays are iddentified in DIMENSION BY statement. As a result, you can make array-based calculations in SQL easily.

In Model clause, business rules are taken into SQL. It is possible not to use a procedural language with model clause. One example of this can be found at the end of this entry with fibonacci numbers.

To make clear understanding of Model clause it'd better make some exercises:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> DROP TABLE worker;

Table dropped

SQL> CREATE TABLE worker( id number primary key, name varchar2(32), salary number);

Table created

SQL> INSERT INTO worker VALUES (1, 'Mennan', 1200);

1 row inserted

SQL> INSERT INTO worker VALUES (2, 'Ali', 1500);

1 row inserted

SQL> INSERT INTO worker VALUES (3, 'Selim', 900);

1 row inserted

SQL> INSERT INTO worker VALUES (4, 'Ayse', 1450);

1 row inserted

SQL> INSERT INTO worker VALUES (5, 'Seyyah', 2900);

1 row inserted

SQL> SELECT *
  2    FROM worker wr
  3  MODEL
  4  DIMENSION BY(wr.id i)
  5  MEASURES(wr.NAME n, wr.salary sa)
  6  RULES();

         I N                                        SA
---------- -------------------------------- ----------
         1 Mennan                                 1200
         2 Ali                                    1500
         3 Selim                                   900
         4 Ayse                                   1450
         5 Seyyah                                 2900

In example above  n[i] descr,bes ith worker's name and sa[i] describes ith worker's salary. For instance n[1] is Mennan, sa[4] is 1450

SQL> SELECT *
  2    FROM worker wr
  3  MODEL
  4  DIMENSION BY(wr.id i)
  5  MEASURES(wr.NAME n, wr.salary sa)
  6  RULES(
  7  sa[any] = sa[cv()] + 10000 );

         I N                                        SA
---------- -------------------------------- ----------
         1 Mennan                                11200
         2 Ali                                   11500
         3 Selim                                 10900
         4 Ayse                                  11450
         5 Seyyah                                12900

CV() only can be used RIGHT-HAND SIDE and ANY only can be used LEFT-HAND SIDE of operators. sa[any] describes all employes' salaries.

SQL> SELECT *
  2    FROM worker wr
  3  MODEL
  4  DIMENSION BY(wr.id i)
  5  MEASURES(wr.NAME n, 0 temp, wr.salary sa)
  6  RULES(
  7  temp[any] = sa[cv()] + 10000 );

         I N                                      TEMP         SA
---------- -------------------------------- ---------- ----------
         1 Mennan                                11200       1200
         2 Ali                                   11500       1500
         3 Selim                                 10900        900
         4 Ayse                                  11450       1450
         5 Seyyah                                12900       2900

It is possible to use temporary arrays in MODEL clause.

SQL> SELECT *
  2    FROM worker wr
  3  MODEL
  4  DIMENSION BY(wr.id i)
  5  MEASURES(wr.NAME n, wr.salary sa, cast('' as varchar2(32)) names )
  6  RULES(
  7  names[any] =  n[cv()] || '....' );

         I N                                        SA NAMES
---------- -------------------------------- ---------- --------------------------------
         1 Mennan                                 1200 Mennan....
         2 Ali                                    1500 Ali....
         3 Selim                                   900 Selim....
         4 Ayse                                   1450 Ayse....
         5 Seyyah                                 2900 Seyyah....

It is also possible to use string arrays with CAST operator.

SQL> SELECT *
  2    FROM worker wr
  3  MODEL
  4  DIMENSION BY(wr.id i)
  5  MEASURES(wr.NAME n, wr.salary sa, cast('' as varchar2(32)) names )
  6  RULES ITERATE( 2 )(
  7  names[any] = names[CV()] || n[cv()] || '....' );

         I N                                        SA NAMES
---------- -------------------------------- ---------- --------------------------------
         1 Mennan                                 1200 Mennan....Mennan....
         2 Ali                                    1500 Ali....Ali....
         3 Selim                                   900 Selim....Selim....
         4 Ayse                                   1450 Ayse....Ayse....
         5 Seyyah                                 2900 Seyyah....Seyyah....

Iteration can be used in RULE section.

SQL> SELECT *
  2    FROM worker wr
  3  MODEL IGNORE NAV
  4  DIMENSION BY(wr.id i)
  5  MEASURES( wr.salary sa)
  6  RULES UPSERT
  7  (
  8    sa[99999] = MAX(sa)[i BETWEEN 1 AND 3]
  9  );

         I         SA
---------- ----------
         4       1450
         5       2900
         1       1200
         2       1500
         3        900
     99999       1500

6 rows selected

UPSERT keyword is used for getting all rows. Some aggregate functions can be used in Model clause.

Example below shows how to calculate fibonacci series.

SQL> SELECT fi
  2    FROM dual
  3  MODEL
  4  DIMENSION BY( 1 i)
  5  MEASURES(1 b1, 1 b2, 0 fi )
  6  RULES ITERATE( 5 )(
  7  fi[any] = b1[cv()] + b2[cv()],
  8  b2[any] = b1[cv()],
  9  b1[any] = fi[cv()]
 10  );

        FI
----------
        13

Usage of OUTER JOIN PARTITION BY In Oracle

It is possible to make an outer join with partitions in Oracle. Suppose that you have a table that contains workers' work days. If a worker did not work, there is no record in that table, working_time table. If your boss want you to prepare a sheet that contains all workers' working times. You have to put all day entries. If a worker did not work, you simple add "0" working hours for worker. How can you achieve this situation?
Follow the example....

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL>
SQL> DROP TABLE working_time;

DROP TABLE working_time

ORA-00942: table or view does not exist

SQL> DROP TABLE worker;

DROP TABLE worker

ORA-00942: table or view does not exist

SQL> CREATE TABLE worker( id number primary key, name varchar2(32), salary number);

Table created

SQL> INSERT INTO worker VALUES (1, 'Mennan', 1200);

1 row inserted

SQL> INSERT INTO worker VALUES (2, 'Ali', 1500);

1 row inserted

SQL> INSERT INTO worker VALUES (3, 'Selim', 900);

1 row inserted

SQL> INSERT INTO worker VALUES (4, 'Ayse', 1450);

1 row inserted

SQL> INSERT INTO worker VALUES (5, 'Seyyah', 2900);

1 row inserted

SQL> commit;

Commit complete

SQL> SELECT * FROM worker;

ID NAME SALARY
---------- -------------------------------- ----------
1 Mennan 1200
2 Ali 1500
3 Selim 900
4 Ayse 1450
5 Seyyah 2900

SQL> CREATE TABLE working_time( worker_id number references worker, working_date date, working_hour number );

Table created

SQL> INSERT INTO working_time VALUES(1, to_date('01.11.2006','DD.MM.YYYY'), 4);

1 row inserted

SQL> INSERT INTO working_time VALUES(1, to_date('02.11.2006','DD.MM.YYYY'), 8);

1 row inserted

SQL> INSERT INTO working_time VALUES(1, to_date('05.11.2006','DD.MM.YYYY'), 8);

1 row inserted

SQL> INSERT INTO working_time VALUES(5, to_date('01.11.2006','DD.MM.YYYY'), 8);

1 row inserted

SQL> INSERT INTO working_time VALUES(5, to_date('02.11.2006','DD.MM.YYYY'), 8);

1 row inserted

SQL> INSERT INTO working_time VALUES(5, to_date('03.11.2006','DD.MM.YYYY'), 8);

1 row inserted

SQL> INSERT INTO working_time VALUES(5, to_date('04.11.2006','DD.MM.YYYY'), 6);

1 row inserted

SQL> INSERT INTO working_time VALUES(3, to_date('02.11.2006','DD.MM.YYYY'), 6);

1 row inserted

SQL> INSERT INTO working_time VALUES(3, to_date('05.11.2006','DD.MM.YYYY'), 8);

1 row inserted

SQL> commit;

Commit complete

SQL> SELECT * FROM working_time;

WORKER_ID WORKING_DATE WORKING_HOUR
---------- ------------ ------------
1 01.11.2006 4
1 02.11.2006 8
1 05.11.2006 8
5 01.11.2006 8
5 02.11.2006 8
5 03.11.2006 8
5 04.11.2006 6
3 02.11.2006 6
3 05.11.2006 8

9 rows selected

SQL> SELECT * FROM worker;

ID NAME SALARY
---------- -------------------------------- ----------
1 Mennan 1200
2 Ali 1500
3 Selim 900
4 Ayse 1450
5 Seyyah 2900

SQL> SELECT wr.name, wt.working_date, wt.working_hour FROM working_time wt, worker wr WHERE wt.worker_id = wr.id;

NAME WORKING_DATE WORKING_HOUR
-------------------------------- ------------ ------------
Mennan 01.11.2006 4
Mennan 02.11.2006 8
Mennan 05.11.2006 8
Seyyah 01.11.2006 8
Seyyah 02.11.2006 8
Seyyah 03.11.2006 8
Seyyah 04.11.2006 6
Selim 02.11.2006 6
Selim 05.11.2006 8

9 rows selected

SQL> SELECT inn.NAME, tim.time_day, nvl(inn.working_hour, 0) working_hour
2 FROM (SELECT wr.NAME, wt.working_date, wt.working_hour
3 FROM working_time wt, worker wr
4 WHERE wt.worker_id = wr.id) inn PARTITION BY(inn.NAME)
5 RIGHT OUTER JOIN (SELECT to_date('31.10.2006', 'DD.MM.YYYY') + rownum time_day
6 FROM all_objects
7 WHERE rownum <=
8 (SYSDATE - to_date('31.10.2006', 'DD.MM.YYYY'))) tim ON (inn.working_date =
9 tim.time_day)
10 ORDER BY inn.NAME, tim.time_day;

NAME TIME_DAY WORKING_HOUR
-------------------------------- ----------- ------------
Mennan 01.11.2006 4
Mennan 02.11.2006 8
Mennan 03.11.2006 0
Mennan 04.11.2006 0
Mennan 05.11.2006 8
Mennan 06.11.2006 0
Mennan 07.11.2006 0
Mennan 08.11.2006 0
Mennan 09.11.2006 0
Mennan 10.11.2006 0
Mennan 11.11.2006 0
Mennan 12.11.2006 0
Mennan 13.11.2006 0
Mennan 14.11.2006 0
Selim 01.11.2006 0
Selim 02.11.2006 6
Selim 03.11.2006 0
Selim 04.11.2006 0
Selim 05.11.2006 8
Selim 06.11.2006 0
Selim 07.11.2006 0
Selim 08.11.2006 0
Selim 09.11.2006 0
Selim 10.11.2006 0
Selim 11.11.2006 0
Selim 12.11.2006 0
Selim 13.11.2006 0
Selim 14.11.2006 0
Seyyah 01.11.2006 8
Seyyah 02.11.2006 8
Seyyah 03.11.2006 8
Seyyah 04.11.2006 6
Seyyah 05.11.2006 0
Seyyah 06.11.2006 0
Seyyah 07.11.2006 0
Seyyah 08.11.2006 0
Seyyah 09.11.2006 0
Seyyah 10.11.2006 0
Seyyah 11.11.2006 0
Seyyah 12.11.2006 0
Seyyah 13.11.2006 0

NAME TIME_DAY WORKING_HOUR
-------------------------------- ----------- ------------
Seyyah 14.11.2006 0

42 rows selected

SQL> DROP TABLE working_time;

Table dropped

SQL> DROP TABLE worker;

Table dropped

SQL>