Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Win2k/8.1.7.3/Optimizer Weirdness

RE: Win2k/8.1.7.3/Optimizer Weirdness

From: Gogala, Mladen <MGogala_at_oxhp.com>
Date: Tue, 19 Nov 2002 12:54:06 -0800
Message-ID: <F001.005072C6.20021119125406@fatcity.com>


It is very hard to believe that there is a big problem with merge join as it is the oldest join method, next to
the nested loops. Hash join and star schema are much newer. I remember merge joins at least from version 5.
Merge join was traditionally used with RBO. Whenever given the choice, I prefer hash join as it is usually signifficantly faster.

-----Original Message-----

Sent: Tuesday, November 19, 2002 3:04 PM To: Multiple recipients of list ORACLE-L

Here are the different explain plans. This is on an 8.1.7.0 db on Solaris. One idea is the problem could be on the merge-join. I've heard of some problems with this operation.  

SQL> select file_name from dba_temp_files;  

FILE_NAME




/db02/oradata/MDX817/temp_01.dbf  

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=21 Card=82 Bytes=26486)    1 0 NESTED LOOPS (Cost=21 Card=82 Bytes=26486)

   2    1     NESTED LOOPS (Cost=20 Card=1 Bytes=310)
   3    2       FIXED TABLE (FULL) OF 'X$KCCFN' (Cost=10 Card=1 Bytes=284)
   4    2       FIXED TABLE (FIXED INDEX #1) OF 'X$KTFTHC' (Cost=10 Card=100
Bytes=2600)
   5    1     TABLE ACCESS (CLUSTER) OF 'TS$' (Cost=1 Card=8168
Bytes=106184)
   6    5       INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
 
 
 
 
 

SQL> select /*+ RULE */ file_name from dba_temp_files;  

no rows selected  

Execution Plan


   0 SELECT STATEMENT Optimizer=HINT: RULE    1 0 MERGE JOIN

   2    1     SORT (JOIN)
   3    2       NESTED LOOPS
   4    3         FIXED TABLE (FULL) OF 'X$KTFTHC'
   5    3         TABLE ACCESS (CLUSTER) OF 'TS$'
   6    5           INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
   7    1     SORT (JOIN)
   8    7       FIXED TABLE (FULL) OF 'X$KCCFN'


-----Original Message-----

Sent: Tuesday, November 19, 2002 12:39 PM To: Multiple recipients of list ORACLE-L

9.2 on AIX ... same problem ... Wait they must have fixed it in 10i

Raj



Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----

<mailto:peter.gram_at_miracleas.dk> ]
Sent: Tuesday, November 19, 2002 2:15 PM To: Multiple recipients of list ORACLE-L

Same thing in 9.2.0.2 on linux

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gogala, Mladen
  INET: MGogala_at_oxhp.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 Tue Nov 19 2002 - 14:54:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US