SpatialDB Advisor

Fast Refreshing Materialized View Containing SDO_GEOMETRY and SDO_GEOM.SDO_AREA function

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