Oracle SQL DB是否删除where-in子句中的重复条目? [英] Do Oracle SQL DBs Remove duplicate entries in a where-in clause?

问题描述

如果这是一个重复的问题，我深表歉意:我已经搜索过，但发现了许多不相关的主题/问题.

I apologize if this is a duplicate question: I've searched but finding many non-related topics/questions.

例如

select * 
from table_of_things 
where id in (1, 2, 3, 4, 5, 6, 1, 2, 1)

Oracle预处理会在执行查询之前删除重复项吗?我怎么知道?我想在大列表中，性能损失可能是巨大的，最好在查询之前自行删除重复项.

Will Oracle pre-processing remove the duplicates before performing the query? How can I find out? I imagine in large lists the performance loss can be huge and it would be good to remove duplicates myself before querying if not.

推荐答案

像您一样，我找不到任何特定的文档条目来指定表达式列表是否由于性能原因而简化.必须将其视为实现细节.但是基于观察，它看起来确实像优化器执行了优化.

Like you, I couldn't find any specific documentation entry that specifies if the expression list gets simplified for performance reasons. It must be considered an implementation detail. But based on observation, it does appear like the optimizer performs the optimization.

在Oracle 12c上，我在打开autotrace的情况下运行了一个与您非常相似的示例查询，并且列出了所使用的过滤谓词.正如预期的那样，简化了谓词列表，以消除重复项.

On Oracle 12c, I ran an example query much like yours with autotrace turned on, and it listed the filter predicates it used. As expected, the list of predicates was simplified to eliminate the duplicates.

如果要自行检查，请在查询中使用autotrace.

Use autotrace on your query if you want to check it out for yourself.

这篇关于Oracle SQL DB是否删除where-in子句中的重复条目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！