I have a seemingly simple problem, for which materialized views seem to be the ideal solution, however I do not get it to work efficiently, and maybe the answer simply is: "Oracle does not allow it at all", but I am hoping I am overlooking something stupid.
Problem: due to some historic decision I have a table containing geometries from two countries, stored in the coordinate system of one country. I now want to create a materialized view using a shared coordinate system. So in effect, the simplest query to achieve this was:
select thr_ident, thr_status, geom from
((
select thr_ident, thr_status, sdo_cs.transform(sdo_cs.transform(thr_geometry, 327680), 8307) as geom
from th_threat
where thr_origin in (6,61, 11, 9)
)
union all
(
select thr_ident, thr_status, sdo_cs.transform(thr_geometry, 8307) as geom
from th_threat
where thr_origin not in (6,61,11,9)
))
The geometry is only created once, but I would like to keep the state in sync (for visualisation purposes). So I added a materialized view log on thr_ident and thr_status:
create materialized view log on th_threat
with sequence, rowid (thr_ident, thr_status)
including new values;
and created the materialized view, and hoped it would automatically stay in sync whenever the original source data was changed ('on commit').
But apparently refresh on commit is not possible when using objects, but having a fast refresh would be close enough, if it was efficient/fast enough.
I assumed assumed union all was not really helping, I rewrote this into a single query as follows:
create materialized view th_threat_mv
-- refresh fast on demand
as
select
rowid rid, thr_ident, thr_status,
case
when thr_origin in (6,61, 11, 9) then
sdo_cs.transform(sdo_cs.transform(thr_geometry, 327680), 8307)
else
sdo_cs.transform(thr_geometry, 8307)
end as geom
from th_threat;
But still fast refresh is not enabled.
The result of explain_mview shows only complete refresh is possible, and the others are all disabled (a bit hard to read/deduce for me, I could dump it if requested, but it repeats three times object data types are not supported in this context).
The mtune_view gives me the following error:
QSM-03113: Cannot tune the MATERIALIZED VIEW statement
QSM-02083: mv references PL/SQL function that maintains state
So now I am guessing this is caused by the SDO_CS.TRANSFORM ?
Theoretically I would assume a fast refresh would be possible/simple:
- create the row on insert (with the appropriate transformations)
- update the state when the
thr_statusis changed (or calculate the single row in its entirety
but either I did not correctly implement it or Oracle is not able to deduce it is a actually a simple 1-1 materialized view (because it contains a geometry? a case statement?).
I can always brew my own solution, using triggers, but was wondering if I am overlooking something obvious to get the material view working efficiently (as it seems a perfect fit). Any help/insights/comments are greatly appreciated.
