sql - Oracle:在较小的结果集上性能不佳？

Question

我在 Oracle 中的查询遇到了一个非常奇怪的行为查询本身是巨大且相当复杂的......但每次运行它时都基本相同。但是，当返回较小的结果集时，它似乎执行得更慢。我能给出的最好的例子是，如果我在上面设置这个过滤器，

and mgz_query.IsQueryStatus(10001,lqo.query_id)>0 

它返回 12,429 条记录中的 960 条，我看到执行时间约为 1.9 秒。但是，如果我将过滤器更改为

and mgz_query.IsQueryStatus(10005,lqo.query_id)>0

它返回 12,429 条记录中的 65 条，我看到执行时间约为 6.8 秒。当深入挖掘时，我发现较小的结果集似乎比较大的结果集执行了更多的缓冲区获取。这对我来说似乎完全违反直觉。

正在运行的查询大约有 8000 个字符长(除非有人想要它，否则我不会用整个查询来混淆这篇文章)，包括 4 个“Union All”语句，但主要过滤索引并且非常漂亮高效，除了其庞大的规模。

正在使用的过滤器通过以下函数执行。

Function IsQueryStatus(Pni_QueryStateId        in number,
                         Pni_Query_Id       in number) return pls_integer as
    vn_count pls_integer;
  Begin
    select count(1)
      into vn_count
     from m_query mq
    where mq.id = Pni_Query_Id
      and mq.state_id = Pni_QueryStateId;

    return vn_count;
  End;

关于什么可能导致较小的结果集比大的结果集表现更差的任何想法？

Answer 1

我认为你正面临这样一种情况:确定某物不在系列中比确定它是否在系列中花费的时间要长得多。这可能经常发生。例如，如果 m_query(id) 上有一个索引，然后考虑如何where子句可能会被执行:

(1) 值Pni_Query_Id在索引中查找。没有匹配项。使用值 0 完成查询.

(2) 有一堆火柴。现在，让我们获取 state_id 所在的页面位于并比较 Pni_QueryStateId .哦，那还有很多工作要做。

如果是这种情况，那么在 m_query(id, state_id) 上有一个索引应该有助于查询。

顺便说一句，这是假设唯一的变化是 where 中的函数调用条款。如果还有其他更改以减少行数，您可能只是调用此函数的次数更少。