Notes in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar
<samuels_at_red.seas.upenn.edu> wrote in message
news:c3fp5b$36ih$1_at_netnews.upenn.edu...
> okay, can someone explain to me what is going on here.
> i have 2 tables:
> TABLE_ONE
> -------
> FIRST_NAME
> MIDDLE_NAME
> LAST_NAME
> GENDER
>
> TABLE_TWO
> -------
> FIRST_NAME
> MIDDLE_NAME
> LAST_NAME
> NUM_CHILDREN
>
>
> on TABLE_ONE there is a primary key constraint made up of FIRST_NAME ,
MIDDLE_NAME , and LAST_NAME.
>
> here is the explain plan for 2 different queries (that return the same
result set):
> Query1
> select a.first_name , a.middle_name , a.last_name , a.gender from
table_one a , table_two b
> where
> a.first_name = b.first_name and
> a.middle_name = b.middle_name and
> a.last_name = b.last_name and
> b.num_children = 1;
> Execution Plan
> ----------------------------------------------------------
> 0 # SELECT STATEMENT Optimizer=CHOOSE (Cost=87118 Card=10254 Byt #
> es=656256)
> --------------------------------------------------------------------------
---------------------------------------------------
> 1 0 MERGE JOIN (Cost=87118 Card=10254 Bytes=656256) #
> 2 1 SORT (JOIN) (Cost=7872 Card=698792 Bytes=18168592) #
> 3 2 TABLE ACCESS (FULL) OF 'TABLE_TWO' (Cost=228 #
> 6 Card=698792 Bytes=18168592)
> --------------------------------------------------------------------------
---------------------------------------------------
> 4 1 SORT (JOIN) (Cost=79247 Card=6486358 Bytes=246481604) #
> 5 4 INDEX (FAST FULL SCAN) OF 'PK_TABLE_ONE' ( #
> UNIQUE) (Cost=10184 Card=6486358 Bytes=246481604)
> --------------------------------------------------------------------------
---------------------------------------------------
>
> Query2
> select first_name , middle_name , last_name , gender from table_one
> where (first_name , middle_name , last_name) in
> (select first_name , middle_name , last_name from table_two where
num_children = 1);
> Execution Plan
> ----------------------------------------------------------
> 0 # SELECT STATEMENT Optimizer=CHOOSE (Cost=87118 Card=10254 Byt #
> es=635748)
> --------------------------------------------------------------------------
---------------------------------------------------
> 1 0 MERGE JOIN (Cost=87118 Card=10254 Bytes=635748) #
> 2 1 VIEW OF 'VW_NSO_1' (Cost=7872 Card=698792 Bytes=16771008 #
> )
> --------------------------------------------------------------------------
---------------------------------------------------
> 3 2 SORT (UNIQUE) (Cost=7872 Card=698792 Bytes=18168592) #
> 4 3 TABLE ACCESS (FULL) OF 'TABLE_TWO' (Cost=2 #
> 286 Card=698792 Bytes=18168592)
> --------------------------------------------------------------------------
---------------------------------------------------
> 5 1 SORT (JOIN) (Cost=79247 Card=6486358 Bytes=246481604) #
> 6 5 INDEX (FAST FULL SCAN) OF 'PK_TABLE_ONE' ( #
> UNIQUE) (Cost=10184 Card=6486358 Bytes=246481604)
> --------------------------------------------------------------------------
---------------------------------------------------
>
>
> now, here's my question, what does this line mean (did oracle build some
kind of intermediate view and why)?
> 2 1 VIEW OF 'VW_NSO_1' (Cost=7872 Card=698792 Bytes=16771008)
>
VW_NSO_1: View, nested subquery optimisation
Oracle has built an scratch result set.
This is known as 'unnesting', and can be the most
efficient way of dealing with a subquery. (See Gaja
Vaidyanatha et. al. Tuning 101).
>
> also, which query is better (more efficient)?
Technically your two queries are not identical
because you don't have a unique constraint on
Table_2 (first_name, middle_name, last_name)
so the join query can return multiple rows for a
given name combination - the second query
makes each name combination unique before
driving into table_1. If you added a unique (or
pk) constraint to table_2, you would probably
find that the VW_NSO with it's sort(unique)
disappeared, and the execution plans for both
queries would be the same. (Unnesting would
still happen).
> #2 returns faster but is necessarily any more efficient than query1?
>
It seems a little unlikely that a sort(unique) should be faster
then a simple sort - unless lots of duplicates were eliminated
so that intermediate sort runs were smaller. Perhaps you had
some fringe buffering benefits if you just ran the two scripts one
after the other.
> i'm using Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production on an
IBM AIX5.1 machine.
>
> thanks in advance for any help.
> -maurice
>
> samuels_at_seas.upenn.edu
>
>
> ps --> the table name's and column name's are made up, but are
representative enough of my situation.
>
Received on Sat Mar 20 2004 - 07:22:52 CST
