Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: FW: Help....With weird join...
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