Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: optimizer question
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...Received on Sat Mar 20 2004 - 07:22:52 CST
> 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.
>
![]() |
![]() |