如何避开Rownum？

Question

由于批处理约束，下面的查询将获取1000行，并且应该只获取1000行。如果我不使用rownum，那么只需5秒就能获得1000多个recs。但是罗姆花了20秒钟。

SELECT  E.INFO_ID
FROM TAB1 E LEFT OUTER JOIN TAB2 D
ON E.INFO_ID= D.INFO_ID 
WHERE D.INFO_ID IS NULL AND ROWNUM < 1000;

请帮助我在不影响功能的情况下调优查询。

Answer 1

看看执行计划。可能优化器认为，通过遵循不同的路径，它可以更快地获得前1000个结果，而对于完整的数据，它使用一个散列连接之类的方法--这令人惊讶地发现，在第一个记录上速度很快。

一旦知道了执行计划，就可以使用提示让优化器遵循路径，从您的经验中可以知道这一点更好。

无论如何，您请求的是在tab1中不存在的tab2记录，但是不是这样说不存在，而不是in或减号，您可以使用左联接来隐藏这一点。这有时会更快，但毕竟是个诡计。为什么不以更直截了当的方式重写查询，看看它是如何执行的呢？我认为这样的说法可能会更稳定一些，比如稍微修改一下，比如使用一个行限值。值得一试。

编辑:一些澄清。您需要的是tab1中存在的in，而不是tab2中的in。这将是：

SELECT INFO_ID FROM TAB1 
MINUS
SELECT INFO_ID FROM TAB2;

您还可以对任务进行不同的表述，例如:我希望tab1中不存在tab2中的所有in：

SELECT INFO_ID FROM TAB1
WHERE NOT EXISTS 
(
  SELECT * FROM TAB2
  WHERE TAB2.INFO_ID = TAB1.INFO_ID
);

或者:我想要tab1中所有不在tab2中的in

SELECT INFO_ID FROM TAB1
WHERE INFO_ID NOT IN
(
  SELECT INFO_ID FROM TAB2
);

相反，您要做的是:对于tab1中的每个ID，查找tab2中的所有匹配ID，并将这些ID组合起来。对于在tab2中没有匹配的选项卡1中的in，也给我一个结果记录。然后，从这组(可能是很大的)结果中删除所有匹配项，以便保留那些没有匹配的ID。

许多词来描述同样的任务。因此，对于不熟悉这种技巧的人来说，查询是不容易阅读的。查询肯定会产生一个较大的中间结果。那么为什么人们会使用它呢？数据库系统是在连接的基础上成长起来的，所以这是他们真正擅长的事情。例如，他们使用哈希机制来连接记录，而不是循环记录。因此，尽管提出了一种相当复杂的访问方式，但左连接技术可能会带来良好的性能。

然而，上面的查询更直截了当.让我们看一下第一个；一个可能的执行计划是: Order tab1 ID，Order tab2 ID，然后循环一次以保持tab1 ID与tab2匹配。非常简单。排序需要时间，但随后您会顺序地检查这两个结果。如果这恰好给了您第一次一千次匹配，那么当您限制ROWNUM < 1000的结果时，很可能会这样做。第二个问题呢？循环遍历tab1，在tab2中查找匹配的id，如果没有匹配，请保存该记录。使用索引可能很快，添加ROWNUM < 1000可能不会改变获取第一条记录的速度，因为执行路径保持不变。第三个查询:可以解释为第二个查询。或者将tab2 ID放入具有快速访问权限的数组中。无论如何，ROWNUM < 1000不太可能在访问路径中发生很大变化。

然而，对于您的查询，很难说。当所有记录都必须被视为时，加入可能是最快的。但是如果只有一些记录就足够了，为什么要加入所有的东西呢？也许优化器决定按tab1的记录进行记录，然后在tab2中寻找匹配项。这将极大地改变执行计划，对于前1000条记录来说，速度可能更快。它只是不能保证是这样和坏运气，因为在你的情况下，它可能会变得更慢。

毕竟，Oracle有一个很棒的优化器。查询会被重写，您的查询可能会变成不存在的查询，反之亦然。即使没有重写:尽管处理不同的查询，优化器仍然可以决定相同的执行计划。所以你永远不会知道。但它总是值得一试。

我的建议是:直接写SQL。SQL语句通常类似于一个人如何用语言表达它的任务。正如上面所示。只有当面临性能问题时，才考虑如何重写查询来处理这个问题。