IN vs OR of Oracle, which faster?

IN is preferable to OR -- OR is a notoriously bad performer, and can cause other issues that would require using parenthesis in complex queries.

Better option than either IN or OR, is to join to a table containing the values you want (or don't want). This table for comparison can be derived, temporary, or already existing in your schema.

In this scenario I would do this:

1. Create a one column global temporary table
2. Populate this table with your list from the external source (and quickly - another whole discussion)
3. Do your query by joining the temporary table to the other table (consider dynamic sampling as the temporary table will not have good statistics)

This means you can leave the sort to the database and write a simple query.

Oracle internally converts IN lists to lists of ORs anyway so there should really be no performance differences. The only difference is that Oracle has to transform INs but has longer strings to parse if you supply ORs yourself.

Here is how you test that.

CREATE TABLE my_test (id NUMBER);SELECT 1 FROM my_testWHERE id IN (1,2,3,4,5,6,7,8,9,10,             21,22,23,24,25,26,27,28,29,30,             31,32,33,34,35,36,37,38,39,40,             41,42,43,44,45,46,47,48,49,50,             51,52,53,54,55,56,57,58,59,60,             61,62,63,64,65,66,67,68,69,70,             71,72,73,74,75,76,77,78,79,80,             81,82,83,84,85,86,87,88,89,90,             91,92,93,94,95,96,97,98,99,100             );SELECT sql_text, hash_valueFROM v$sql WHERE sql_text LIKE '%my_test%';SELECT operation, options, filter_predicatesFROM v$sql_planWHERE hash_value = '1181594990'; -- hash_value from previous query

SELECT STATEMENT
TABLE ACCESS FULL ("ID"=1 OR "ID"=2 OR "ID"=3 OR "ID"=4 OR "ID"=5 OR "ID"=6 OR "ID"=7 OR "ID"=8 OR "ID"=9 OR "ID"=10 OR "ID"=21 OR "ID"=22 OR "ID"=23 OR "ID"=24 OR "ID"=25 OR "ID"=26 OR "ID"=27 OR "ID"=28 OR "ID"=29 OR "ID"=30 OR "ID"=31 OR "ID"=32 OR "ID"=33 OR "ID"=34 OR "ID"=35 OR "ID"=36 OR "ID"=37 OR "ID"=38 OR "ID"=39 OR "ID"=40 OR "ID"=41 OR "ID"=42 OR "ID"=43 OR "ID"=44 OR "ID"=45 OR "ID"=46 OR "ID"=47 OR "ID"=48 OR "ID"=49 OR "ID"=50 OR "ID"=51 OR "ID"=52 OR "ID"=53 OR "ID"=54 OR "ID"=55 OR "ID"=56 OR "ID"=57 OR "ID"=58 OR "ID"=59 OR "ID"=60 OR "ID"=61 OR "ID"=62 OR "ID"=63 OR "ID"=64 OR "ID"=65 OR "ID"=66 OR "ID"=67 OR "ID"=68 OR "ID"=69 OR "ID"=70 OR "ID"=71 OR "ID"=72 OR "ID"=73 OR "ID"=74 OR "ID"=75 OR "ID"=76 OR "ID"=77 OR "ID"=78 OR "ID"=79 OR "ID"=80 OR "ID"=81 OR "ID"=82 OR "ID"=83 OR "ID"=84 OR "ID"=85 OR "ID"=86 OR "ID"=87 OR "ID"=88 OR "ID"=89 OR "ID"=90 OR "ID"=91 OR "ID"=92 OR "ID"=93 OR "ID"=94 OR "ID"=95 OR "ID"=96 OR "ID"=97 OR "ID"=98 OR "ID"=99 OR "ID"=100)