Re: Purely for your amusement

From: <Jared.Still_at_radisys.com>
Date: Thu, 27 Feb 2003 15:29:52 -0800
Message-ID: <F001.0055C59E.20030227152952@fatcity.com>

Hmm.....

Joining 6 tables, but only 3 appear in the where clause.

Small wonder if it runs a long time.

Jared

Barbara Baker <barbarabbaker_at_yahoo.com>
Sent by: root_at_fatcity.com
 02/27/2003 02:51 PM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Re: Purely for your amusement

I found it in our "offending sql statements" report we get from the database each morning. Went looking when someone complained about poor performance. (Ya, do you THINK!!!!) Probably not Discoverer. I think it was either Access or Crystal. (When I found it, it had double quotes around everything, which is usually Access.)

On the bright side, our BHR looks great!!! Guess I'll go home and have a beer.

For Jared's entertainment, here's the sql:

SELECT WO.ACCT_KEY, WO.PUB, WO.ISSUE, WO.STATUS,

WO.AD_TYPE, WO.EDITION, WO.SECTION, WO.AD_DESC,
WO.COMMENTS, WO.JOB_NBR, WO.ACT_SIZE, WO.BASE_AMT,
WO.DISC_TOT,
WO.AGY_COMM, WO.SP_AGY_COMM, WO.INVC_AMT, WO.FREQ,
WO.CIRCULATION,
WO.RC_NBR, WO.AVG_RATE, NAD.NAM1, NAD.PROD_4,
PUB.SLS_EFF_ISS_1, PUB.SLS_NBR_1_1, PUB.SLS_EFF_ISS_2,

PUB.SLS_NBR_2_1, PUB.SLS_NBR_3_1, CNR.SLS_NBR_1, WOEMANIFEST_VIEW.WOE_EDITION, WO_CHARG_VIEW.PROD1,

WO_CHARG_VIEW.AMT1, WO_CHARG_VIEW.PROD2,
WO_CHARG_VIEW.AMT2, WO_CHARG_VIEW.PROD3,
WO_CHARG_VIEW.AMT3, WO_CHARG_VIEW.PROD4,
WO_CHARG_VIEW.AMT4, WO_CHARG_VIEW.AMT5,
WO_CHARG_VIEW.AMT6, WO_CHARG_VIEW.AMT7,
WO_CHARG_VIEW.AMT8, WO_CHARG_VIEW.AMT9

FROM ADMARC.WO WO,

ADMARC.NAD NAD,
ADMARC.PUB PUB,
ADMARC.CNR CNR,
ADMARC.WOEMANIFEST_VIEW WOEMANIFEST_VIEW,
ADMARC.WO_CHARG_VIEW WO_CHARG_VIEW

WHERE WO.ACCT_KEY = NAD.ACCT_NBR (+)
 AND WO.ACCT_KEY = PUB.ACCT_KEY
--

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

Author:
  INET: Jared.Still_at_radisys.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 Thu Feb 27 2003 - 17:29:52 CST