Now I want change my predicate to just a little bit complex one. Exactly speaking from equality predicates to inlist predicates. But due to this small change, we have a very surprising effect on execution plan.

In the above queries, Oracle should transform the select part of the merge statement into outer join query(this is how merge works). From 10053 trace, I’ve found that Oracle transformed the queries in very different ways for above 2 cases.

Inlist predicates are converted to OR predicates, which is quite natural.

Oracle failed at merging the internal subquery due to the OR predicates. Even MERGE hint couldn’t force the view merging. It’s very common for Oracle to ignore efficient query transformation with OR predicates involved.

With view merge disabled, we’re left with correlated subquery.

There is no choice but to nested loops join due to the complex correlated subquery.

The result is a very inefficient nested loops outer join between the 2 tables.

Aha! This is the stereotypical query transformation problem! One of the solutions is to remove redundant inlist predicate on table t2 as following: