CODEWITHSUNDEEP
sqloracle-database
Praveen Rajaput
Praveen Rajaput

Reputation: 11

Is there any major query performance difference between IN, NOT IN operators compared to AND/OR

Is there any major query performance difference between IN and NOT IN operators compared to AND/OR

Upvotes: 1

Views: 46

Answers (1)

wolφi
wolφi

Reputation: 8361

I'd say generally there is no difference between IN or OR. However, it's better to verify this by looking at the query plans:

CREATE TABLE t AS SELECT object_type AS c FROM all_objects;
EXEC DBMS_STATS.GATHER_TABLE_STATS(null,'T');

EXPLAIN PLAN FOR 
SELECT * FROM T WHERE c IN ('INDEX', 'TABLE', 'SYNONYM');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
...
   1 - filter("C"='INDEX' OR "C"='SYNONYM' OR "C"='TABLE')

So, internally, Oracle converts the IN into a list of OR, at least for this example.

The version with OR generates exactly the same query plan and has therefore exactly the same performance as the version with IN:

EXPLAIN PLAN 
FOR SELECT * FROM T WHERE c='INDEX' OR c='TABLE' OR c='SYNONYM';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
   1 - filter("C"='INDEX' OR "C"='SYNONYM' OR "C"='TABLE')

NOT IN seems to be converted into an AND list:

EXPLAIN PLAN FOR 
SELECT * FROM T WHERE c NOT IN ('INDEX', 'TABLE', 'SYNONYM');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

   1 - filter("C"<>'INDEX' AND "C"<>'TABLE' AND "C"<>'SYNONYM')

Upvotes: 1

Related Questions