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:
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