Oracle-L: RE: Help....With weird join...

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Tue, 12 Mar 2002 07:33:25 -0800
Message-ID: <F001.00425AA2.20020312073325@fatcity.com>

Hmmmm.... Are these base tables or views? Have you explained both statements? Have you looked at consistent gets and so forth.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu

-----Original Message-----
Sent: Tuesday, March 12, 2002 4:28 AM
To: Multiple recipients of list ORACLE-L

I got an interesting email yesterday from our Business Objects Admin... Anyone know why ADDING a duplicate join would make this run faster...?

April Wells
Corporate Systems
Amarillo Texas

I am trying to clean up some junk in one of the universes. Below is a sql statement that runs in less than one second. If you will notice there are 2 joins that are identical between invoice and line item. When I delete the duplicate, it runs forever -- it seems to me that it should be the opposite since it would have to make the same pass through twice.....

SELECT

  VINVOCE.ACCT_NO,
  VINVOCE.INV_NO,
  VINVOCE.BILLING_NO,

decode(substr(VLINITM.ADJDIAG_CD,4,1),null,VLINITM.ADJDIAG_CD,substr(VLI NITM.ADJDIAG_CD,1,3) ||
'.' || substr(VLINITM.ADJDIAG_CD,4,3)),
  sum(VLINITM.CHARGE_AMT)
FROM
  VINVOCE,
  VLINITM
WHERE
( VLINITM.BILLING_NO=VINVOCE.BILLING_NO )   AND ( VLINITM.BILLING_NO=VINVOCE.BILLING_NO )   AND (
  VINVOCE.ACCT_NO = '12345'
  )
GROUP BY

  VINVOCE.ACCT_NO, 
  VINVOCE.INV_NO, 
  VINVOCE.BILLING_NO,

decode(substr(VLINITM.ADJDIAG_CD,4,1),null,VLINITM.ADJDIAG_CD,substr(VLI NITM.ADJDIAG_CD,1,3) ||
'.' || substr(VLINITM.ADJDIAG_CD,4,3))

begin 666 InterScan_Disclaimer.txt

M5&AE(&EN9F]R;6%T:6]N(&-O;G1A:6YE9"!I;B!T:&ES(&4M;6%I;"!I<R!S
M=')I8W1L>2!C;VYF:61E;G1I86P_at_86YD(&9O<B!T:&4@:6YT96YD960@=7-E
M(&]F('1H92!A9&1R97-S964@;VYL>3L@:70@;6%Y(&%L<V\@8F4@;&5G86QL

M>2!P<FEV:6QE9V5D(&%N9"]O<B!P<FEC92!S96YS:71I=F4N("!.;W1I8V4@

M:7,@:&5R96)Y(&=I=F5N('1H870_at_86YY(&1I<V-L;W-U<F4L('5S92!O<B!C
M;W!Y:6YG(&]F('1H92!I;F9O<FUA=&EO;B!B>2!A;GEO;F4@;W1H97(@=&AA
M;B!T:&4@:6YT96YD960@<F5C:7!I96YT(&ES('!R;VAI8FET960_at_86YD(&UA

M>2!B92!I;&QE9V%L+B @268@>6]U(&AA=F4@<F5C96EV960@=&AI<R!M97-S M86=E(&EN(&5R<F]R+"!P;&5A<V4@;F]T:69Y('1H92!S96YD97(@:6UM961I M871E;'D_at_8GD@<F5T=7)N(&4M;6%I;"X*"D-O<G!O<F%T92!3>7-T96US+"!) M;F,N(&AA<R!T86ME;B!E=F5R>2!R96%S;VYA8FQE('!R96-A=71I;VX@=&\@

M96YS=7)E('1H870_at_86YY(&%T=&%C:&UE;G0@=&\@=&AI<R!E+6UA:6P@:&%S
M(&)E96X@<W=E<'0_at_9F]R('9I<G5S97,N("!792!A8V-E<'0@;F\@;&EA8FEL
M:71Y(&9O<B!A;GD_at_9&%M86=E('-U<W1A:6YE9"!A<R!A(')E<W5L="!O9B!S

M;V9T=V%R92!V:7)U<V5S(&%N9"!A9'9I<V4@>6]U(&-A<G)Y(&]U="!Y;W5R M(&]W;B!V:7)U<R!C:&5C:W,@8F5F;W)E(&]P96YI;F<@86YY(&%T=&%C:&UE %;G0N#0H
end

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: April Wells
INET: awells_at_csedge.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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.com
--

Author: MacGregor, Ian A.
INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------