Does the amount of IN parameters change the execution plan of a query?

Question

Let's consider those queries:

select * from person where com_code in (1, 2, 3, 4)
select * from person where com_code in (1, 2, 2, 2)
select * from person where com_code in (1, 2, 3)
select * from person where com_code in (0,1,2,3,4,5,6,7,8,9,10,11,12,13)

Do they have different execution plan stored in Oracle's memory? In other words, does the amount of IN parameters change the execution plan? Will there be an execution plan for 1 parameter, another one for 2 parameters, etc.?

Answer 1

Yes the execution plan changes. First of all, the conditions are part of the plan. So checking whether id in (1,2) or id in (1,2,3) means two different comparisons and hence two different plans. The general plan, though, which tables to access in which order and by what means can be the same.

But the don't have to be the same, so even the route Oracle decides for can change. Take a table of 1000 rows for instance and a query with where id in (...). If the IN clause contains one or two IDs, Oracle will use the index. With all thousand IDs in the IN clause, Oracle will decide for a full table scan instead.

select * from thousandrows where id in (1,2); -- index
select * from thousandrows where id in (1,...,1000); -- full table scan

And as to the plans in Oracle's memory, I see from V$SQL_PLANthat Oracle stores the plans per query. So with two queries with the exact same execution plan like these two:

select * from thousandrows where id in (1,2);
select * from thousandrows where id = 1 or id = 2;

Oracle will still have two copies of that same plan in memory, one for each query.

Answer 2

My test in SQL Developer shows that the cost of the query will be larger when more parameters are in in operator.

One value:

Two values:

Three values:

So, to answer your question: "Does the amount of IN parameters change the execution plan of a query?" YES.

This can also help: How efficiently does Oracle handle a very long IN operator list