Oracle MV requires object type to be defined as FINAL?

Question

I want to create an Oracle 18c materialized view (MV) on a table that has a user-defined datatype called ST_GEOMETRY:

• The MV would do a COMPLETE refresh on a schedule — on a local table.

---

Some info about ST_GEOMETRY:

The ST_Geometry data type implements the SQL 3 specification of user-defined data types (UDTs), allowing you to create columns capable of storing spatial data such as the location of a landmark, a street, or a parcel of land. It provides International Organization for Standards (ISO) and Open Geospatial Consortium, Inc. (OGC) compliant structured query language (SQL) access to the geodatabase and database. This storage extends the capabilities of the database by providing storage for objects (points, lines, and polygons) that represent geographic features. It was designed to make efficient use of database resources; to be compatible with database features such as replication and partitioning; and to provide rapid access to spatial data.

ST_Geometry itself is an abstract, noninstantiated superclass. However, its subclasses can be instantiated. An instantiated data type is one that can be defined as a table column and have values of its type inserted into it.

The SQL definition of the user-defined datatype can be found here: db<>fiddle.

CREATE OR REPLACE TYPE SDE."ST_GEOMETRY" (
...
) NOT final

(Although, I'm guessing most of the logic is stored in the EXTPROC.)

---

MV object types must be FINAL:

There is a known issue when trying to create an MV on the ST_GEOMETRY user-defined datatype (ORA-30373):

Creating an Oracle materialized view for a table containing an ST_Geometry attribute returns the following error:

"ORA-30373: object data types are not supported in this context".

Code:
SQL> CREATE MATERIALIZED VIEW parcel_view
  2  AS SELECT * FROM parcel@remote_server;

CREATE MATERIALIZED VIEW parcel_view
*
ERROR at line 1:
ORA-30373: object data types are not supported in this context

Cause:

Oracle's Advanced Replication functionality requires that all object types be defined as FINAL to participate within a materialized view.

The reason that the ST_Geometry cannot be defined as FINAL is because the type contains subtypes used for type inheritance. Oracle does not allow a type with subtypes to be defined as FINAL.

Esri is currently working with Oracle to address this issue and limitation. The following Oracle TAR file is available for reference: "6482996.992 - ORA-30373 MATERIALIZE VIEW UNABLE TO REPLICATE A TYPE WHICH CONTAINS SUBTYPES. Enhancement Request (ER) 6370112".

Note: I've contacted ESRI support and they say there hasn't been any progress with Oracle regarding the enhancement request.

---

Question:

Is there a way to workaround this issue?

• I ask because ESRI has a reputation of not being great with Oracle. So there is a chance that they have misunderstood the issue or haven't gotten creative enough.

---

For example, what about the ONLY keyword?

9.9 Materialized View Support for Objects

For both object-relational and object materialized views that are based on an object table, if the type of the master object table is not FINAL, the FROM clause in the materialized view definition query must include the ONLY keyword.

For example:

CREATE MATERIALIZED
VIEW customer OF cust_objtyp AS  
SELECT CustNo FROM ONLY HR.Customer_objtab@dbs1;  

Otherwise, the FROM clause must omit the ONLY keyword.

Notes:

• Unfortunately, that blurb is about object tables, not about object columns (I believe the ST_GEOMETRY user-defined datatype would be considered an "object column").

• But I wonder if the concept of the ONLY keyword could be used to help with the FINAL problem with the ST_GEOMETRY object column. Any ideas?

Answer 1

I tested in my test server ambient and worked.

SQL> create table test_mv (col1 number not null unique, col2 number);

Table created.

SQL> create materialized view log on test_mv with primary key;

Materialized view log created.

SQL>  create materialized view mv refresh fast on demand as
  2     select col1,col2, sdo_geometry(2001, 26917, sdo_point_type(col1,col2, null), null, null) as shape
  3     from test_mv;

Materialized view created.

SQL> insert into test_mv values (1, 1);

1 row created.

SQL>  insert into test_mv values (2, 2);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('MV', 'f');

PL/SQL procedure successfully completed.

col1 col2 shape
1   1   (2001, 26917, (1, 1, ), (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ), (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ))
2   2   (2001, 26917, (2, 2, ), (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ), (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ))

Answer 2

I tested in my test server.I have created as "COLA_MARKETS" Table using ST_GEOMETRY (TYPE). for the purpose of creating "Materialized View" i have to create view "COLA_MARKETS_VIEW" then create "Materialized View" name as "COLA_MARKETS_MVIEW".

CREATE TABLE cola_markets (
  mkt_id NUMBER PRIMARY KEY,
  name VARCHAR2(32),
  shape ST_GEOMETRY); 
    
select * from cola_markets;

                           
CREATE VIEW cola_markets_view as select * from cola_markets;    

select * from cola_markets_view;

CREATE MATERIALIZED VIEW cola_markets_mview
REFRESH COMPLETE ON DEMAND
AS SELECT * From  cola_markets_view;

select * from COLA_MARKETS_MVIEW t;