create table qz_bricks ( 
  brick_id integer 
    not null 
    primary key, 
  colour   varchar2(10) 
    not null, 
  shape    varchar2(10) 
    not null, 
  weight   integer 
    not null 
)

insert into qz_bricks 
with rws as ( 
  select level id from dual 
  connect by level <= 999 
) 
  select id, case mod ( id, 3 ) 
           when 0 then 'red' 
           when 1 then 'blue' 
           when 2 then 'green' 
         end, case ceil ( id / 333 ) 
           when 1 then 'cylinder' 
           when 2 then 'cube' 
           when 3 then 'cuboid' 
         end, 
         round ( dbms_random.value ( 1, 10 ) ) 
  from   rws

commit

create materialized view qz_brick_summary 
  enable query rewrite as 
select colour,  
       shape,  
       count (*) row#,  
       grouping_id ( colour, shape )  gid 
from   qz_bricks  
group  by grouping sets ( ( colour ), ( shape ) ) 

CREATE TABLE REWRITE_TABLE( 
-- 
statement_id VARCHAR2(30),  
-- id for the query 
mv_owner VARCHAR2(30),  
-- owner of the MV 
mv_name VARCHAR2(30),  
-- name of the MV 
sequence INTEGER,  
-- sequence no of the msg 
query VARCHAR2(2000),  
-- user query 
query_block_no INTEGER,  
-- block no of the current subquery 
rewritten_txt VARCHAR2(2000),  
-- rewritten query 
message VARCHAR2(512),  
-- EXPLAIN_REWRITE msg 
pass VARCHAR2(3),  
-- rewrite pass no 
mv_in_msg VARCHAR2(30),  
-- MV in current message 
measure_in_msg VARCHAR2(30),  
-- Measure in current message 
join_back_tbl VARCHAR2(30),  
-- Join back table in message 
join_back_col VARCHAR2(30),  
-- Join back column in message 
original_cost INTEGER,  
-- Cost of original query 
rewritten_cost INTEGER,  
-- Cost of rewritten query 
flags INTEGER,  
-- associated flags 
reserved1 INTEGER,  
-- currently not used 
reserved2 VARCHAR2(10)  
-- currently not used 
) 

DECLARE 
-- the query 
  qrytext VARCHAR2(2000) := 
  'select count ( distinct colour ) colour#, 
          count ( distinct shape ) shape# 
   from   qz_bricks'; 
 
-- a target mview 
  mv  VARCHAR2(100) := 'QZ_BRICK_SUMMARY'; 
 
  idno VARCHAR2(30) :='ID1'; 
BEGIN 
  DBMS_MVIEW.EXPLAIN_REWRITE(qrytext, mv, idno); 
END; 

declare 
          
  l_data   sys.REWRITEARRAYTYPE; 
  l_rc     sys_refcursor; 
 
  l_message     varchar2(2000); 
 
-- the query 
  querytxt VARCHAR2(2000) :=  
  'select count ( distinct colour ) colour#, 
          count ( distinct shape ) shape# 
   from   qz_bricks'; 
 
 
-- a target mview 
  mv  VARCHAR2(100) := 'QZ_BRICK_SUMMARY'; 
 
begin 
  
         DBMS_MVIEW.EXPLAIN_REWRITE (querytxt, mv, l_data); 
 
         open l_rc for 
         select message 
         from table( cast( l_data as sys.REWRITEARRAYTYPE ) ) 
         order by sequence; 
 
         loop 
            fetch l_rc into l_message; 
            exit when l_rc%notfound; 
 
            dbms_output.put_line(l_message); 
         end loop; 
 
         close l_rc; 
 
end;