CODEWITHSUNDEEP
sqloracle-database
AJFaraday
AJFaraday

Reputation: 2450

Oracle: What is the difference between = and in with one option?

So, here are two statements which return the same results.

SELECT * FROM USERS WHERE ID = 1;
-----------------------
SELECT * FROM USER WHERE ID IN (1);

Sometimes it's easier to generate the second query from a single item, with the option of adding additional items to the list.

Is there any inherent risks in using a single-item list in Oracle? Is it likely to cause performance issues?

Upvotes: 1

Views: 51

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

The two queries you have shown have no difference except the syntax. Compare the explain plan:

Query 1:

SQL> EXPLAIN PLAN FOR SELECT * FROM dual WHERE dummy = 'X';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------

   1 - filter("DUMMY"='X')

13 rows selected.

Query 2:

SQL> EXPLAIN PLAN FOR SELECT * FROM dual WHERE dummy IN 'X';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------

   1 - filter("DUMMY"='X')

13 rows selected.

In both the queries, internally the filter applied is filter("DUMMY"='X').

However, when you have multiple values int he IN list, then Oracle internally interprets as multiple OR conditions.

IN list

SQL> EXPLAIN PLAN FOR SELECT * FROM dual WHERE dummy IN ('X', 'Y', 'Z');

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------

   1 - filter("DUMMY"='X' OR "DUMMY"='Y' OR "DUMMY"='Z')

13 rows selected.

You can see that Oracle internally interprets it as filter("DUMMY"='X' OR "DUMMY"='Y' OR "DUMMY"='Z')

Upvotes: 5

Related Questions