|
|
|
|
|
|
| Re: IN (or) Equality Operator [message #651570 is a reply to message #651569] |
Fri, 20 May 2016 02:50   |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
IN is internally converted to OR (see the filter predicate):
SQL> explain plan for select * from dual where dummy in ('A','B','C');
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):
---------------------------------------------------
1 - filter("DUMMY"='A' OR "DUMMY"='B' OR "DUMMY"='C')
I'd like to know the reasons of each of the different views.
[Updated on: Fri, 20 May 2016 02:52] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: IN (or) Equality Operator [message #651616 is a reply to message #651593] |
Fri, 20 May 2016 15:46 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Performance is the same.
However, there is a limit on how many values you can have in an "IN (...)" clause.
I am not aware of any limit on "OR = ..." conditions.
If you have a long list and they can be selected from somewhere, then ideally:
SELECT ...
FROM ...
WHERE ... IN
(SELECT ...
FROM ...);
[Updated on: Fri, 20 May 2016 15:47] Report message to a moderator |
|
|
|
| Re: IN (or) Equality Operator [message #651617 is a reply to message #651566] |
Fri, 20 May 2016 16:16 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You need to be careful with NULLs with this sort of thing. For example, two queries that you might think would give the same result:
orclz>
orclz> update emp set ename=null where ename='MILLER';
1 row updated.
orclz> create index ename_i on emp (ename);
Index created.
orclz> set autot on exp
orclz> select * from emp where ename='KING' or ename is null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7839 KING PRESIDENT 1981-11-17:00:00:00 5000 10
7934 CLERK 7782 1982-01-23:00:00:00 1300 10
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 174 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 2 | 174 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME" IS NULL OR "ENAME"='KING')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
orclz> select * from emp where ename in ('KING',null);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7839 KING PRESIDENT 1981-11-17:00:00:00 5000 10
Execution Plan
----------------------------------------------------------
Plan hash value: 4162339657
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ENAME_I | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='KING')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
orclz>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
