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