Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Mangesh.

Asked: November 03, 2016 - 8:58 am UTC

Last updated: June 28, 2017 - 12:35 am UTC

Version: 11gR2

Viewed 1000+ times

You Asked

Hi,

First of all apologies for not putting test case in LiveSQL. I tried but got error
ORA-06550: line 1, column 9:
PLS-00201: identifier 'SYS.DBMS_SNAPSHOT_UTL' must be declared
ORA-06550: line 1, column 9:
PL/SQL: Statement ignored

while creating materialized view. So I put DDLs in question here itself.

I have four tables, COUNTER_PARTY, COUNTER_PARTY_GROUP_ASSOC, COUNTER_PARTY_BO_ASSOC and GROUP_BO_IBO_ASSOC.

--CREATE COUNTER PARTY TABLE
CREATE TABLE COUNTER_PARTY (
COUNTER_PARTYID INTEGER NOT NULL,
PRIMARY KEY (COUNTER_PARTYID)
);

INSERT INTO COUNTER_PARTY
VALUES (1);
INSERT INTO COUNTER_PARTY
VALUES (2);
INSERT INTO COUNTER_PARTY
VALUES (3);
INSERT INTO COUNTER_PARTY
VALUES (4);
INSERT INTO COUNTER_PARTY
VALUES (5);

COMMIT;

--CREATE COUNTER PARTY GROUP CODE ASSOCIATION TABLE
CREATE TABLE COUNTER_PARTY_GROUP_ASSOC (
COUNTER_PARTYID INTEGER NOT NULL,
GROUP_CODE_ID VARCHAR2(5 CHAR) NOT NULL,
PRIMARY KEY (COUNTER_PARTYID,GROUP_CODE_ID)
);

INSERT INTO COUNTER_PARTY_GROUP_ASSOC
VALUES (1,'GRO1');
INSERT INTO COUNTER_PARTY_GROUP_ASSOC
VALUES (2,'GRO2');
INSERT INTO COUNTER_PARTY_GROUP_ASSOC
VALUES (3,'GRO3');
INSERT INTO COUNTER_PARTY_GROUP_ASSOC
VALUES (4,'GRO3');
INSERT INTO COUNTER_PARTY_GROUP_ASSOC
VALUES (5,'GRO3');

COMMIT;

--CREATE COUNTER PARTY BO ASSOCIATION TABLE
CREATE TABLE COUNTER_PARTY_BO_ASSOC (
COUNTER_PARTYID INTEGER NOT NULL,
BO_CODE_ID VARCHAR2(5 CHAR) NOT NULL,
PRIMARY KEY (COUNTER_PARTYID,BO_CODE_ID)
);


INSERT INTO COUNTER_PARTY_BO_ASSOC
VALUES (1,'BO1');
INSERT INTO COUNTER_PARTY_BO_ASSOC
VALUES (2,'BO2');
INSERT INTO COUNTER_PARTY_BO_ASSOC
VALUES (3,'BO3');
INSERT INTO COUNTER_PARTY_BO_ASSOC
VALUES (4,'BO3');
INSERT INTO COUNTER_PARTY_BO_ASSOC
VALUES (5,'BO3');

COMMIT;

--CREATE GROUP BO IBO ASSOCIATION TABLE
CREATE TABLE GROUP_BO_IBO_ASSOC (
GROUP_CODE_ID VARCHAR2(5 CHAR) NOT NULL,
BO_CODE_ID VARCHAR2(5 CHAR) NOT NULL,
IBO_CODE_ID VARCHAR2(5 CHAR) NOT NULL,
PRIMARY KEY (GROUP_CODE_ID,BO_CODE_ID,IBO_CODE_ID)
);

INSERT INTO GROUP_BO_IBO_ASSOC
VALUES ('GRO1','BO1','IBO1');
INSERT INTO GROUP_BO_IBO_ASSOC
VALUES ('GRO1','BO2','IBO2');
INSERT INTO GROUP_BO_IBO_ASSOC
VALUES ('GRO1','BO3','IBO3');
INSERT INTO GROUP_BO_IBO_ASSOC
VALUES ('GRO2','BO1','IBO1');
INSERT INTO GROUP_BO_IBO_ASSOC
VALUES ('GRO2','BO2','IBO2');
INSERT INTO GROUP_BO_IBO_ASSOC
VALUES ('GRO2','BO3','IBO3');
INSERT INTO GROUP_BO_IBO_ASSOC
VALUES ('GRO3','BO1','IBO1');
INSERT INTO GROUP_BO_IBO_ASSOC
VALUES ('GRO3','BO2','IBO2');
INSERT INTO GROUP_BO_IBO_ASSOC
VALUES ('GRO3','BO3','IBO3');

