Exists与In的物化视图性能

Question

我在谷歌上搜索了一下，找不到oracle性能问题的明确答案。也许我们可以在这里记录下来。我正在制作一个MV，它非常简单，但放在相当大的桌子上。像许多事情一样，查询可以用多种方式编写。在我的案例中，当编写为select语句时，两个解决方案具有相似的成本/执行计划，但当放置在创建物化视图中时，执行时间会发生巨大变化。有什么原因吗？

• 表1是大约4000万条记录
• 表2是大约800万条记录
• field1是Tab1上的主键，它在Tab2上不是PK或唯一，但选项卡2对此字段有索引
• field2不是键，也没有在任一表上进行索引(boo(

查询为：

问题1：

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

问题2：

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

作为select语句的两个查询在时间上运行相似，explain计划让它们都使用索引扫描，而不是我所期望的全表扫描。出乎意料的是，当在类似的mv创建中运行时，Q2运行速度要快得多(47秒vs 81天/v$session_longops(

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

有人有什么见解吗？这里有没有一条规则，如果可能的话，只对mviews使用IN？当表之间不存在索引时，我知道in和exist之间的技巧，但这一点让我感到困惑。这是针对Oracle11g数据库运行的。

Answer 1

这看起来像是一个已知的错误。如果您可以访问My Oracle Support，请查看Slow Create/Refresh of Materialized View Based on NOT IN Definition Query(Doc ID 1591851.1(，或者如果您没有访问权限，请查看问题摘要。

MOS版本的内容当然不能在这里复制，但可以说，唯一的解决方法是您已经在使用not exists。它固定在12c，这对你没有多大帮助。