问题[materialized-view](dba)

这是我的老问题多个并发“REFRESH MATERIALIZED VIEW”的延续：如何管理？

现在，在最初更加理论化之后，不幸的是，我正在与真实的事物作斗争:)

我有一个发送许多请求的程序，最后刷新一个物化视图。不幸的是，有时，两个并发作业请求重叠CREATE MATERIALIZED VIEW milestones_files（视图很大并且需要几分钟才能刷新），所以最新的作业失败（显然），这会取消整个事务（这很糟糕，因为一些数据库更新也被删除了）。

这是发生错误时的日志（为简洁起见，没有所有 SQL 行）：

2022-03-22 11:17:37.685 UTC [29004] storiq@storiqone ERREUR:  Bloquage mortel détecté
2022-03-22 11:17:37.685 UTC [29004] storiq@storiqone DÉTAIL:  Le processus 29004 attend AccessExclusiveLock sur relation 181019 de la base de données 19044 ; bloqué par le processus 29174.
2022-03-22 11:17:37.685 UTC [29004] storiq@storiqone ASTUCE :  Voir les journaux applicatifs du serveur pour les détails sur la requête.
2022-03-22 11:17:37.685 UTC [29004] storiq@storiqone INSTRUCTION :  CREATE MATERIALIZED VIEW milestones_files_29000 AS SELECT sa.id AS archive,
2022-03-22 11:17:37.691 UTC [29243] storiq@storiqone ERREUR:  could not open relation with OID 181019
2022-03-22 11:17:37.691 UTC [29243] storiq@storiqone INSTRUCTION :  SELECT REGEXP_REPLACE(pg_catalog.pg_get_ruledef(r.oid, TRUE), '^.*DO INSTEAD  (SELECT.*);$', '\1') FROM pg_catalog.pg_rewrite r WHERE r.ev_class = ( SELECT oid FROM pg_class WHERE relname = 'milestones_files')
2022-03-22 11:17:37.693 UTC [29243] storiq@storiqone ERREUR:  la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc
2022-03-22 11:17:37.693 UTC [29243] storiq@storiqone INSTRUCTION :  UPDATE archivetoarchivemirror SET lastupdate = NOW(), jobrun = $1 WHERE archive = $2 OR jobrun = $1
2022-03-22 11:17:37.696 UTC [29243] storiq@storiqone ERREUR:  l'instruction préparée « update_archivemirrors » n'existe pas
2022-03-22 11:17:39.510 UTC [29004] storiq@storiqone ERREUR:  la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc
2022-03-22 11:17:39.510 UTC [29004] storiq@storiqone INSTRUCTION :  UPDATE archivetoarchivemirror SET lastupdate = NOW(), jobrun = $1 WHERE archive = $2 OR jobrun = $1
2022-03-22 11:17:39.510 UTC [29004] storiq@storiqone ERREUR:  l'instruction préparée « update_archivemirrors » n'existe pas

当然我可以简单地将CREATE MATERIALIZED VIEW命令放在主事务之外，但是最新的刷新仍然会失败，因此视图不会反映数据库中的最新更改，这仍然是一个（不太严重的）问题。

理想情况下，我希望最新的刷新优先，但不会使已经在另一个进程中运行的事务失败（如果我在检查锁后简单地杀死另一个进程，就会出现这种情况，根据Kill concurrent materialized view refresh PID） .

从文档来看，添加该选项似乎CONCURRENTLY也不能解决我的问题（这与并发 SELECT 无关）。

欢迎任何建议。当然，我可以尝试实施我在旧问题中提出的解决方案，尽管考虑到级联查询和事务以及庞大的数据库，它会特别复杂......

随着select matviewname from pg_matviews我得到数据库中的所有物化视图。如何过滤结果以仅获取当前用户具有选择授权（=读取权限）的那些？

我对计算有复杂的看法，但有一条线会引起问题

ROUND((((latest_revenue::numeric/ oper_reve_3y_old::numeric) ^ (1/3::numeric)) -1::numeric)*100::numeric)

当我执行查询时，计算工作正常，当我在视图中使用它时，一切正常，但是如果我尝试用这个语句创建物化视图，我得到错误

