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