April Wells wrote:
>
> 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))
>
The remark about the 'two passes' is wrong. My guess is that repeating
the condition makes it more attractive, compared to the VINVOCE.ACCT_NO
= '12345' condition, normally more attractive (constant) but which
musn't be very good - stupid question, but is this column indexed? I
would expect it to be selective. Are tables analyzed and CBO turned on?
As usual, running EXPLAIN would help. I find this kind of optimization
rather questionable. First, the least you can say is that it is not
really self-explanatory. You'd better check the execution plans and use
hints to reproduce the good one.
--
Regards,
Stephane Faroult
Oriole Ltd
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
INET: sfaroult_at_oriole.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).
Received on Tue Mar 12 2002 - 15:48:40 CST
