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

Oracle LazyDBA home page