RE: Optimizing a query
- From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
- To: "Mladen Gogala" <gogala@xxxxxxxxxxxxx>
- Date: Mon, 3 Apr 2006 19:47:46 -0400
Thanks Mladen! The production server does have 40 CPUs. I am looking at
the optimization from the RAC standpoint. Would PQO-based FTS be better
than regular (buffer-based) FTS in RAC?
-----Original Message-----
From: Mladen Gogala [mailto:gogala@xxxxxxxxxxxxx]
Sent: Monday, April 03, 2006 7:01 PM
To: Hameed, Amir
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Optimizing a query
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
Other related posts:
