Part of a very long query put together by a developer and of course implemented in production. Lots of inline views ( not a bad idea many times ) ... big tables it was going against ... many of them. Still we have a pretty fast system ...
Things were still ok until you get to this part of the SQL:
(SELECT POC.PO_ID,
POC.LINE_ITEM_PO,
POC.COMMENT_PO,
POC.DATE_ADD_COMMENT
FROM PO_COMMENT POC
WHERE (POC.PO_ID,POC.LINE_ITEM_PO,POC.ROW_TIMESTAMP) IN
(SELECT A.PO_ID,A.LINE_ITEM_PO,MAX(A.ROW_TIMESTAMP) FROM PO_COMMENT A
WHERE A.TYPE_PO_COMMENT='LE' GROUP BY A.PO_ID, A.LINE_ITEM_PO) ) T193
So were are operating against one table PO_COMMENT ... lots of rows in there.
The WHERE clause takes multiple columns simultaneously and looks for that combination in ... where else ... the same blank ... table???
Using MAX(A.ROW_TIMESTAMP) to get the most recent one.
How might one fix this bad part of the SQL?
How about not doing an IN against the same table?
Just one of those days eh?
UPDATE: New blog post shows a fix using analytic rewrite ... rewritten
Teach your developer analytic functions? Maybe if he learns well, you get fewer of "those days" in the future? ;-)
ReplyDelete(
SELECT PO_ID
, LINE_ITEM_PO
, COMMENT_PO
, DATE_ADD_COMMENT
FROM (
SELECT POC.PO_ID
, POC.LINE_ITEM_PO
, POC.COMMENT_PO
, POC.DATE_ADD_COMMENT
, ROW_NUMBER() OVER (
PARTITION BY POC.PO_ID
, POC.LINE_ITEM_PO
ORDER BY POC.ROW_TIMESTAMP DESC
) RN
FROM PO_COMMENT POC
WHERE POC.TYPE_PO_COMMENT='LE'
)
WHERE RN = 1
) T193
:-)
Thanks Kim ( sorry did not see your comment ) yes right on target.
ReplyDeleteYes this old DBA also needs to spend some more time learning analytics but did come up with a solution ( thanks to Mladen Gogala sp? ) using analytics ... solution posted separately.
The solution even was able to use PO_ID from other join and now does lookup using existing multi column index on PO_ID, LINE_ITEM_PO and TYPE_PO_COMMENT ...