COMMIT;

--CREATE MV LOGS ON FOUR BASE TABLES
CREATE MATERIALIZED VIEW LOG ON COUNTER_PARTY
WITH PRIMARY KEY,ROWID,SEQUENCE INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON COUNTER_PARTY_GROUP_ASSOC
WITH PRIMARY KEY,ROWID,SEQUENCE INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON COUNTER_PARTY_BO_ASSOC
WITH PRIMARY KEY,ROWID,SEQUENCE INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON GROUP_BO_IBO_ASSOC
WITH PRIMARY KEY,ROWID,SEQUENCE INCLUDING NEW VALUES;

--CREATE MV

CREATE materialized VIEW COUNTER_PARTY_IBO_ASSOC_MV REFRESH FAST ON COMMIT AS
SELECT
A.COUNTER_PARTYID
,GBI.GROUP_CODE_ID
,GBI.BO_CODE_ID
,GBI.IBO_CODE_ID
,COUNT(1) AS CNT
FROM COUNTER_PARTY A
INNER JOIN COUNTER_PARTY_GROUP_ASSOC GP
ON A.COUNTER_PARTYID=GP.COUNTER_PARTYID
INNER JOIN COUNTER_PARTY_BO_ASSOC BO
ON A.COUNTER_PARTYID=BO.COUNTER_PARTYID
INNER JOIN GROUP_BO_IBO_ASSOC GBI
ON GP.GROUP_CODE_ID=GBI.GROUP_CODE_ID
AND BO.BO_CODE_ID=GBI.BO_CODE_ID
GROUP BY
A.COUNTER_PARTYID
,GBI.GROUP_CODE_ID
,GBI.BO_CODE_ID
,GBI.IBO_CODE_ID
;

SELECT * FROM COUNTER_PARTY_IBO_ASSOC_MV order by 1 asc;

When I run select *, I see results I am expecting. But when I change association or delete some of the data from base tables,
it is not reflecting in MV.

DELETE FROM COUNTER_PARTY WHERE COUNTER_PARTYID=5;
COMMIT;

I still see row with counter_partyid=5 in MV. Shouldn't it be removed from MV result set? If I run SELECT statement used to create MV, it doesn't show row with counter_partyid=5? Does it mean this MV is NOT doing FAST REFRESH? However, When I insert new rows in COUNTER_PARTY,COUNTER_PARTY_GROUP_ASSOC AND COUNTER_PARTY_BO_ASSOC, MV shows me that new row without doing full refresh.

INSERT INTO COUNTER_PARTY
VALUES (7);
INSERT INTO COUNTER_PARTY_GROUP_ASSOC
VALUES (7,'GRO2');
INSERT INTO COUNTER_PARTY_BO_ASSOC
VALUES (7,'BO2');

commit;

Am I missing something here?

What are the pre-requisite to make MV FAST refresh? How can I make this MV FAST refresh?

Problem I am trying to solve here as you can see I have to make three JOINs to get IBO for a counterparty. With this MV, I want to get it simple select on MV.

Thanks,
Mangesh



and Chris said...

Unfortunately LiveSQL has some restrictions. If you encounter problems using it please report the issue with the Feedback link at the top. Thanks for trying anyway and pasting your test case here! :)

It can be tricky figuring out why Oracle doesn't refresh MVs. Fortunately we've provided the procedure dbms_mview.explain_mview to help with this.

http://docs.oracle.com/database/121/DWHSG/basicmv.htm#DWHSG8219

Run this on your MV and you'll see:

truncate table MV_CAPABILITIES_TABLE;
exec dbms_mview.explain_mview('COUNTER_PARTY_IBO_ASSOC_MV');
select capability_name, possible, msgtxt from MV_CAPABILITIES_TABLE;

