optimizer question

From: <samuels_at_red.seas.upenn.edu>
Date: Fri, 19 Mar 2004 21:36:43 +0000 (UTC)
Message-ID: <c3fp5b$36ih$1@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)

also, which query is better (more efficient)? #2 returns faster but is necessarily any more efficient than query1?

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 Fri Mar 19 2004 - 15:36:43 CST