FreeLists / oracle-l / RE: Optimizing a query

["Hameed, Amir" <Amir.Hameed@xxxxxxxxx>]

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@xxxxxxxxx] 
        Sent: Monday, April 03, 2006 5:45 PM
        To: Hameed, Amir
        Cc: oracle-l@xxxxxxxxxxxxx
        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@xxxxxxxxx> 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