Oracle-L: RE: Purely for your amusement

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Thu, 27 Feb 2003 15:29:51 -0800
Message-ID: <F001.0055C582.20030227152951@fatcity.com>

The numbers look normal based on three Cartesian joins.

Multiply the card for those Cartesians and you will get big numbers.

Is the sql missing the join predicates(I'm tired to look further)?

Regards,

Waleed

-----Original Message-----
Sent: Thursday, February 27, 2003 1:24 PM To: Multiple recipients of list ORACLE-L

And you thought your job was bad . . .
(Yes, this IS a production job)

0 SELECT STATEMENT Optimizer=CHOOSE
(Cost=623898383366807000000000

Card=81143391896031200000000000000

1 0 MERGE JOIN (Cost=623898383366807000000000 Card=81143391896031200000000000000
Bytes=535546386513806

   2    1     SORT (JOIN)
   3    2       MERGE JOIN (CARTESIAN)

(Cost=1323033411994580000000

Card=518075818229206000000000 Bytes=321725

4 3 MERGE JOIN (CARTESIAN)
(Cost=190827283721623000 Card=28145586908741600000
Bytes=150578889961

5 4 MERGE JOIN (OUTER)
(Cost=301983192525 Card=40967578733025

Bytes=21385076098639100)

6 5 MERGE JOIN (Cost=301983182527 Card=40967578733025 Bytes=19377664740720800)

   7    6               SORT (JOIN)
   8    7                 MERGE JOIN (CARTESIAN)

(Cost=2532679282 Card=1590809983140

Bytes=322934426577420)

   9    8                   VIEW OF 'WO_CHARG_VIEW'


(Cost=725242 Card=2615655 Bytes=347882115)

  10    9                     SORT (GROUP BY)
  11   10                       TABLE ACCESS (BY

ROWID) OF 'WO_CHARG' (Cost=690469 Card=2615655 Bytes=68007030

  12   11                         INDEX (FULL SCAN) OF
'PK_WO_CHARG' (UNIQUE)
  13    8                   SORT (JOIN)
  14   13                     TABLE ACCESS (FULL) OF
'PUB' (Cost=968 Card=608188 Bytes=42573160)
  15    6               SORT (JOIN)
  16   15                 TABLE ACCESS (FULL) OF 'WO'


(Cost=190903 Card=15662455 Bytes=4228862850)

  17    5             SORT (JOIN)
  18   17               TABLE ACCESS (FULL) OF 'NAD'


(Cost=2792 Card=500401 Bytes=24519649)

  19    4           SORT (JOIN)
  20   19             TABLE ACCESS (FULL) OF 'CNR'
  21    3         SORT (JOIN)
  22   21           TABLE ACCESS (FULL) OF 'WOE'


(Cost=47 Card=18407 Bytes=1583002)

  23    1     SORT (JOIN)
  24   23       TABLE ACCESS (FULL) OF 'WO'

(Cost=190903 Card=15662455 Bytes=610835745)

Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Barbara Baker
  INET: barbarabbaker_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Thu Feb 27 2003 - 17:29:51 CST