Oracle：如何创建一个快速刷新的物化视图，从XMLType中提取数据？

Question

我有一个包含两列的表 xml_documents：一个 document_id 列（主键）和一个包含一些 XML 数据的 xml 列，这是一个架构-less XMLType。我可以仅使用 document_id 创建一个物化视图：

create materialized view mv refresh fast on commit as 
select document_id
from xml_documents

这工作正常，但不是很有用。正如您所期望的，我希望物化视图从 XML 中提取数据，为此我使用 extractValue()。我正在尝试以下操作：

create materialized view mv refresh fast on commit as 
select document_id, extractValue(xml, '/my/gaga') gaga
from xml_documents

失败并显示：

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

我应该如何在提交物化视图上创建快速刷新以从 XML 中提取值？

Answer 1

您的 XMLType（可能）存储为 CLOB。使用如下查询查找隐藏列：

select * from user_tab_cols where table_name = 'XML_DOCUMENTS';

然后创建一个函数将 CLOB 转换为 XMLType，并提取值。请注意，“确定性”关键字是必要的，尽管
我不知道为什么。在 SQL 和 PL/SQL 之间来回传递数据会很慢，但如果您使用物化视图，事情可能会很慢
已经很慢了。

create or replace function extract_from_clob(p_xml in clob) return varchar2 deterministic
  is
begin
    return XMLType(p_xml).extract('/my/gaga/text()').getStringVal();
end;
/

然后删除并创建物化视图，并将系统列传递到函数中：

create materialized view mv refresh fast on commit as 
select document_id, extract_from_clob(SYS_NC00003$) gaga
from xml_documents;

我不确定是否使用系统生成的隐藏列。它有效，但似乎不是一个真正的好主意。至少它会成功
在不同的系统上创建对象很困难 - 您每次都需要找到新的列名称。

当 LOB 工作正常时 XMLType 却不起作用，这似乎很奇怪。我找不到任何关于此的文档；我不确定这是否是一个错误，一个未实现的功能，或者是否有一些神奇的设置可以让它工作。如果没有其他人可以提供更好的答案，那么在使用上述方法之前可能需要先咨询 Oracle 支持人员。