--1 create mv log
drop MATERIALIZED VIEW LOG ON target;
drop MATERIALIZED VIEW LOG ON targetextension;
drop MATERIALIZED VIEW LOG ON brand;
CREATE MATERIALIZED VIEW LOG ON target with rowid, sequence(id);
CREATE MATERIALIZED VIEW LOG ON targetextension with rowid, sequence(targetid,brandid,EmailPermission,NumberOfAllOrders);
CREATE MATERIALIZED VIEW LOG ON brand with rowid, sequence(brandid, brandname);

--2 create the first mv and it's log
drop MATERIALIZED VIEW mv_target1;
CREATE MATERIALIZED VIEW mv_target1
REFRESH FAST START WITH SYSDATE 
NEXT SYSDATE + 1/1440
AS   
select t1.rowid t1_rowid, t2.rowid t2_rowid, t3.rowid t3_rowid, 
       t1.id targetid,
       t3.brandname,
       t2.emailPermission, 
       t2.numberOfAllOrders
from target t1, targetextension t2, brand t3
where t1.id=t2.targetid(+)
and t2.brandid=t3.brandid(+);
  
drop MATERIALIZED VIEW LOG ON mv_target1;
CREATE MATERIALIZED VIEW LOG ON mv_target1
WITH ROWID, SEQUENCE(t1_rowid, t2_rowid, t3_rowid, targetid, brandname, emailPermission, numberOfAllOrders) 
INCLUDING NEW VALUES;

--3 create the second mv
drop MATERIALIZED VIEW mv_target2;
CREATE MATERIALIZED VIEW mv_target2
REFRESH FAST START WITH SYSDATE 
NEXT  SYSDATE + 1/1440
AS 
select * from mv_target1
pivot(max(EmailPermission) EmailPermission, max(NumberOfAllOrders) NumberOfAllOrders for brandName in ('XXX' XXX,'YYY' YYY ,'ZZZ' ZZZ)); 

declare
  lv_sqltext varchar2(4000);
begin
  execute immediate 'truncate table mv_capabilities_table';
  lv_sqltext := 'select * from mv_target1
pivot(max(EmailPermission) EmailPermission, max(NumberOfAllOrders) NumberOfAllOrders for brandName in (''XXX'' XXX,''YYY'' YYY ,''ZZZ'' ZZZ))';  
  dbms_mview.explain_mview(lv_sqltext,'nested=>TRUE');
  commit;
end;
/

create table target
(
  id        number,
  custName varchar2(50)
);
alter table target add constraint PK_target_id primary key (id);

create table Targetextension
(
  targetid          number,
  brandid           number,
  EmailPermission   number,
  NumberOfAllOrders number
);
alter table Targetextension add constraint PK_targetExt_id primary key (targetid);

create table brand
(
  brandID    number,
  brandName varchar2(50)   
);
alter table brand add constraint PK_brand_id primary key (brandid);

alter table Targetextension
  add constraint FK_targetExt_brandid foreign key (brandid)
  references brand(brandid);
-- Grant/Revoke object privileges 

insert into target values(1001,'Tom');
insert into target values(1002,'Julia');
insert into target values(1003,'Adam');
commit;

insert into brand values(3001, 'XXX');
insert into brand values(3002, 'YYY');
insert into brand values(3003, 'ZZZ');
commit;

insert into Targetextension values(1001,3001,11,10);
insert into Targetextension values(1002,3002,22,20);
commit;

select * from target;
select * from Targetextension;
select * from brand;

create table empX as select * from scott.emp;
alter table empX add constraint PK_empX_empno primary key (empno);

--drop  MATERIALIZED VIEW LOG ON empX;
CREATE MATERIALIZED VIEW LOG ON empX with rowid, sequence(empno);

--drop MATERIALIZED VIEW mv_empX;
CREATE MATERIALIZED VIEW mv_empX
REFRESH FAST START WITH SYSDATE
NEXT  SYSDATE + 1/1440
AS   
  select * from
  (
   select rowid emp_rowid, deptno, job, sal from empX
  )
  PIVOT( max(sal) for job IN ('ANALYST' job1, 'CLERK' job2, 'MANAGER' job3));
 
--select * from mv_capabilities_table
declare
  lv_sqltext varchar2(4000);
begin
  execute immediate 'truncate table mv_capabilities_table';
  lv_sqltext := 'select * from
  (
   select deptno, job, sal from empX
  )
  PIVOT( max(sal) for job IN (''ANALYST'' job1, ''CLERK'' job2, ''MANAGER'' job3))
  ';  
  dbms_mview.explain_mview(lv_sqltext,'nested=>TRUE');
  commit;
end;
/

SQL> create table empX as select * from scott.emp;

Table created.

SQL> alter table empX add constraint PK_empX_empno primary key (empno);

Table altered.

SQL> CREATE MATERIALIZED VIEW mv_empX
  2  REFRESH complete START WITH SYSDATE
  3  NEXT  SYSDATE + 1/1440
  4  AS   
  5    select * from
  6    (
  7     select rowid emp_rowid, deptno, job, sal from empX
  8    )
  9    PIVOT( max(sal) for job IN ('ANALYST' job1, 'CLERK' job2, 'MANAGER' job3));

Materialized view created.