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屋！