Re: Where Clauses: IN versus =

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Mon, 12 Sep 2005 18:54:56 +0100
Message-ID: <elfbi1lt8uir6289thclulsos81fd1er6k@4ax.com>

On Mon, 12 Sep 2005 13:01:14 -0230, Tim Marshall <TIMMY!@PurplePandaChasers.Moertherium> wrote:

>I'm writing a lot of code in a various apps to construct strings which
>are Oracle SQL statements.
>
>But what about when I have a single criteria? Is:
>
> Where FK_Whatever = 123
>
>faster/preferable to
>
> Where FK_Whatever in (123)?
>
>The reason I ask is because it is much easier to simply write the code
>to construct an in() rather than test to see how many criteria there
>are. I can and have done the latter for years, but wonder if I am just
>wasting my efforts.

 A simple test indicates that Oracle generates the same execution plan for "in (:x)" versus "= :x" which makes sense since it's semantically identical.

 Whether this is the case for more complex statements, you'd have to test for yourself, but it seems reasonable that it'd be a simple replacement optimisation done early in the parsing process.

SQL> create table t (c number not null primary key);

Table created.

SQL> insert into t select rownum from dba_objects;

45714 rows created.

SQL> commit;

Commit complete.

SQL> set autotrace traceonly explain;
SQL> select c from t where c in (128, 129);

Execution Plan

---

Plan hash value: 3190859561

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

|   0 | SELECT STATEMENT  |             |     2 |    26 |     3   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR  |             |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| SYS_C003638 |     2 |    26 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   2 - access("C"=128 OR "C"=129)

SQL> select c from t where c = 128;

Execution Plan

---

Plan hash value: 3830255846

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

|   0 | SELECT STATEMENT  |             |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| SYS_C003638 |     1 |    13 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   1 - access("C"=128)

SQL> select c from t where c in (128);

Execution Plan

---

Plan hash value: 3830255846

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

|   0 | SELECT STATEMENT  |             |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| SYS_C003638 |     1 |    13 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   1 - access("C"=128)

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Received on Mon Sep 12 2005 - 12:54:56 CDT