Fast Refreshing Materialized View Containing SDO_GEOMETRY and SDO_GEOM.SDO_AREA function
The following series of blog articles by Rob van Wijk are an excellent resource and should be read by those reading this article.
Sven commented on my article about Materialized Views containing SDO_GEOMETRY data. He still had some issues so he pointed me to a posting he had made on the Oracle Spatial OTN forum.
I repeat the posting I made on that forum here to complete my response to Sven’s original comment on this website. While my answer doesn’t shows that he cannot include the SDO_GEOM.SDO_AREA in an MV that he wants to FAST REFRESH, it does highlight how to go about solving problems in relation to FAST REFRESHing SDO_GEOMETRY based tables.
Original Posting
Sven’s posting on the OTN website included this example:
/* First create the table */ CREATE TABLE MUSCLE_PSOAS ( MUSCLE_PSOASID NUMBER(38), RL4AXIALCONTOUR MDSYS.SDO_GEOMETRY) -- /* Now the primary key */ CREATE UNIQUE INDEX MUSCLE_PSOAS_PK ON MUSCLE_PSOAS (MUSCLE_PSOASID); -- /* Now the materialized view */ CREATE MATERIALIZED VIEW MV_DERIVED_MUSCLE_PSOAS BUILD IMMEDIATE REFRESH FORCE ON COMMIT WITH PRIMARY KEY AS SELECT MP.MUSCLE_PSOASID, SDO_GEOM.SDO_AREA (mp.RL4AxialContour, 0.005) rl4area, mp.rowid AS mp_rowid FROM muscle_psoas mp;
I responded by taking his example and processing it to discover what the problem.
My Response
/* First create the table */ CREATE TABLE MUSCLE_PSOAS ( MUSCLE_PSOASID NUMBER(38), RL4AXIALCONTOUR MDSYS.SDO_GEOMETRY) -- /* Now the primary key */ CREATE UNIQUE INDEX MUSCLE_PSOAS_PK ON MUSCLE_PSOAS (MUSCLE_PSOASID); -- /* Now the materialized view */ CREATE MATERIALIZED VIEW MV_DERIVED_MUSCLE_PSOAS BUILD IMMEDIATE REFRESH FORCE ON COMMIT WITH PRIMARY KEY AS SELECT MP.MUSCLE_PSOASID, SDO_GEOM.SDO_AREA (mp.RL4AxialContour, 0.005) rl4area, mp.rowid AS mp_rowid FROM muscle_psoas mp;
/* First create the table */
CREATE TABLE MUSCLE_PSOAS (
MUSCLE_PSOASID NUMBER(38),
RL4AXIALCONTOUR MDSYS.SDO_GEOMETRY
);
--
CREATE TABLE succeeded.
--
/* Now the primary key */
ALTER TABLE MUSCLE_PSOAS ADD CONSTRAINT MUSCLE_PSOAS_PK PRIMARY KEY (MUSCLE_PSOASID); -- ALTER TABLE MUSCLE_PSOAS succeeded.
--
/* Now the MV log */
/* To support all DML (INSERT, UPDATE and DELETE) for FAST REFRESH you must include SEQUENCE and "INCLUDING NEW VALUES" in the MV log. */ CREATE MATERIALIZED VIEW LOG ON MUSCLE_PSOAS
WITH SEQUENCE, PRIMARY KEY, ROWID
INCLUDING NEW VALUES;
-- CREATE MATERIALIZED succeeded.
--
/* Now create the materialized view
* Note: Build with REFRESH COMPLETE
* in order to have the MV built and be able to
* query the MV_CAPABILITIES_TABLE
*/
CREATE MATERIALIZED VIEW MV_DERIVED_MUSCLE_PSOAS
BUILD IMMEDIATE
REFRESH COMPLETE
WITH PRIMARY KEY
AS
SELECT mp.rowid AS mp_rowid,
MP.MUSCLE_PSOASID,
SDO_GEOM.SDO_AREA (mp.RL4AxialContour, 0.005) rl4area
FROM muscle_psoas mp;
-- CREATE MATERIALIZED succeeded.
--
DELETE FROM MV_CAPABILITIES_TABLE;
-- 20 rows deleted
--
COMMIT;
-- commited
--
EXECUTE dbms_mview.explain_mview('MV_DERIVED_MUSCLE_PSOAS');
-- anonymous block completed
--
SELECT capability_name,
possible,
related_text,
msgtxt
FROM MV_CAPABILITIES_TABLE
WHERE capability_name NOT LIKE '%PCT%'
AND capability_name NOT LIKE 'PCT%'
AND capability_name NOT LIKE '%REWRITE%'
AND capability_name NOT LIKE 'REWRITE%'
ORDER BY seq;
--
CAPABILITY_NAME POSSIBLE RELATED_TEXT MSGTXT
------------------------------ -------- ------------ ------------------------------------------------------------ REFRESH_COMPLETE Y NULL NULL
REFRESH_FAST N NULL NULL
REFRESH_FAST_AFTER_INSERT N NULL mv REFERENCES PL/SQL FUNCTION that maintains state REFRESH_FAST_AFTER_ONETAB_DML N NULL see the reason why REFRESH_FAST_AFTER_INSERT IS disabled REFRESH_FAST_AFTER_ANY_DML N NULL see the reason why REFRESH_FAST_AFTER_ONETAB_DML IS disabled --
/* Drop objects */
DROP MATERIALIZED VIEW MV_DERIVED_MUSCLE_PSOAS;
-- DROP MATERIALIZED VIEW succeeded.
--
DROP TABLE MUSCLE_PSOAS;
-- DROP TABLE MUSCLE_PSOAS succeeded.
Note that the problem is with the SDO_GEOM.SDO_AREA PL/SQL function as can be seen in the result text (“mv references PL/SQL function that maintains state”) above.
How can one get it to work?
Final Solution
This is how I did it.
/* First create the table */
CREATE TABLE MUSCLE_PSOAS (
MUSCLE_PSOASID NUMBER(38),
rl4area NUMBER,
RL4AXIALCONTOUR MDSYS.SDO_GEOMETRY
);
-- CREATE TABLE succeeded.
--
/* Now the primary key */
ALTER TABLE MUSCLE_PSOAS ADD CONSTRAINT MUSCLE_PSOAS_PK PRIMARY KEY (MUSCLE_PSOASID); -- ALTER TABLE MUSCLE_PSOAS succeeded.
--
/* Create Sequence */
CREATE SEQUENCE MUSCLE_PSOAS_PK_SEQ;
-- Create Sequence succeeded.
--
/* Create Trigger for PK and Area */
CREATE OR REPLACE TRIGGER MUSCLE_PSOAS_PK_SEQ
BEFORE INSERT OR UPDATE ON MUSCLE_PSOAS
FOR each ROW
BEGIN
IF inserting THEN
IF :NEW.MUSCLE_PSOASID IS NULL THEN
SELECT MUSCLE_PSOAS_PK_SEQ.NEXTVAL INTO :NEW.MUSCLE_PSOASID FROM dual;
END IF;
END IF;
IF :NEW.rl4area IS NULL AND :NEW.RL4AXiALCONTOUR IS NOT NULL THEN
:NEW.rl4area := MDSYS.SDO_GEOM.SDO_AREA(:NEW.RL4AxialContour, 0.005);
END IF;
END;
/
-- TRIGGER MUSCLE_PSOAS_PK_SEQ compiled
SHOW errors
-- No Errors.
--
/* Now the MV log */
/* To support all DML (INSERT, UPDATE and DELETE) for FAST REFRESH you must include SEQUENCE and "INCLUDING NEW VALUES" in the MV log. */ CREATE MATERIALIZED VIEW LOG ON MUSCLE_PSOAS
WITH SEQUENCE, PRIMARY KEY, ROWID
INCLUDING NEW VALUES;
-- CREATE MATERIALIZED succeeded.
--
/* Now create the materialized view */
CREATE MATERIALIZED VIEW MV_DERIVED_MUSCLE_PSOAS
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
WITH PRIMARY KEY
AS
SELECT mp.rowid AS mp_rowid,
MP.MUSCLE_PSOASID,
mp.rl4area
FROM muscle_psoas mp;
-- CREATE MATERIALIZED succeeded.
--
DELETE FROM MV_CAPABILITIES_TABLE;
-- 14 rows deleted
--
COMMIT;
-- commited
--
EXECUTE dbms_mview.explain_mview('MV_DERIVED_MUSCLE_PSOAS');
-- anonymous block completed
--
SELECT capability_name,
possible,
related_text,
msgtxt
FROM MV_CAPABILITIES_TABLE
WHERE capability_name NOT LIKE '%PCT%'
AND capability_name NOT LIKE 'PCT%'
AND capability_name NOT LIKE '%REWRITE%'
AND capability_name NOT LIKE 'REWRITE%'
ORDER BY seq;
--
CAPABILITY_NAME POSSIBLE RELATED_TEXT MSGTXT
------------------------------ -------- -------------- ---------
REFRESH_COMPLETE Y NULL NULL
REFRESH_FAST Y NULL NULL
REFRESH_FAST_AFTER_INSERT Y NULL NULL
REFRESH_FAST_AFTER_ONETAB_DML Y NULL NULL
REFRESH_FAST_AFTER_ANY_DML Y NULL NULL
--
/* Drop objects */
DROP MATERIALIZED VIEW LOG ON MUSCLE_PSOAS;
-- DROP MATERIALIZED VIEW succeeded.
--
DROP MATERIALIZED VIEW MV_DERIVED_MUSCLE_PSOAS;
-- DROP MATERIALIZED VIEW succeeded.
--
DROP TABLE MUSCLE_PSOAS;
-- DROP TABLE MUSCLE_PSOAS succeeded.
--
DROP SEQUENCE MUSCLE_PSOAS_PK_SEQ;
-- DROP SEQUENCE MUSCLE_PSOAS_PK_SEQ succeeded.
Note that the value of the POSSIBLE column for the REFRESH_FAST CAPABILITY_NAME means the MV is fast refreshable.
Final Comments with Methodology
So, while this approach won’t let you generate the area on the fly (for this you need to use an ordinary view) it shows that you can fast refresh an MV that contains an SDO_GEOMETRY object. The way I go about building build fast refreshable MVs is:
1. Initially create the materialized view as “BUILD IMMEDIATE REFRESH COMPLETE”;
2. Then use DBMS_MVIEW.EXPLAIN_MVIEW in conjunction with querying the MV_CAPABILITIES_TABLE, to work out if the MV is capable of REFRESH FAST;
3. If it is not, play around until you get the required Y against the REFRESH_FAST CAPABILITY_NAME. But don’t forget to read the documentation!
4. When you finally get the required Y, rebuild the MV using your desired BUILD IMMEDIATE REFRESH FAST parameters.
regards
Simon