遠端表上快速刷新的物化視圖：如何包含 GEOMETRY 列？

我想在遠端表上創建一個****快速刷新的物化視圖 (18c) 。MV 也將有一個 GEOMETRY 列。

GEOMETRY 列數據類型的選項包括：

• ESRI 的ST_GEOMETRY專有實現（使用者定義的數據類型；是“對象”數據類型）
• Oracle 的 SDO_GEOMETRY 數據類型

---

首先，我可以在沒有GEOMETRY 列的情況下成功創建快速刷新 MV：

create materialized view log on maximo.workorder with primary key;  --remote table
grant select maximo.mlog$_workorder to schema_for_dblink; --I've given the dblink access to everything in this schema


create materialized view my_gis_schema.wo_mv
build immediate 
refresh fast
start with sysdate next sysdate + (15/(60*60*24))
as
select
   cast(workorderid as number(38,0)) as objectid,
   wonum,
   status,
   --other fields
   longitudex,
   latitudey
from
   maximo.workorder@my_dblink

---

上面的 MV 有效，但我想將遠端表中的 XY 座標儲存在 MV 的 GEOMETRY 列中（現在，座標儲存在數字列中，而不是幾何列中）。

不幸的是，我對 MV 中 GEOMETRY 列的選擇似乎非常有限：

1. Oracle 似乎不支持 MV 中的 ESRI 的ST_GEOMETRY數據類型（更多資訊在這里和這裡）。

• SQL 將是：sde.st_geometry(longitudex,latitudey,null,null, 26917 ) as shape

2. 此外，Oracle 似乎不支持 MV 中的 SDO_GEOMETRY 與遠端表上的****快速刷新選項：ORA-12015: cannot create a fast refresh materialized view from a complex query

• SQL 將是：sdo_geometry(2001, 26917, sdo_point_type(longitudex,latitudey, null), null, null) as shape

---

問題：

有沒有辦法使用快速刷新選項在遠端表的物化視圖中包含 GEOMETRY 列？

關於SDO_GEOMETRY/快速刷新/遠端表場景：

當 MV 從本地表中選擇時，該問題似乎不會發生。這個問題似乎只在我們從遠端表中選擇/使用 dblink時發生。這似乎是正常的 Oracle 行為。

---

使用遠端表/dblink（失敗）：

這些步驟改編自這篇文章中的答案：Create fast-refresh MV over dblink on table without PK?

SQL> create table t5 (c1 number not null unique, c2 number);

Table created.
SQL> create materialized view log on t5 with primary key;

Materialized view log created.

SQL> create materialized view mv5 refresh fast on demand as
 2  select t5.*, sdo_geometry(2001, 26917, sdo_point_type(c1,c2, null), null, null) as shape
 3  from t5@self;
select t5.*, sdo_geometry(2001, 26917, sdo_point_type(c1,c2, null), null, null) as shape

            *
ERROR at line 2:
ORA-12015: cannot create a fast refresh materialized view from a complex query

---

使用本地表（成功）：

SQL> create table t5 (c1 number not null unique, c2 number);

Table created.

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

Materialized view log created.

SQL> create materialized view mv5 refresh fast on demand as
 2  select t5.*, sdo_geometry(2001, 26917, sdo_point_type(c1,c2, null), null, null) as shape
 3  from t5;

Materialized view created.

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

1 row created.

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

1 row created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

SQL> select refresh_method from user_mviews where mview_name = 'MV5';

REFRESH_METHOD
--------
FAST

---

當然，這並不能回答您關於如何使 GEOMETRY 列正常工作的問題。

但我認為該測試至少有助於證明1) SDO_GEOMETRY、2) fast refresh和3) 使用遠端表的組合是導致問題的原因。如果我們刪除這三件事中的任何一個，那麼 MV 似乎工作正常。

一位同事建議可以通過以下方式解決此問題：

“…在數據庫連結頂部的目標數據庫中創建公共同義詞；遠端表將像本地表一樣。”

據推測，同義詞解決了在具有 SDO_GEOMETRY 列的遠端表上創建具有 FAST 刷新的物化視圖的問題。

CREATE public SYNONYM maximo.workorder FOR maximo.workorder@my_dblink;

-- ... then create the materialized view. 

我的同事說它已經過測試並且有效；它是使用假主鍵測試的，這與我的設置一致。

不幸的是，我沒有機會親自測試它。我目前沒有 CREATE MATERIALIZED VIEW 權限，所以我無法驗證它是否有效。

引用自：https://dba.stackexchange.com/questions/281372