Optimizing a query

  • From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 3 Apr 2006 17:21:46 -0400

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

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


Other related posts: