1

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:

enter image description here

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?

2
  • I doubt there is a work-around, especially if that's what you've already been told by Oracle Support. Defining your query a certain way will not help, as the query you write is only actually used once, when the MV is first created. When the full refreshes run, Oracle generates its own query behind the scenes, using its own internal rules and optimizations. That query will be similar to yours, but will almost certainly not match exactly: hints will disappear, joins may be done differently, some keywords may be dropped or replaced, etc., and you will have no control over that. – pmdba Jan 25 at 11:28
  • Of course, the only way to know for sure is to try it. Have you tried using the "ONLY" keyword, and if so what was the result? – pmdba Jan 25 at 11:29
0

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, ), (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ), (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ))
1
  • Thanks, but that looks like it's using SDO_GEOMETRY (native Oracle datatype). The question is about ST_GEOMETRY (user-defined datatype that is not FINAL). – User1973 Jan 25 at 17:34
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.