“一个负数的非整数幂会产生一个复杂的结果”

有什么办法可以在物化视图中使用它？还是有一些解释为什么这不适用于物化视图？

我有一个在 BIRT 报告中使用的查询（在 IBM 的 Maximo Asset Management 平台中）。

我正在考虑将查询存储在物化视图中，以提高性能并减少系统负载。

问题：

如果 MV 已过期，是否有办法故意让报告失败？

• 换句话说，我们宁愿报告中没有数据，也不愿有错误的数据。

编辑：

我从问题中删除了过多的细节。

我在 Oracle 19c 数据库中的 WORKORDER 表上创建了物化视图日志。

create materialized view log on my_workorder_system.workorder with rowid;  

该计划是通过物化视图将 WORKORDER 记录集成到单独的 GIS 系统 (Oracle 18c)。

问题：

WORKORDER 表中同时包含空间和非空间工作订单记录（ISGIS = 0 或 1）。我只需要将记录同步到 GIS 数据库 WHERE ISGIS = 1。

如果我理解正确，我在 GIS 中的物化视图将告诉工单系统中的 MV 日志在同步后清除日志记录。但是，同步只会发生在 ISGIS=1 的工单上。ISGIS=0 的工单永远不会同步——因此在 MV 日志中不需要。

有没有办法只为满足条件的记录生成 MV 日志？

例如：

create materialized view log on my_workorder_system.workorder with rowid where ISGISS=1.

我在主系统 (Oracle 18c) 和外部系统 (Oracle 19c) 之间创建了一个物化视图：

