Re: Very long "WHERE" list.

From: Noons <wizofoz2k_at_yahoo.com.au>
Date: 11 Jul 2004 17:42:04 -0700
Message-ID: <73e20c6c.0407111642.7e9a2710@posting.google.com>

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<ccrlc8$rt9$1_at_sparta.btinternet.com>...
> I would phrase that as:
>
> Putting 20000 values in an IN list MAY BE
> the most efficient way available of performing
> a lookup on those values
>
> I have to say that I would be quite surprised if I came
> across an application where it was really necessary.

Well, it gets even more interesting. Please excuse if google reformats some long lines.
Look at this. It comes from 9ir2 and the two tables have the row counts as shown:

SQL> select count(1) from EVE_table;
Press Return to continue...

       COUNT(1)

---

          31831

Elapsed: 00:00:00.06
SQL> select count(1) from EP_table;
Press Return to continue...

       COUNT(1)

---

          94094

Elapsed: 00:00:00.05
SQL> select count(1) from EP_table
  2 where evtref_id in (select evtref_id from EVE_table); Press Return to continue...

       COUNT(1)

---

          94094

Elapsed: 00:00:01.05
SQL> set autotrace on
SQL> /
Press Return to continue...

       COUNT(1)

---

          94094

Elapsed: 00:00:02.01

Execution Plan

---

   0 (nul SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=1 Bytes=12) (null)      l)

   1    0   SORT (AGGREGATE)                                           (null)
   2    1     NESTED LOOPS (Cost=28 Card=94045 Bytes=1128540)          (null)
   3    2       INDEX (FAST FULL SCAN) OF 'EP_PK' (UNIQUE) (Cost=28 Ca (null)
          rd=94045 Bytes=564270)

   4    2       INDEX (UNIQUE SCAN) OF 'EVE_PK' (UNIQUE)               (null)

Statistics

---

          0  recursive calls
          0  db block gets
      94391  consistent gets
          1  physical reads
          0  redo size
        212  bytes sent via SQL*Net to client
        274  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> ed
Wrote file afiedt.buf

  1 select count(1) from EP_table a
  2 where exists (select 1 from EVE_table b   3* where b.evtref_id = a.evtref_id) SQL> /
Press Return to continue...

       COUNT(1)

---

          94094

Elapsed: 00:00:02.01

Execution Plan

---

   0 (nul SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=1 Bytes=12) (null)      l)

   1    0   SORT (AGGREGATE)                                           (null)
   2    1     NESTED LOOPS (SEMI) (Cost=28 Card=94045 Bytes=1128540)   (null)
   3    2       INDEX (FAST FULL SCAN) OF 'EP_PK' (UNIQUE) (Cost=28 Ca (null)
          rd=94045 Bytes=564270)

   4    2       INDEX (UNIQUE SCAN) OF 'EVE_PK' (UNIQUE)               (null)

Statistics

---

          0  recursive calls
          0  db block gets
      94391  consistent gets
          0  physical reads
          0  redo size
        213  bytes sent via SQL*Net to client
        274  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> IOW, Oracle is now smart enough to replace the EXISTS and the IN by effectively the same code. Or am I reading it wrong? Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Sun Jul 11 2004 - 19:42:04 CDT