SQL Snippets ™: Tutorials for Oracle Developers

In this tutorial we explore the RETURN ROWS component of the MODEL clause. With it you can control whether all rows selected or only those rows updated by model rules are returned by the query.

RETURN ALL ROWS

RETURN ALL ROWS is the default behaviour for MODEL queries. It causes the query to returns all rows selected whether they were updated by a rule or not.

break on group_1 skip 1 duplicates

select key, row_status
from   t
model
  RETURN ALL ROWS
  dimension by ( key )
  measures     ( cast( 'x' as varchar2(10) ) as row_status )
  rules        ( row_status[key in (1,3,5,7)] = 'UPDATED' )
order by key ;
 

   KEY ROW_STATUS
------ ----------
     1 UPDATED
     2 x
     3 UPDATED
     4 x
     5 UPDATED
     6 x
     7 UPDATED
     8 x
     9 x
    10 x
 

RETURN UPDATED ROWS

RETURN UPDATED ROWS causes the query to return only those rows updated by a rule.

break on group_1 skip 1 duplicates

select key, row_status
from   t
model
  RETURN UPDATED ROWS
  dimension by ( key )
  measures     ( cast( 'x' as varchar2(10) ) as row_status )
  rules        ( row_status[key in (1,3,5,7)] = 'UPDATED' )
order by key ;
 

   KEY ROW_STATUS
------ ----------
     1 UPDATED
     3 UPDATED
     5 UPDATED
     7 UPDATED