create materialized view primary_system.workorder_mv
build immediate 
refresh force 
on demand 
start with sysdate next sysdate + (30/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    external_system.workorder_vw@gistomax --view in external system that is served up by dblink (gistomax)
    --the view looks like this: select * from workorder where isgis=1


create materialized view log on external_system.workorder with rowid;  

文档说物化视图应该自动清除：

Oracle 在物化视图刷新期间自动跟踪物化视图日志中的哪些行已被使用，并从日志中清除这些 行，以使日志不会无休止地增长。因为多个简单的物化视图可以使用相同的物化视图日志，所以可能仍然需要已经用于刷新一个物化视图的行来刷新另一个物化视图。在所有物化视图都使用它们之前，Oracle 不会从日志中删除行。

• 我没有任何其他引用 MV 日志的物化视图。
• 这是 MV 日志属性 (Toad)。
• 这是all_registered_mviews看起来的样子。
• MV已同步成功。如果我在外部系统的 WORKORDER 表中创建或更新记录，记录会按 30 秒的计划同步到主系统。

问题：

到目前为止，MV 日志数据似乎没有自动清除：

记录同步后，为什么不自动清除 MV 日志？

如何确保将 Oracle 物化视图创建为“可写”物化视图而不是“可更新”物化视图？我从不希望这些视图将数据推送回源，我希望完全刷新以覆盖我更改的任何内容。

根源问题是其中一个远程数据库不支持物化视图日志，但表有最后修改的时间戳列。因此，我想尝试通过在该列上过滤 Merge 语句使它们保持最新，然后如果出现任何问题，偶尔会进行正常的复制刷新。

我试过了CREATE MATERIALIZED VIEW Test.SomeTable_MVW TABLESPACE SOME_DATA BUILD DEFERRED REFRESH COMPLETE FOR UPDATE AS (SELECT ... FROM SomeTable@OverThere);

但我得到了错误：

12013. 00000 -  "updatable materialized view must be simple enough and have a materialized view log on its master table to do fast refresh"
*Cause:    The updatable materialized view query contained a JOIN, SUBQUERY,
           UNION, CONNECT BY, ORDER BY, or GROUP BY clause, or the master
           table did not have a materialized view log.
*Action:   Make the materialized view simpler. If a join is really needed,
           make multiple simple materialized views then put a view on top of
           them. Make sure a materialized view log is created for the master
           table.

只有一张远程桌子。我不明白为什么甚至包含此错误，or the master table did not have a materialized view log因为这就是您获得可写物化视图的方式。太糟糕了，当您知道不想将数据推回时，没有“FOR WRITEABLE”子句。并且 REFRESH COMPLETE 应该提示我不想将数据推回。

我有一个通过 组合多个表的视图UNION ALL，并使用数据处理一些基本错误。由于处理错误的方式，视图比我希望的要慢一些。有没有办法缓存视图的输出，并在任何基础表更新时动态更新它？

我的系统的一些怪癖：

• 数据库每天只在非工作时间查看一次更新。因此，只要在这些非工作时间消耗资源，资源密集型解决方案就可以了。
• 由于系统的设置方式，我宁愿在加载数据时不清理数据，而宁愿保持底层表不变。
• 我设置了适当的支持索引
• 这些表都包含不同的记录，没有重叠，所以我需要把它们都拉进去

我考虑过的一些选项：

• 由于视图依赖于 UNION 运算符，索引视图将不起作用。
• 我还没有找到任何方法来缓存 TVF 的输出。
• 如果修改了基础表，我可以创建一个缓存表并在任何时候手动加载它，但这感觉就像一个 hacky 解决方法。我想我还必须为此创建一个 SQL 代理作业，因为将其设置为触发器将涉及每天晚上在表被覆盖时刷新表数百万次。
• 我可以忍受糟糕的性能，但这会对最终用户产生负面影响。

这是视图的简化示例：

CREATE OR ALTER VIEW AllNamesAndAccountNumbers AS 
SELECT
    x.AccountNumber
    , COALESCE(x.FullName, n.FullName) AS FullName
FROM (
    SELECT
        AccountNumber
        , FullName 
    FROM TableWithNames
    UNION ALL
    SELECT
        AccountNumber
        , NULL AS FullName
    FROM TableMissingNames
) x
LEFT JOIN NameMappings n -- this join is slow due to the table being really huge
ON n.AccountNumber = x.AccountNumber

哦 - 如果这是一个反模式，我应该从一个完全不同的角度来攻击它，请这么说。我担心我看错了。

我有一个较大的表（>1.26 亿行），并且需要优化一组专门的查询，所以我在表上创建了一个索引视图，并编写了一个程序，当条件有利于时从索引视图中进行选择它。

该过程的执行时间比我预期的要长，所以我查看了执行计划，我发现耗时最长的查询是从我的视图中选择的查询，当我将鼠标悬停在耗时最长的元素上时，它是基础表的 CI 的聚集索引查找。不是视图上的索引。

我知道索引视图是一种存储在磁盘上的虚拟表，并且在查询时根本不会将查询传递到基础表。不是这样吗？

在 SQL Server 中，索引视图是一个充满限制的地狱景观。但我需要一个。我有一个格式化程序 SQLCLR 函数，它创建一个域键的美化版本 - 用户希望能够搜索该美化版本的子字符串。所以我需要一个持久计算列或物化视图的全文索引。

但是，格式化程序依赖于存储在多个表中的数据。

所以这适用于视图，但不适用于持久计算列，因为它们无法从多个表中查询。

我的 SQLCLR 方法是精确且确定性的，因此它应该可以在索引视图中使用，但索引视图的索引键中不能有 SQLCLR。

我可以使用 T-SQL 函数重新实现我的格式化程序FORMAT......但FORMAT也与索引视图不兼容。

索引视图可以做任何事情吗？ 曾经？

如果有人可以向我推荐一个关于最佳实践的好文档，我对“使用触发器滚动你自己的物化视图”方法持开放态度。上次我尝试时，它失控了，插入和更新的代码与初始化之间存在大量重复。

是否有某种方法可以在不使用物化/索引视图或持久计算列的情况下对几百万行的计算数据进行高性能文本搜索？

格式化程序不执行数据访问。但是，我需要输入格式化程序以使其有用的数据将来自多个表（具有良好的常规连接），因此我无法使用持久计算列解决此问题。我无法键入 SQLCLR 列，因此无法在全文索引中使用它。

我以为这很简单。持久列和索引视图旨在执行写入计算，并正确实现观察者模式，以便对其依赖关系的更改反映在计算值上。