Finishing my blog entry on SQL for Modeling, here are some examples where the results of such an example should be equivalent to a non-MODEL clause solution. These examples should give you, the reader, an idea how this new clause works by comparing it with another statement. My personal take on the MODEL clause is: Use it when you need iterations or even recursions inside an SQL statement or when you need to apply a "spreadsheet" logic to your result set.

row "generator"

-- "connect by" method

SELECT dummy c1
  FROM dual
 CONNECT BY LEVEL <= 10
;  

-- "model" method

SELECT c1
  FROM dual
 MODEL
   DIMENSION BY (0 dim)
   MEASURES     (dummy c1)
   RULES ITERATE(10) (c1[ITERATION_NUMBER] = 'X')
;

C
-
X
X
X
X
X
X
X
X
X
X

WITH t AS (SELECT 'John,Doe,"$85,256",Programmer,"$42,857"' col1
             FROM dual
            UNION
           SELECT 'Mary,Jane,"$26,586",BA,"$42,857"'
             FROM dual
            UNION
           SELECT 'Test,,Test,",1", BA,",8"'
             FROM dual
          )
SELECT t.col1
     , REGEXP_REPLACE(t.col1, '("[^,]+),*([^,"]+),*([^,"]+),*([^,"]+),*', '1234') col3
  FROM t
;

WITH clause from previous SELECT
SELECT c1
     , c3
  FROM t
 MODEL
   PARTITION BY (ROWNUM rn)
   DIMENSION BY (0 dim)
   MEASURES(col1 c1, col1 c2, col1 c3)
   RULES ITERATE(10) UNTIL (c3[0]= PREVIOUS(c3[0]))
                (c2[0] = REGEXP_SUBSTR(c1[0], '("[^"]*"|[^,]*)(,|$)', 1, ITERATION_NUMBER + 1)
                ,c3[0] = DECODE(ITERATION_NUMBER, 0, '', c3[0]) || REPLACE(SUBSTR(c2[0], 1, LENGTH(c2[0]) - 1), ',', '') || SUBSTR(c2[0], -1)
                )
;

C1                                      C3
--------------------------------------- ---------------------------------------
John,Doe,"$85,256",Programmer,"$42,857" John,Doe,"$85256",Programmer,"$42857"
Mary,Jane,"$26,586",BA,"$42,857"        Mary,Jane,"$26586",BA,"$42857"
Test,,Test,",1", BA,",8"                Test,,Test,"1", BA,"8"

WITH t AS (SELECT '10,12,20,30,30,3,12,12,56' col1
             FROM dual
          )
SELECT SUBSTR(col1
             ,DECODE(level, 1, 1, INSTR(col1,',',1,level-1)+1)
             ,DECODE(INSTR(col1,',',1,level), 0, LENGTH(col1), instr(col1,',',1,level)
             -DECODE(level, 1, 0, INSTR(col1,',',1,level-1))-1)
             ) col_new
  FROM t
  CONNECT BY level < LENGTH(REPLACE(TRANSLATE(col1,'01234567890','00000000000'),'0')) + 2
; 

WITH clause from previous SELECT
SELECT col_new
  FROM t
 MODEL
   PARTITION BY (ROWNUM rn)
   DIMENSION BY (0 dim)
   MEASURES(col1, col1 col_new)
   RULES ITERATE(99) UNTIL (ITERATION_NUMBER = LENGTH(REGEXP_REPLACE(col1[0], '[^,]')))
                (col_new[ITERATION_NUMBER] = REPLACE(REGEXP_SUBSTR(col1[0], '(^|,)[^,]*', 1, ITERATION_NUMBER+1), ','))
;

COL_NEW
-------------------------
10
12
20
30
30
3
12
12
56

WITH champ AS (SELECT 'Chelsea' team_name, 1960 year 
                 FROM dual 
                UNION ALL 
               SELECT 'Manchester United', 1961 
                 FROM dual 
                UNION ALL 
               SELECT 'Manchester United', 1962 
                 FROM dual 
                UNION ALL 
               SELECT 'Manchester United', 1963 
                 FROM dual 
                UNION ALL 
               SELECT 'Manchester United', 1964
                 FROM dual
                UNION ALL 
               SELECT 'Manchester United', 1965
                 FROM dual 
                UNION ALL 
               SELECT 'Chelsea', 1966 
                 FROM dual 
                UNION ALL 
               SELECT 'Manchester United', 1967 
                 FROM dual
                UNION ALL 
               SELECT 'Chelsea', 1968 
                 FROM dual 
                UNION ALL 
               SELECT 'Chelsea', 1969 
                 FROM dual 
                UNION ALL 
               SELECT 'Chelsea', 1970 
                 FROM dual 
                UNION ALL 
               SELECT 'Manchester United', 1971 
                 FROM dual 
                UNION ALL 
               SELECT 'Manchester United', 1972 
                 FROM dual
              )
SELECT team_name
     , MAX(consecutive_championships) consecutive_championships
     , MAX(startyear) KEEP (dense_rank first order by consecutive_championships desc) startyear
     , MAX(endyear) KEEP (dense_rank first order by consecutive_championships desc) endyear
  FROM (SELECT team_name
             , MIN(year) startyear
             , MAX(year) endyear
             , COUNT(*) consecutive_championships
          FROM (SELECT team_name
                     , year
                     , SUM(x) OVER (PARTITION BY team_name ORDER BY year) gr
                  FROM (select team_name
                             , year
                             , sign(year - lag(year,1,0) OVER (PARTITION BY team_name ORDER BY year) - 1) x
                          FROM champ
                       )
               )
         GROUP BY team_name
                , gr
       )
 GROUP BY team_name
 ORDER BY team_name
;

WITH clause from previous SELECT
SELECT team_name
     , cons_champ
     , year_start
     , year year_end
  FROM (SELECT *
          FROM champ
         MODEL
           DIMENSION BY (ROW_NUMBER() OVER (ORDER BY year) rn)
           MEASURES(year, team_name, 0 year_start, 0 cons_champ, 0 max_cons_champ)
           RULES (year_start[rn]     = DECODE(team_name[CV(rn)], team_name[CV(rn)-1], year_start[CV(rn)-1], year[CV(rn)])
                 ,cons_champ[rn]     = DECODE(team_name[CV(rn)], team_name[CV(rn)-1], cons_champ[CV(rn)-1]+1, 1)
                 ,max_cons_champ[rn] = MAX(cons_champ) OVER (PARTITION BY team_name)
                 )
       )
 WHERE max_cons_champ = cons_champ
 ORDER BY team_name DESC  
;              

TEAM_NAME         CONS_CHAMP YEAR_START   YEAR_END
----------------- ---------- ---------- ----------
Chelsea                    3       1968       1970
Manchester United          5       1961       1965

Reference Links

SQL for Modeling