Oracle缓慢的RANK函数

Question

我的应用程序使用必须保持通用性（没有过滤器）的视图，并包括分析函数RANK和DENSE_RANK。例如，我有一个名为MYVIEW的视图：

SELECT 
RANK() OVER (PARTITION BY FIELD1 ORDER BY FIELD2) RANK, 
FIELD2, 
FIELD3
FROM TABLE1;

我的应用程序会在运行时应用必要的过滤器，例如：

SELECT * FROM MYVIEW WHERE FIELD3 IN ('a','b','c');

没有使用RANK函数时，我的查询速度非常快，但是使用它后变得极其缓慢（超过2分钟）（我能够得到正确的结果，只是速度很慢）。底层表有250,000多行，我无法控制其设计。我不能进一步分区。因此，它是否之所以慢是因为每次调用视图时都会为FIELD1中的每个唯一条目创建分区？有没有其他避免这种情况的方法？有什么建议可以让这个查询更快？

- PWW

---

你尝试查看查询计划了吗？ - mustaccio

我会期望它先应用过滤器（谓词推送）并将排名应用于匹配行。但执行计划将揭示正在发生的事情。您是否获得了预期的排名值 - 即它们在结果集上是连续的，还是由于在应用过滤器之前进行评估而存在间隙？ （您想要哪个？） - Alex Poole

@Alex Pole，对于FIELD3 IN ('a'，'b'，'c')条件的谓词推送在这种情况下无法工作，因为这是一种物化子查询：SELECT ... FROM (子查询/视图，编号行) WHERE FIELD3 IN ('a'，'b'，'c')； - 这个查询必须先给所有行分配编号，然后选择一些行，但不改变编号。我猜在table1（FIELD1，FIELD2）上建立一个简单的多列索引可以加速这个查询。 - krokodilko

@kordiko - 我的期望经常是错误的 *8-) 我不确定排名是否总是会先发生（这也是我在问的部分），所以你帮我省去了测试的时间。 - Alex Poole

实现视图材料化？不确定是否可能，因为我正在使用的RDBMS不允许在材料化视图中使用分析函数。 :( - Shannon Severance

Answer 1

正如评论中提到的，由于您在视图中使用了分析函数，Oracle 无法采取任何快捷方式（谓词推送），因为：

• 在您的视图中，您已经与 Oracle 创建了一份协议：每当访问该视图时，RANK 应基于表中的所有行 - 没有指定 WHERE 子句
• 查询视图时，“外部”WHERE 子句不应影响视图生成的行的外观，而只影响是否保留该行
• 分析函数查看其他行以生成值，因此如果更改这些行（过滤），则可以更改该值 - 推动谓词可能会轻松地影响这些函数生成的值
• 如果发生这种情况，则您的视图结果可能变得非常不一致（仅取决于优化器选择如何评估查询）

因此，根据您提供的详细信息，需要按以下方式评估您的查询：

SELECT * 
  FROM (
         SELECT
                RANK() OVER (PARTITION BY FIELD1 ORDER BY FIELD2) RANK, 
                FIELD2, 
                FIELD3
           FROM TABLE1
       ) myview
 WHERE <condition>; -- rankings are not affected by external conditions

和不是这个：

SELECT * FROM (
    SELECT 
           RANK() OVER (PARTITION BY FIELD1 ORDER BY FIELD2) RANK, 
           FIELD2, 
           FIELD3
      FROM TABLE1
     WHERE FIELD3 IN ('a','b','c') -- ranking is affected by the conditions
)

那么，有没有方法可以加快这个过程？或许可以。

1. 如果表被分区了，可以考虑使用并行查询。
2. 索引能帮上忙吗？

通常情况下不能。因为视图本身没有任何条件，所以它将执行全表扫描来考虑所有用于排名的行，等到WHERE子句应用时，使用索引进行过滤已经太晚了。

但是，如果你有一个"覆盖"查询的索引，即只在使用的列上建立索引（例如按顺序为FIELD1、FIELD2、FIELD3），则索引可以作为表的较小版本使用（计划中不再是FULL TABLE SCAN而是INDEX FAST FULL SCAN）。作为额外的好处，由于它已经排序，因此它可以有效地计算FIELD1上的分区，然后在每个分区内按FIELD2排序。

3. 另一种选择是将其作为材料化视图，但如果您的数据经常更改，保持当前状态可能会很麻烦。

4. 最后一个想法是类似于分区选项之前使用的“穷人”的分区方法。 （抱歉，我找不到描述这一点的好链接，但也许你以前听说过。）

只有当：

1. 您的分区列具有相对较少的不同值
2. 这些值不会改变
3. 您知道可以在查询中使用什么分区值来隔离数据
4. Oracle愿意在安全时推动谓词

考虑到Oracle似乎不喜欢在涉及分析函数时推动谓词，我认为这个成功的可能性不高。

如果您想要更多信息，请告诉我。

- Patrick Marchand