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


Other related posts: