使用ROWNUM进行Oracle查询优化

Question

使用ROWNUM进行Oracle查询优化

oracleperformancerownum

4

这个问题现在已经不存在了

我重新计算了表格的统计数据，添加了新的索引，并重新分析了现有的索引。这完全改变了我的结果，并使下面大部分的发现无效。此时，我找到了一个足够高效且不需要任何ROWNUM技巧的新查询。

另外，我认为值得指出的是，如下所写的查询不能保证给出我想要的结果。将DISTINCT添加到中间查询中可能会破坏我在最内层查询中尝试应用的排序。实际上，这种情况并不会发生，但我不能依赖它。

原始问题

我编写了一个查询，当我加入一个伪造的ROWNUM检查时，似乎表现得更好：

  SELECT * FROM
  (
    SELECT DISTINCT * FROM 
    ( 
      SELECT TransactionID FROM WOWDev.QueryLog WHERE UPPER(UserName)=UPPER('xyz') AND TransactionID IS NOT NULL ORDER BY TransactionID DESC
    ) 
    WHERE ROWNUM<=1e100 -- fake ROWNUM check! this gets us on the fast path
  )
  WHERE ROWNUM<=50

这是优化器计划。

SELECT STATEMENT, GOAL = ALL_ROWS           38025   50  650
 COUNT STOPKEY                  
  VIEW  JSTILES     38025   801 10413
   SORT UNIQUE NOSORT           38025   801 3204
    COUNT STOPKEY                   
     VIEW   JSTILES     38024   801 3204
      TABLE ACCESS BY INDEX ROWID   WOWDEV  QUERYLOG    38024   545694  9276798
       INDEX FULL SCAN DESCENDING   WOWDEV  IX_QUERYLOG_TID 1263    212704

如果我注释掉虚假的 ROWNUM 检查，这个查询会立刻崩溃并变得更慢（而且成本增加了 5 倍）。

SELECT STATEMENT, GOAL = ALL_ROWS           204497  50  650
 COUNT STOPKEY                  
  VIEW  JSTILES     204497  34865   453245
   SORT GROUP BY STOPKEY            204497  34865   592705
    INDEX FAST FULL SCAN    WOWDEV  IX_QUERYLOG_USER_TID    204462  545694  9276798

显然，它也打了一个完全不适合的索引（一个显然不适合它的索引）。

如果我自然地简化查询并消除所有冗余，我们得到一个类似于差劲版本的执行计划，以及同样糟糕的性能。

SELECT * FROM 
(
  SELECT DISTINCT TransactionID
          FROM WOWDev.QueryLog
         WHERE UPPER(UserName) = UPPER('xyz')
           AND TransactionID IS NOT NULL
         ORDER BY TransactionID DESC
)
WHERE ROWNUM <= 50

解释如下：

SELECT STATEMENT, GOAL = ALL_ROWS           207527  50  650
 COUNT STOPKEY                  
  VIEW  JSTILES     207527  34865   453245
   SORT UNIQUE STOPKEY          207491  34865   592705
    INDEX FAST FULL SCAN    WOWDEV  IX_QUERYLOG_USER_TID    204462  545694  9276798

按照下面的建议，我尝试用ROWNUM>0替换了ROWNUM<=1e100，这也命中了快速路径，但计划略有不同：

SELECT STATEMENT, GOAL = ALL_ROWS           38025   50  650
 COUNT STOPKEY                  
  VIEW  JSTILES     38025   801 10413
   SORT UNIQUE NOSORT           38025   801 3204
    COUNT                   
     FILTER                 
      VIEW  JSTILES     38024   801 3204
       TABLE ACCESS BY INDEX ROWID  WOWDEV  QUERYLOG    38024   545694  9276798
        INDEX FULL SCAN DESCENDING  WOWDEV  IX_QUERYLOG_TID 1263    212704

有人能解释这种行为吗？有没有更干净、不那么hacky的方法来让Oracle走快速通道？

- StilesCrisis

SELECT * FROM   (       SELECT DISTINCT TransactionID FROM WOWDev.QueryLog WHERE UPPER(UserName)=UPPER('xyz') AND TransactionID IS NOT NULL ORDER BY TransactionID DESC   )   WHERE ROWNUM<=50

的执行计划是什么？（这应该立即强制使用 INDEX FULL SCAN 和 COUNT STOPKEY） - potashin

我明天早上会查看并回复你。 - StilesCrisis

我已经发布了那个计划。它与慢版本没有什么显著的变化。 - StilesCrisis

3个回答

0

据我回忆，将圆形谓词放在子查询中的一个影响是防止谓词推送和某些类型的视图合并。

虽然它们通常都对查询性能有益，但在某些情况下，它们会产生不良的副作用。这曾经是一个技巧，可以代替优化提示来防止查询转换被考虑。

- David Aldridge

-1

您通过添加“虚假”的romwnum WHERE ROWNUM<=1e100 来减少可能的结果。

这指示数据库 ROWNUM 最多必须为 1e100。

第一次查询更快的原因是外部选择需要处理一个较小的子集。

也许你可以通过以下方法实现“更快”的结果：

SELECT * FROM
  (
    SELECT DISTINCT * FROM 
    ( 
      SELECT TransactionID FROM WOWDev.QueryLog WHERE UPPER(UserName)=UPPER('xyz') AND TransactionID IS NOT NULL ORDER BY TransactionID DESC
    ) 
    WHERE ROWNUM<=50 -- 将其移到内部以获取较小的结果集
  )

- Vhortex

1

你知道1e100意味着10的100次方，对吧？那是一个非常大的数字。无论如何，Oracle都无法处理那么多数据。 - StilesCrisis

SELECT DISTINCT TransactionID FROM WOWDev.QueryLog WHERE UPPER(UserName)=UPPER('xyz') AND TransactionID IS NOT NULL ORDER BY TransactionID DESC - Vhortex

现在你完全摆脱了 ROWNUM 检查。这甚至不是等价的。 - StilesCrisis

我刚刚去掉了你低效的额外嵌套，即DISTINCT那个。有什么问题吗？如果你不想要答案，那么为什么要在这里发问呢？ - Vhortex

据我所看，“如果我注释掉虚假的 ROWNUM 检查，查询会崩溃并变得更慢”... 你自己说多余的 ROWNUM 检查是不必要的，因为它是虚假的，而现在你抱怨我将其删除了？ - Vhortex

显示剩余2条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Nick.McDermaid · Accepted Answer

这不一定是正确的答案，但我过去曾使用 ROWNUM > 0 来强制“物化”数据。这反过来可以使查询处理器正确获取基数。

例如，如果查询计划器认为特定谓词只会返回一行，则通常会在其上使用笛卡尔积。如果实际数据不是一行而是很多行，则笛卡尔积将导致大量行和大量不正确的处理。添加 ROWNUM > 0 强制它在评估 ROWNUM > 0 之前对每一行求值，从而有效地强制数据物化。

然而，看起来这并不是你目前的问题所在。

也许由于数据分散，物化后搜索表比尝试先检查索引更快。

在计划转储中有没有一种方法可以确定谓词应用的位置？

很遗憾你必须对字段进行 UPPER 操作，因为它使其无法被 SARGABLE 化，且不会在该字段上使用索引。