CAPABILITY_NAME                POSSIBLE  MSGTXT                                                                              
PCT                            N                                                                                             
REFRESH_COMPLETE               Y                                                                                             
REFRESH_FAST                   N                                                                                             
REWRITE                        N                                                                                             
REFRESH_FAST_AFTER_INSERT      N         problem with loader log                                                             
REFRESH_FAST_AFTER_INSERT      N         problem with loader log                                                             
REFRESH_FAST_AFTER_INSERT      N         problem with loader log                                                             
REFRESH_FAST_AFTER_INSERT      N         problem with loader log                                                             
REFRESH_FAST_AFTER_INSERT      N         the materialized view is BUILD DEFERRED                                             
REFRESH_FAST_AFTER_ONETAB_DML  N         see the reason why REFRESH_FAST_AFTER_INSERT is disabled                            
REFRESH_FAST_AFTER_ONETAB_DML  N         COUNT(*) is not present in the select list                                          
REFRESH_FAST_AFTER_ANY_DML     N         see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled                        
REFRESH_FAST_PCT               N         PCT FAST REFRESH is not possible if query contains an inline view                   
REFRESH_FAST_PCT               N         the materialized view is BUILD DEFERRED                                             
REWRITE_FULL_TEXT_MATCH        N         query rewrite is disabled on the materialized view                                  
REWRITE_FULL_TEXT_MATCH        N         the materialized view is BUILD DEFERRED                                             
REWRITE_PARTIAL_TEXT_MATCH     N         query rewrite is disabled on the materialized view                                  
REWRITE_PARTIAL_TEXT_MATCH     N         the materialized view is BUILD DEFERRED                                             
REWRITE_GENERAL                N         the reason why the capability is disabled has escaped analysis                      
REWRITE_GENERAL                N         query rewrite is disabled on the materialized view                                  
REWRITE_GENERAL                N         the materialized view is BUILD DEFERRED                                             
REWRITE_PCT                    N         general rewrite is not possible or PCT is not possible on any of the detail tables  


The key lines for you are:

REFRESH_FAST_AFTER_INSERT      N         the materialized view is BUILD DEFERRED     
REFRESH_FAST_AFTER_ONETAB_DML  N         COUNT(*) is not present in the select list  


The msgtxt gives clues you in to some subtle changes you need to make to your MV:

"the materialized view is BUILD DEFERRED" -> Create it with the "build immediate" option
"COUNT(*) is not present in the select list" -> Change count(1) -> count(*)

Make these changes and everything should work swimmingly:

CREATE TABLE COUNTER_PARTY (
COUNTER_PARTYID INTEGER NOT NULL,
PRIMARY KEY (COUNTER_PARTYID)
);
INSERT INTO COUNTER_PARTY 
VALUES (5);
COMMIT;

--CREATE COUNTER PARTY GROUP CODE ASSOCIATION TABLE
CREATE TABLE COUNTER_PARTY_GROUP_ASSOC (
COUNTER_PARTYID INTEGER NOT NULL,
GROUP_CODE_ID VARCHAR2(5 CHAR) NOT NULL,
PRIMARY KEY (COUNTER_PARTYID,GROUP_CODE_ID)
);

INSERT INTO COUNTER_PARTY_GROUP_ASSOC 
VALUES (5,'GRO3');
COMMIT;
--CREATE COUNTER PARTY BO ASSOCIATION TABLE
CREATE TABLE COUNTER_PARTY_BO_ASSOC (
COUNTER_PARTYID INTEGER NOT NULL,
BO_CODE_ID VARCHAR2(5 CHAR) NOT NULL,
PRIMARY KEY (COUNTER_PARTYID,BO_CODE_ID)
);

INSERT INTO COUNTER_PARTY_BO_ASSOC 
VALUES (5,'BO3');
COMMIT;
--CREATE GROUP BO IBO ASSOCIATION TABLE
CREATE TABLE GROUP_BO_IBO_ASSOC (
GROUP_CODE_ID VARCHAR2(5 CHAR) NOT NULL,
BO_CODE_ID VARCHAR2(5 CHAR) NOT NULL,
IBO_CODE_ID VARCHAR2(5 CHAR) NOT NULL,
PRIMARY KEY (GROUP_CODE_ID,BO_CODE_ID,IBO_CODE_ID)
);
INSERT INTO GROUP_BO_IBO_ASSOC 
VALUES ('GRO3','BO3','IBO3');
COMMIT;

--CREATE MV LOGS ON FOUR BASE TABLES
CREATE MATERIALIZED VIEW LOG ON COUNTER_PARTY
WITH PRIMARY KEY,ROWID,SEQUENCE INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON COUNTER_PARTY_GROUP_ASSOC
WITH PRIMARY KEY,ROWID,SEQUENCE INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON COUNTER_PARTY_BO_ASSOC
WITH PRIMARY KEY,ROWID,SEQUENCE INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON GROUP_BO_IBO_ASSOC
WITH PRIMARY KEY,ROWID,SEQUENCE INCLUDING NEW VALUES;

