如何使这个查询在Oracle 10g限制下执行得更快?

Question

我有一个Oracle查询，执行可怕，可以做一些建议，什么可能是原因和/或建议如何改进它。我在下面详细介绍了我的原始查询和我所尝试的简化版本。

原始查询

Select * From
(
SELECT * FROM table1
Union All
SELECT * FROM table2
Union All
SELECT * FROM table3
Union All
SELECT * FROM table4
) GroupedData
LEFT JOIN
(
SELECT * FROM RecursiveCte
) ON GroupedData.id = RecursiveCte.id

我已将查询简化为通用的"select all";语句只是为了方便这个问题。

关于一些查询的几点…

GroupedData子查询实际上有4个以上的联合，每个联合所查看的数据量不同，但在日期过滤器返回的数据中受到限制。从该查询返回的总数据通常是1500条记录，尽管正在处理的数据量可能是数十万条记录。如果我单独运行这个查询，不到一秒钟就可以返回这1500行。

RecursiveCte子查询使用CONNECT BY功能，因为Oracle 10g没有递归CTE(这将更容易)。如果我单独运行这个查询，也需要不到1秒的时间。

当我试图通过LEFT join将两者连接在一起时，问题就出现了。当我这样做时，对于相同的日期范围参数，查询需要超过8分钟的时间来运行。

我试过在以下CTE格式中设置这些，但它们的性能都更差!

方法# 1

WITH GroupedData AS
(
SELECT * FROM table1
Union All
SELECT * FROM table2
Union All
SELECT * FROM table3
Union All
SELECT * FROM table4
) GroupedData,
RecursiveCte AS
(
SELECT * FROM RecursiveCte
)
Select * From
GroupedData
LEFT JOIN RecursiveCte ON GroupedData.id = RecursiveCte.id

方法# 2

WITH Query1 AS
(SELECT * FROM table1),
Query2 AS
(SELECT * FROM table2),
Query3 AS
(SELECT * FROM table3),
Query4 AS
(SELECT * FROM table4),
RecursiveCte AS
(
SELECT * FROM RecursiveCte
)
Select * From
(
Select * From Query1
Union All
Select * From Query2
Union All
Select * From Query3
Union All
Select * From Query4
) GroupedData
LEFT JOIN RecursiveCte ON GroupedData.id = RecursiveCte.id

在Oracle 10g的限制之上，我还使用具有只读权限的数据库用户运行，这限制了我在数据库内可以做的事情。

任何帮助是非常感激的，并提前抱歉，如果我没有提供足够的背景!

感谢

Answer 1

当您有两个查询单独运行快速，但一起运行缓慢时，最简单的解决方案通常是添加ROWNUM，如下所示:

Select * From
(
...
--Prevent optimizer transformations to improve performance:
WHERE ROWNUM >= 1
) GroupedData
LEFT JOIN
(
SELECT * FROM RecursiveCte
--Prevent optimizer transformations to improve performance:
WHERE ROWNUM >= 1
) ON GroupedData.id = RecursiveCte.id

查看我的回答，这里有关于这个技巧为什么有效的更详细的解释。

虽然上面的技巧通常是最简单的解决方案，但它通常不是最好的解决方案。总有一个原因，为什么Oracle重写你的查询很糟糕;可能缺少表统计信息，或者条件太复杂，Oracle无法估计返回的行数，等等。但是，如果您现在不想花几个小时调查SQL监视报告，那么可以采取快捷方式。