Home » Server Options » Replication » Can nested materialized view use pivot function? and Can Pivot used in Materialized view (2 threads merged by bb) (Oracle 11g)
Can nested materialized view use pivot function? and Can Pivot used in Materialized view (2 threads merged by bb) [message #554784] Thu, 17 May 2012 06:51 Go to next message
anyoneokay
Messages: 10
Registered: September 2007
Junior Member
Hi, I have a question about nested materialized view.

Firstly, I have created 3 mv log on 3 table(target,targetextension,brand)
Secondly, I created the first mv and its log
Lastly, I created the second mv from the first mv. This time I used the pivot function, but it cannot work now.

--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)); 


Now, Here is a problem, it throws "ORA-12015: cannot create a fast refresh materialized view from a complex query"
Then I used dbms_mview.explain_mview to see the reason, and it tell me the following
REFRESH_FAST_AFTER_INSERT "inline view or subquery in FROM list not supported for this type MV"

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;
/


Can somebody help me, any suggestion will be appreciated
Re: Can nested materialized view use pivot function? [message #554787 is a reply to message #554784] Thu, 17 May 2012 07:04 Go to previous messageGo to next message
anyoneokay
Messages: 10
Registered: September 2007
Junior Member
The init code as following
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;

Can Pivot used in Materialized view [message #554849 is a reply to message #554784] Thu, 17 May 2012 22:05 Go to previous messageGo to next message
anyoneokay
Messages: 10
Registered: September 2007
Junior Member
Hi, I have a question about nested materialized view on Oracle 11g(It support pivot function, but oracle 10g not support).

When I created, it throws "ORA-12015: cannot create a fast refresh materialized view from a complex query"
Then I used dbms_mview.explain_mview to see the reason, and it tell me the following
REFRESH_FAST_AFTER_INSERT "inline view or subquery in FROM list not supported for this type MV"

Can somebody help me, any suggestion will be appreciated

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;
/


Re: Can Pivot used in Materialized view [message #554856 is a reply to message #554849] Fri, 18 May 2012 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use it in MV but not in a fast refreshed MV.
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.

Regards
Michel
Re: Can Pivot used in Materialized view [message #554859 is a reply to message #554856] Fri, 18 May 2012 01:10 Go to previous messageGo to next message
anyoneokay
Messages: 10
Registered: September 2007
Junior Member
thank you so much, but the refresh mode what I want is fast refreshed.
Do you have other suggestion?
Re: Can Pivot used in Materialized view [message #554866 is a reply to message #554859] Fri, 18 May 2012 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It can't be done with so the only suggestion I can give is do it without pivot but still with aggregate and pivot when you query.

Regards
Michel

[Edit: typo]

[Updated on: Fri, 18 May 2012 07:48]

Report message to a moderator

Re: Can Pivot used in Materialized view [message #554872 is a reply to message #554866] Fri, 18 May 2012 03:33 Go to previous message
anyoneokay
Messages: 10
Registered: September 2007
Junior Member
Thank you very much for your help, Now I know the solution.
Maybe I have to create a job, then execute a sp to complete the pivot query for replication, instead of MV
Previous Topic: Need Help - Replication breaks when subscribers are updatable
Next Topic: Book on Materialized Views (3 Merged)
Goto Forum:
  


Current Time: Thu Mar 28 14:01:35 CDT 2024