--CREATE MV
CREATE materialized VIEW COUNTER_PARTY_IBO_ASSOC_MV 
BUILD IMMEDIATE
REFRESH FAST ON COMMIT 
AS
SELECT 
A.COUNTER_PARTYID
,GBI.GROUP_CODE_ID
,GBI.BO_CODE_ID
,GBI.IBO_CODE_ID
,COUNT(*) AS CNT
FROM COUNTER_PARTY A
INNER JOIN COUNTER_PARTY_GROUP_ASSOC GP
ON A.COUNTER_PARTYID=GP.COUNTER_PARTYID
INNER JOIN COUNTER_PARTY_BO_ASSOC BO
ON A.COUNTER_PARTYID=BO.COUNTER_PARTYID
INNER JOIN GROUP_BO_IBO_ASSOC GBI
ON GP.GROUP_CODE_ID=GBI.GROUP_CODE_ID
AND BO.BO_CODE_ID=GBI.BO_CODE_ID
GROUP BY 
A.COUNTER_PARTYID
,GBI.GROUP_CODE_ID
,GBI.BO_CODE_ID
,GBI.IBO_CODE_ID
;

SELECT * FROM COUNTER_PARTY_IBO_ASSOC_MV order by 1 asc;

COUNTER_PARTYID  GROUP_CODE_ID  BO_CODE_ID  IBO_CODE_ID  CNT  
5                GRO3           BO3         IBO3         1

select last_refresh_date from user_mviews
where  mview_name = 'COUNTER_PARTY_IBO_ASSOC_MV';

DELETE FROM COUNTER_PARTY WHERE COUNTER_PARTYID=5;
COMMIT;

select last_refresh_date from user_mviews
where  mview_name = 'COUNTER_PARTY_IBO_ASSOC_MV';

INSERT INTO COUNTER_PARTY 
VALUES (7);
INSERT INTO COUNTER_PARTY_GROUP_ASSOC 
VALUES (7,'GRO7');
INSERT INTO COUNTER_PARTY_BO_ASSOC 
VALUES (7,'BO7');
INSERT INTO GROUP_BO_IBO_ASSOC 
VALUES ('GRO7','BO7','IBO7');

commit;

select last_refresh_date
from   user_mviews
where  mview_name = 'COUNTER_PARTY_IBO_ASSOC_MV';

SELECT * FROM COUNTER_PARTY_IBO_ASSOC_MV order by 1 asc;

COUNTER_PARTYID  GROUP_CODE_ID  BO_CODE_ID  IBO_CODE_ID  CNT  
7                GRO7           BO7         IBO7         1

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Mangesh Deshpande, November 03, 2016 - 10:58 am UTC

Thanks Chris. That really helped. However I would like to know if there is better solution than implementing MV to solve original problem. JOIN four tables to get IBO code or realize those JOINs using MV.
Chris Saxon
November 03, 2016 - 1:07 pm UTC

What exactly is the problem? Why are you trying to use MVs?

Create Materialized view with fast refresh automatically every 5 mints

Uttam Kumar, June 22, 2017 - 6:40 pm UTC

Hi Experts,

I am struggling to create MV from last couple of day's.

1. I have 3 table who's mv logs available and mv also available in remote databases.
2. for some reason (performance) I need to create another MATERIALIZED VIEW in owner schema itself, but query's have all 3 tables in join and have other filter conditions.
4. when I am trying this it says
ORA-12015: cannot create a fast refresh materialized view from a complex query
5.I am ok to have either ON DEMAND OR automated refresh, but it must be fast refresh. other wise environment get hung with consuming all memory.

e.g.

CREATE MATERIALIZED VIEW &&BASE_SCHEMA..MV_1
PARTITION BY HASH (table_b_filed_2, table_c_filed_3)
PARTITIONS 16
PCTFREE 5 PCTUSED 60
TABLESPACE IDX_HIGH
STORAGE (INITIAL 50K)
USING INDEX STORAGE (INITIAL 25K)
REFRESH FAST -- ON DEMAND
START WITH SYSDATE
NEXT SYSDATE + 5/1440
AS
SELECT /*+ PARALLEL (A 24) */ DISTINCT
b.table_b_filed_2
, c.table_c_filed_3
FROM table_A a,
table_b B,
table_c c
WHERE a.id = b.id
and b.id = c.id
AND a.filed_a ='PROCESSED'
AND a.field_b ='XXXXXX'

Pls help.

Regards,
Kumar
Connor McDonald
June 28, 2017 - 12:35 am UTC

Please dont ask new questions via Review.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library