Create materialized view

set feedback off
drop table t_;
drop materialized view t_mat_;

create table t_ (
  a number,
  b varchar2(10)
);

begin
  for i in 1 .. 1000 loop
    insert into t_ values (
      mod(i,trunc(dbms_random.value(1,i))),
      dbms_random.string('a',10));
  end loop;
end;
/


commit;

begin
  dbms_stats.gather_table_stats(
  user,'T_',
  method_opt=>'FOR ALL COLUMNS');
end;
/

create materialized view t_mat_ 
  build immediate
  refresh on commit
  enable query rewrite
  as
  select count(*),a from t_ group by(a);

begin
dbms_stats.gather_table_stats(
  user,'T_MAT_',
  method_opt=>'FOR ALL COLUMNS');
end;
/

alter session set query_rewrite_enabled=false;

explain plan for
  select count(*),a from t_ group by(a);

select * from table(dbms_xplan.display);

alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;

explain plan for
  select count(*),a from t_ group by(a);

select * from table(dbms_xplan.display);