Amir, you don't have any limiting condition. Table apps.WSH_DELIVERY_ASSIGNMENT, according to your query, must be read in its entirety. The most efficient way to do so is the full table scan. Yu can try with /*+ parallel */ if the hardware is such that you can do that. On 04/03/2006 05:21:46 PM, Hameed, Amir 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 > > -- > //www.freelists.org/webpage/oracle-l > > > -- Mladen Gogala http://www.mgogala.com -- //www.freelists.org/webpage/oracle-l