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 > > -- > //www.freelists.org/webpage/oracle-l > > >