RE: Optimizing a query

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Mon, 3 Apr 2006 17:50:58 -0400
Message-ID: <77A4D80DB2ADD74EB5D7F1D31626F0C003217B69@usa0300ms03.na.xerox.net>

I can paste the entire query but over 90% of the cost is coming out from this subquery.  

SELECT TSCI.ORDER_TYPE, TSCI.DELIVERY_DETAIL_ID, TSCI.SHIP_METHOD_CODE, TSCI.SHIP_TYPE, TSCI.CUSTOMER_ID, TSCI.SHIP_TO_SITE_USE_ID, TSCI.SHIP_TO_LOCATION_ID, TSCI.SHIP_TO_CONTACT_ID, TSCI.NET_WEIGHT, NVL(TO_CHAR(TSCI.SHIP_TO_CONTACT_ID),OEH.CUST_PO_NUMBER) CUST_PO_NUMBER, TSCI.RELEASED_STATUS, TSCI.ORDER_HEADER_ID, TSCI.ORDER_LINE_ID, WDA.DELIVERY_ID, WC.FREIGHT_CODE CARRIER_CODE, OEH.ORG_ID, TSCI.SHIP_FROM_ORGANIZATION_ID, WC.CARRIER_ID, OEH.ORDER_NUMBER, OEH.CUST_PO_NUMBER CUST_PO_NUM
FROM TXRSCI0_SUPPLIES_CARR_INTF TSCI,

       OE_ORDER_HEADERS_ALL OEH,
       ( SELECT DELIVERY_DETAIL_ID,DELIVERY_ID
       FROM 
              ( SELECT MIN(DELIVERY_DETAIL_ID) OVER (PARTITION BY
DELIVERY_ID) MIN_DELIVERY_DET_ID, DELIVERY_DETAIL_ID,DELIVERY_ID
              FROM WSH_DELIVERY_ASSIGNMENTS
              )
       WHERE MIN_DELIVERY_DET_ID = DELIVERY_DETAIL_ID
       ) WDA,
       WSH_CARRIER_SERVICES WCS,
       WSH_CARRIERS WC
WHERE PROCESS_STATUS_CODE = 'PENDING' AND
       ORDER_TYPE = 'SHIPMENT' AND
       TSCI.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID AND
       WDA.DELIVERY_ID = NVL(:B3 ,WDA.DELIVERY_ID) AND
       OEH.HEADER_ID = TSCI.ORDER_HEADER_ID AND
       OEH.ORDER_NUMBER = NVL(:B2 ,OEH.ORDER_NUMBER) AND
       WCS.SHIP_METHOD_CODE = TSCI.SHIP_METHOD_CODE AND
       WCS.CARRIER_ID = WC.CARRIER_ID AND
       WC.FREIGHT_CODE = NVL(:B1 ,WC.FREIGHT_CODE) AND
       TSCI.SHIP_METHOD_CODE = WCS.SHIP_METHOD_CODE AND
       UPPER(WCS.MODE_OF_TRANSPORT) = 'PARCEL'
 

I am thinking parallel hint will improve the response time a little bit better.

---

	From: LiShan Cheng [mailto:exriscer_at_gmail.com] 
	Sent: Monday, April 03, 2006 5:45 PM
	To: Hameed, Amir
	Cc: oracle-l_at_freelists.org
	Subject: Re: Optimizing a query
	
	
	Hi
	 
	If you dont have any condition then I dont see any possibility

of using index at all?                   

        On 4/3/06, Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:

		Folks,
		I need to optimize the following query:
		
		SELECT DELIVERY_DETAIL_ID,DELIVERY_ID
		FROM
		( SELECT MIN(DELIVERY_DETAIL_ID) OVER (PARTITION BY
DELIVERY_ID)
		MIN_DELIVERY_DET_ID,
		               DELIVERY_DETAIL_ID,DELIVERY_I
		   FROM apps.WSH_DELIVERY_ASSIGNMENTS
		)
		WHERE MIN_DELIVERY_DET_ID = DELIVERY_DETAIL_ID 
		/
		
		This table has the following indices:
		
	
COL  DISTINCT
		         INDEX NAME                  COLUMN NAME
POS    ROWS
		SELECTIVITY
		------------------------------ -------------------------
--- ----------- 
		-------
		WSH_DELIVERY_ASSIGNMENTS_N1    DELIVERY_ID
1     630,301
		8.29
		WSH_DELIVERY_ASSIGNMENTS_N2    PARENT_DELIVERY_ID
1           0
		0.00
		WSH_DELIVERY_ASSIGNMENTS_N3    DELIVERY_DETAIL_ID
1   7,605,650 
		100.00
		WSH_DELIVERY_ASSIGNMENTS_N4    PARENT_DELIVERY_DETAIL_ID
1     377,456
		4.96
		WSH_DELIVERY_ASSIGNMENTS_U1    DELIVERY_ASSIGNMENT_ID
1   7,605,650
		100.00
		
		What would be the best way to optimize it. It currently
does a FTS on 
		this table. Any help will be appreciated.
		Thanks
		Amir
		
		--
		http://www.freelists.org/webpage/oracle-l
		
		
		

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 03 2006 - 16:50:58 CDT