使用 ROWNUM 优化 Oracle 查询

Question

这个问题现在没有意义

我重新计算了表的统计数据，添加了新索引，并重新分析了现有索引。这完全改变了我的结果，并使我下面的大部分发现无效。在这一点上，我发现了一个性能足够好的新查询，不需要任何 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<=1e100 替换我的 ROWNUM>0 并且这也达到了快速路径，但计划略有不同:

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  

谁能解释这种行为？是否有一种更简洁、更简单的方法让 Oracle 走上快速道路？

Answer 1

这不一定是正确答案，但是......

我用过 ROWNUM > 0过去强制“物化”数据。

这反过来又允许查询处理器获得正确的基数。

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

看起来这实际上不是你的问题。

可能是因为数据的传播，实现然后搜索表而不是尝试先检查索引会更快。

该计划转储中是否有办法确定应用谓词的位置？

很遗憾你必须UPPER字段，因为它使其不可 sargeable 并且它不会在该字段上使用索引。