我有一个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的限制之上,我还使用具有只读权限的数据库用户运行,这限制了我在数据库内可以做的事情。
任何帮助是非常感激的,并提前抱歉,如果我没有提供足够的背景!
感谢当您有两个查询单独运行快速,但一起运行缓慢时,最简单的解决方案通常是添加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监视报告,那么可以采取快捷方式。