Oracle“优化"OR + IN 到 OR + EXISTS 非常慢 [英] Oracle "optimizes" OR + IN to OR + EXISTS which is very slow
问题描述
我有以下查询:
select * from application_log log
where log.tag_value = 'xxx'
or log.tag_value in (select transaction.id
from transaction transaction where transaction.sale_id = 'xxx')
order by log.log_date asc;
并根据解释计划将子查询转换为类似的内容:
and based on the explain plan the subquery is transformed to something like that:
EXISTS (SELECT 0 FROM TRANSACTION TRANSACTION
WHERE TRANSACTION.SALE_ID='xxx' AND TRANSACTION.ID=:B1)
所以 IN
被替换为 EXISTS
并且子查询不是执行一次而是针对 application_log 表中的每条记录,如果 application_log 中有超过一百万条记录,这将非常慢表.
So IN
is replaced with EXISTS
and subquery is not executed once but for each record in application_log table which is very slow if there are over one million of records in application_log table.
我可以用 UNION
替换 OR
,这非常快,但由于 Hibernate (JPA) 不支持 UNION
这不是解决方案我.
I can replace OR
with UNION
which is very fast, but as Hibernate (JPA) does not support UNION
this is not solution for me.
select * from application_log log
where
log.tag_value in (select 'xxx' from dual union select transaction.id
from transaction transaction where transaction.sale_id = 'xxx')
order by log.log_date asc;
那么是否有一些 Oracle 提示告诉 Oracle 不要使用 EXISTS
或其他方式来优化"查询,我该如何重写此查询?
So is there some Oracle hint which tells Oracle to not "optimize" query using EXISTS
or some other way how I could rewrite this query?
推荐答案
您可以将子查询转换为连接:
You could transform your subquery into a join:
SELECT *
FROM application_log log
LEFT JOIN transaction transaction ON log.tag_value=transaction.id AND transaction.sale_id = 'xxx'
WHERE log.tag_value = 'xxx' OR transaction.id IS NOT NULL
ORDER BY log.log_date ASC;
这篇关于Oracle“优化"OR + IN 到 OR + EXISTS 非常慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!