Notes in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st
"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message
news:73e20c6c.0407111642.7e9a2710_at_posting.google.com...
> 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
Oracle has been able to turn some IN subqueries into
equivalent EXISTS subqueries for a long time - probably
since CBO first appears, maybe even earlier - and each
major release of Oracle has introduced more strategies
for handling subqueries, including the surprise appearance
of lines like:
VIEW VW_SQ_1
and
VIEW VW_NSO_1
However, if you look closely at the two plans, you will
see that in this case, the IN subquery has been unnested
and turned into a driving in-line view - which then happens
to be operated as a nested loop; whereas the EXISTS
subquery has been turned into a semi-join - which then
happens to be operated as a nested loop.
The (main) reason why both queries show exactly the same
logical I/O is that the resulting join is based on PK to PK,
so the semi-join's strategy of stopping after one row is never
relevant.
Received on Wed Jul 14 2004 - 12:15:02 CDT
