Oracle AI Database 26ai introduces an efficient method for processing large SQL IN-lists, making analytics workloads faster and less resource-intensive.

For example, consider a query like:

select count(*) 
from   my_table 
where  n in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15, ... );

Previously, Oracle would process the IN-list as a long series of “OR” conditions in the execution plan:

Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    36 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   500 |  2500 |    36  (42)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("N"=1 OR "N"=2 OR "N"=3 OR "N"=4 OR "N"=5 OR "N"=6 OR
              "N"=7 OR "N"=8 OR "N"=9 OR "N"=10 OR "N"=11 OR "N"=12 ...

With Oracle AI Database 26ai, when your IN-list has 64 or more items, the execution plan can use a vectorized filter operation:

Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    36 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   500 |  2500 |    36  (42)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SYS_OP_KEY_VECTOR_FILTER("N",:KV0000))

Instead of chaining many “OR” conditions, the database applies a single vector filter (SYS_OP_KEY_VECTOR_FILTER), which runs much more efficiently.

It also works if you do something like this (as long as at least one of the IN-lists is 64 or more elements):

select count(*) 
from my_table 
where n in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15, ... )
or    n in (2000,3000,4000,5000 ...)

Because this is an internal SQL execution optimization, the plan hash value remains unchanged. If you manage execution plans with SQL baselines, plan matching continues to work as before.

Performance gains depend on several factors, including data types, SQL complexity, IN-list length, table size, and how the table is accessed. Typically, both parsing and execution CPU times decrease. For example, using a simple query on a narrow table with three million rows and a 1000-element IN-list, my CPU consumption dropped by 2–3X. Your results will vary.

Finally, ensure the datatype of the column matches the datatype of the IN-list elements; otherwise, the query may not use the vector filter.