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 contextCause:
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?
Best Answer
I tested in my test server ambient and worked.