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