I apologize if this is a duplicate question: I've searched but finding many non-related topics/questions.
For instance
select *
from table_of_things
where id in (1, 2, 3, 4, 5, 6, 1, 2, 1)
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.
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.
Use autotrace on your query if you want to check it out for yourself.