It was bugging me all weekend and with the help of Mladen I came up with a solution.
The original SQL ( again part of an inline view in a much bigger piece of SQL ) looked like this:
FROM SCHEMA_NAME.PO_COMMENT POC
FROM SCHEMA_NAME.PO_COMMENT A /* The same table */
GROUP BY A.PO_ID,
WITH INNER AS
( SELECT PO_ID, LINE_ITEM_PO, COMMENT_PO, DATE_ADD_COMMENT, ROW_TIMESTAMP,
MAX(ROW_TIMESTAMP) OVER (PARTITION BY PO_ID,LINE_ITEM_PO) AS MAXROW
WHERE TYPE_PO_COMMENT='LE' )
SELECT PO_ID, LINE_ITEM_PO, COMMENT_PO, DATE_ADD_COMMENT FROM INNER WHERE ROW_TIMESTAMP = MAXROW
*** Still some weird things in the big SQL I do not like.
End result ... run time drops into 20 second range from 15 minute range.
After the rewrite optimizer able to push predicate into this inline view and use PO_ID ... an existing multi column index on PO_ID, LINE_ITEM_PO and TYPE_PO_COMMENT can now be used.
I am doing a powerpoint on this for NEOOUG will put up link to it eventually. Powerpoint will include SQL monitor output for bad run and fixed version showing stats and execution plan.
UPDATE: Kim posted an alternative analytic rewrite that looks promising also just a little different than mine in syntax mostly ...
, ROW_NUMBER() OVER (
PARTITION BY POC.PO_ID
ORDER BY POC.ROW_TIMESTAMP DESC
FROM PO_COMMENT POC
WHERE RN = 1