Re: Optimizing a query

Hi Amir,

Your query is equivalent to

   select delivery_id, min(delivery_detail_id)    from wsh_delivery_assignments
   group by delivery_id;

however, the optimizer can push predicates down into group by subqueries, whereas I believe your syntax forces a FTS.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/         - For DBAs
@   http://www.christianity.net.au/  - For all

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

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