Hello Rick,

try rewriting your sql like this:

select * from (
select count(a.cmid)
from packout a
where ordernum = '11111111111'
order by count(a.cmid) desc
)
where rownum =1 ;

Stefan


> I'm trying to tune the following statement:
>
> SELECT COUNT (a.cmid)
> FROM assembly.packout a
> INNER JOIN
> (SELECT cmid, MAX (packdate) AS maxdate
> FROM assembly.packout
> GROUP BY cmid) b ON (a.cmid = b.cmid) AND (a.packdate =
> b.maxdate)
> GROUP BY ordernum
> HAVING ordernum = '11111111111'
>
> It's taking about 19 seconds to run. It takes about 1 second to run on
> a SQL server with similar hardware. I've played around with other
> indexes, but I haven't got the time to move.
>
> Explain Plan shows:
>
> Operation Object Name Rows Bytes Cost Object Node
> In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 1 10314
>
>
> FILTER
> SORT GROUP BY 1 40 10314
> HASH JOIN 13 520 10308
> VIEW 1 M 26 M 7273
> SORT GROUP BY 1 M 23 M 7273
> INDEX FAST FULL SCAN ASSEMBLY.PK_PACKOUT 1 M
> 23
> M 1203
> INDEX FAST FULL SCAN ASSEMBLY.PK_PACKOUT 1 M 45 M
> 1203
>
>
> The table looks like:
> COLUMN_NAME DATA_TYPE
> CSN VARCHAR2
> PACKDATE DATE
> CMID NUMBER
> ORDERNUM VARCHAR2
> SSN VARCHAR2
> SWRUNNUM VARCHAR2
> STATUS VARCHAR2
> REASON VARCHAR2
> SYSTEM VARCHAR2
>
> Primary key is on CSN, PACKDATE, CMID & ORDERNUM. Table has about 1.9
> million rows. Any suggestions would be greatly appreciated.
> Running Oracle 9.2.0.6 on Win2K. Thanks.
>
> Rick Howell
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these
> terms:http://www.lazydba.com/legal.html




Oracle LazyDBA home page