存在 vs In: 材料化视图的性能表现

Question

我进行了一些谷歌搜索，但没有找到关于Oracle性能问题的明确答案。也许我们可以在这里记录下来。我正在构建一个非常简单但是数据表相对较大的MV。查询有多种写法。在我的情况下，当作为选择语句写入时，两个解决方案具有类似的成本/执行计划，但是当它们放置在创建材料化视图内部时，执行时间会发生巨大变化。为什么会这样呢？

• Tab1大约有40M条记录。
• Tab2大约有8M条记录。
• field1是Tab1的主键，在Tab2上不是主键或唯一索引，但是Tab2对该字段有索引。
• field2在任何表上都不是键也不被索引（糟糕）。

查询如下：

Q1:

SELECT
    CR1.Several_Fields
FROM 
    SCHEMA1.tab1 T1
WHERE T1.field2 like 'EXAMPLE%'
AND T1.field1 not in (
    SELECT T2.field1
    FROM SCHEMA1.tab2 T2
)
; 

Q2:

SELECT
    CR1.Several_Fields
FROM 
    SCHEMA1.tab1 T1
WHERE T1.field2 like 'EXAMPLE%'
AND  not exists (
    SELECT 1
    FROM SCHEMA1.tab2 T2
    WHERE T1.field1 = T2.field1
)
;

两个查询作为选择语句在时间上运行相似，并且解释计划表明它们都利用索引扫描而不是全表扫描，这是我所期望的。出人意料的是，在像mv创建这样的情况下运行Q2要快得多（每个v $ session_longops 47秒对比81天）。

CREATE MATERIALIZED VIEW SCHEMA1.mv_blah as
(
  Q1 or Q2
);

有人了解吗，关于 mviews 是否存在禁止使用 IN 的规则？我知道在表之间没有索引的情况下，exist 与 in 之间有一些技巧，但这个问题让我感到困惑。这是针对 Oracle 11g 数据库运行的。

- Greg

---

你能发布Q1和Q2的物化视图创建执行计划吗？ - Radagast

在使用 SQL Developer 的 11g 版本中，当你将 MV 进行包装后，就不能像 select 语句一样让 Oracle 显示执行计划。 - Greg

Answer 1

这似乎是一个已知的错误。如果您可以访问我的Oracle支持，请查看基于NOT IN定义查询的材料化视图创建/刷新缓慢（文档ID 1591851.1），或者如果您没有，问题的摘要可用（有点不太有用）。当然无法在此处再现MOS版本的内容，但可以说唯一的解决方法就是您已经使用的not exists。这个问题在12c中已经解决了，但对您来说没有帮助。

- Alex Poole

---

这太棒了。显然，现在我们只需要将其编写为不存在，但知道有一个待处理的修复程序，我们基本上可以让人们知道对于Oracle 11g及更低版本，这是一个问题应该会有所帮助。我不知道为什么第一篇文章没有出现在我的搜索结果中，但无论如何，我很感激你知道并找到它。现在我只能向DBA抱怨升级整个数据库以修复我的一个查询 :) - Greg

还有一件需要注意的事情是，在MV定义中使用的查询仅在MV创建期间显式地按原样使用。在刷新期间，Oracle可能会自动重写它，添加提示或更改其他内容，并且没有控制它的能力。 - pmdba

@pmdba - 在创建过程中也是如此（因为该错误既涉及创建又涉及刷新）；而且在某种程度上，对于所有其他查询也是如此，这一点毋庸置疑。 - Alex Poole