Optimizing a query

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

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

Received on Mon Apr 03 2006 - 16:21:46 CDT