RE: RE: RE: CONSISTANT GETS

From: <Jared.Still_at_radisys.com>
Date: Fri, 15 Nov 2002 09:35:05 -0800
Message-ID: <F001.0050479C.20021115093505@fatcity.com>

Jerry,

I suspect that the improvments are more likely due to your rewriting the WHERE clause rather than the use of NOT EXISTS.

Especially if the database were 9i, where NOT IN actually seems get a better execution path than NOT EXISTS.

That original WHERE clause is really a piece of work.

Jared

"Whittle Jerome Contr NCI" <Jerome.Whittle_at_scott.af.mil> Sent by: root_at_fatcity.com
 11/15/2002 08:21 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: RE: RE: CONSISTANT GETS

I've seen worse. My programmers don't know how to use NOT EXISTS even though I've explained it many times. And that's the least of my problems. Look at this mess:

   SELECT *
     FROM sar.pax_header_suspense_err_temp     WHERE manifest_type

|| manifesting_station
|| fiscal_year
|| manifest_serial_number NOT IN (

             SELECT    manifest_type 
                    || manifesting_station 
                    || fiscal_year 
                    || manifest_serial_number 
               FROM manifest_serial_number_history) 

Takes over an hour to run. I rewrote it as such: SELECT *
  FROM sar.pax_header_suspense_err_temp t  WHERE NOT EXISTS
(SELECT 'X'
 FROM manifest_serial_number_history h
 WHERE

 t.manifest_type = h.manifest_type and 
 t.manifesting_station = h.manifesting_station and 
 t.fiscal_year = h.fiscal_year and 
       t.manifest_serial_number = h.manifest_serial_number ) 

Under a second.
Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
jerome.whittle_at_scott.af.mil
618-622-4145
-----Original Message-----
Raj,

    I needed a 12 pack adter this one, it's from PeopleSlop: SELECT 0001560265,OPL.BUSINESS_UNIT,OPL.PRODUCTION_ID,OPL.OP_SEQUENCE, PID.INV_ITEM_ID,TMP.COST_ELEMENT,'04',0,0,0,0,0,0,PID.ORIG_UOM,PID.PRDN_AREA_COD E,
PID.PRODUCTION_TYPE,OPL.QTY_SCRAPPED,OPL.PERCENT_COMP,' ',1,0,' ',' ',0000000000
FROM PS_BU_ITEMS_INV INV,PS_SF_PRDNID_HEADR PID,PS_CE_OP_LIST_COPY OPL, PS_CE_OP_LIST_VW OPLIST,PS_SF_COMP_LIST CMP,PS_CE_ITEMVAR_TMP TMP WHERE INV.BUSINESS_UNIT='VICOR'
AND PID.BUSINESS_UNIT='VICOR'
AND OPL.BUSINESS_UNIT='VICOR'
AND OPLIST.BUSINESS_UNIT='VICOR'

AND CMP.BUSINESS_UNIT='VICOR' 
AND TMP.BUSINESS_UNIT='VICOR' 
AND TMP.PROCESS_INSTANCE=0001560265 
AND OPL.PROCESS_INSTANCE=0001560265 
AND OPLIST.PROCESS_INSTANCE=0001560265 
AND OPL.PRODUCTION_ID= PID.PRODUCTION_ID 
AND OPL.PRODUCTION_ID= CMP.PRODUCTION_ID 

AND OPL.PRODUCTION_ID= OPLIST.PRODUCTION_ID AND INV.INV_ITEM_ID= PID.INV_ITEM_ID
AND ( OPL.OP_SEQUENCE= CMP.OP_SEQUENCE OR (CMP.OP_SEQUENCE = 0 AND OPL.OP_SEQUENCE = OPLIST.OP_SEQUENCE))

AND PID.PROD_STATUS BETWEEN   '30'  AND  '60' 
AND TMP.INV_ITEM_ID= CMP.COMPONENT_ID 
AND TMP.CONFIG_CODE= CMP.CONFIG_CODE 
AND CMP.SOURCE_CODE <> '5' 
AND CMP.NON_OWN_FLAG = 'N' 
AND NOT EXISTS (SELECT 'X' FROM PS_CE_SCRAPCST_TMP TMP2 
               WHERE TMP2.PROCESS_INSTANCE=0001560265 
               AND TMP2.BUSINESS_UNIT = OPL.BUSINESS_UNIT 
               AND TMP2.PRODUCTION_ID = OPL.PRODUCTION_ID 
               AND TMP2.OP_SEQUENCE = OPL.OP_SEQUENCE 
               AND TMP2.COST_ELEMENT= TMP.COST_ELEMENT) 

GROUP BY

OPL.BUSINESS_UNIT,OPL.PRODUCTION_ID,OPL.OP_SEQUENCE,PID.INV_ITEM_ID, 
TMP.COST_ELEMENT,PID.ORIG_UOM,PID.PRDN_AREA_CODE,PID.PRODUCTION_TYPE, 
OPL.PERCENT_COMP,OPL.QTY_SCRAPPED 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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 Fri Nov 15 2002 - 11:35:05 CST