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.?
Will there be an execution plan for 1 parameter, another one for 2 parameters, etc.?
The SQL engine will check the text of the query and see if it is identical to an existing query:
For your examples, there will be one execution plan for each query regardless of the number of parameters as the query text is different for each query. So there would be 4 different execution plans for the 4 different queries.
If you use bind parameters:
select * from person where com_code in (:a, :b, :c, :d);
and you pass in 1, 2, 3 and 4 or you pass in 2, 2, 2 and 2 then the SQL engine will see that the text of the second query is identical to the first and, assuming that the table statistics are unchanged, will re-use the execution plan from the first query for the second (even though the bind variables are different).
If you vary the number of bind parameters then a different execution plan will be generated to match the number of filters.
If you do not use bind parameters then the SQL engine will have to parse text of the query with each change and may not generate the same query plan.
For example:
select * from person where com_code in (2, 2, 2, 2);
Will be rewritten by the SQL engine to:
select * from person where com_code = 2;
and would generate a different explain plan to:
select * from person where com_code in (1, 2, 3, 4);
db<>fiddle here (Remember to expand the explain plans to see the full details)
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
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.