Oracle:如何创建从 XMLType 中提取数据的快速刷新物化 View ？

Question

我有一个包含两列的 xml_documents 表:一个 document_id 列(主键)和一个包含一些 XML 数据的 xml 列，是无模式的 XMLType。我可以仅使用 document_id 创建物化 View :

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

这很好用，但不是很有用。如您所料，我希望实体化 View 从 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 中提取值的提交物化 View 的快速刷新？

Answer 1

您的 XMLType(可能)存储为 CLOB。使用如下查询查找隐藏列:

select * from user_tab_cols where table_name = 'XML_DOCUMENTS';

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

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;
/

然后使用传递给函数的系统列删除并创建实体化 View :

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

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

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