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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
