加入耗时太长

Question

我有一个查询，运行时间太长，我认为这是不正常的。我猜一些提示/优化器会有所帮助，但我对此了解不多。因此，任何帮助都将不胜感激。

基本上代码是：

with datasetA as (),
     datasetB as (),
     datasetC as ()
select a.*
from datasetA a
     join datasetB b on b.key = a.key
     join datasetC c on c.key = a.key;

每个子查询(datasetA、B、C)运行只需要1秒，但是连接需要30分钟……我100%确定这是一个1对1的连接，数据集很小(比如1000行)，最终输出也很小。

Answer 1

您需要在所有表的join外键列上添加索引，这将提高性能。

Answer 2

您可能缺少索引

点击下面的链接，它应该会对你有所帮助。它应该将查询的总执行时间减少至少100倍

https://www.w3schools.com/sql/sql_create_index.asp

此外，如果您可以发布来自table1的SHOW INDEX、来自table2的SHOW INDEX的结果，我们就可以确定缺少的索引

Answer 3

Oracle可能正在应用一个糟糕的优化器转换，将三个独立运行的快速查询转换为一个运行速度较慢的查询。禁用这些转换并确保每个查询快速运行的最简单方法是添加一个看似毫无价值的ROWNUM条件，如下所示：

with datasetA as (... where rownum >= 1),
     datasetB as (... where rownum >= 1),
     datasetC as (... where rownum >= 1)
select a.*
from datasetA a
     join datasetB b on b.key = a.key
     join datasetC c on c.key = a.key;

Oracle伪列用于top-N报告，如果ROWNUM重写每个查询，这将不起作用。因此，即使条件在逻辑上是多余的，优化器也会保留这些子查询。

上面是解决当前问题的简单方法，但不一定是最好的方法。您可能想要调查为什么Oracle选择糟糕的转换；糟糕的优化器统计数据通常是罪魁祸首。找到根本原因可能有助于解决其他问题，但可能是一个困难的过程。

如果您对Oracle为什么要这样做感到好奇，那么优化器转换通常是一件好事。视图合并和谓词推送等转换允许Oracle将知识从查询的一个部分应用到另一个部分，这通常会显著改善运行时间。例如，假设有一个像select * from (select * from huge_table) where primary_key = 1;这样的查询。我们当然希望将该谓词推入子查询中，这样Oracle就不必为了得到一行而读取整个表。