Re: How do you like this SQL?

From: John Hurley <johnthehurley_at_gmail.com>
Date: Fri, 14 Dec 2012 12:00:55 -0800 (PST)
Message-ID: <2739e0ab-db1e-45aa-aafa-ffd1b1e1a30a_at_u19g2000yqj.googlegroups.com>

On Dec 14, 5:13 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "John Hurley" <johnthehur..._at_gmail.com> wrote in message
>
> news:bd873b2b-24bd-43e5-a0ba-9329eb72c616_at_f19g2000vbv.googlegroups.com...
> | One of my developers ... part of a very long piece of SQL near the
> | end ... lots of inline views ... and this creative piece of code:
> |
> | (
> | 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
> |
> | Plus of course ... PO_COMMENT is a very big table ...
>
> There are a couple of ways in which the code fragment might be
> undesirable - is it the possible logic error, or the possible performance
> impact you are trying to highlight - but there's nothing inherently bad
> about it that an outside observer should condemn without first checking
> context.

My first initial reaction ( and wrong ) was that a group by could be done on all 4 other columns PO_ID PO_LINE_ITEM COMMENT_PO and DATE_ADD_COMMENT along with MAX(ROW_TIMESTAMP) ...

That gives though more rows than just the group by on PO_ID and PO_LINE_ITEM. Oracle plan gives a hash join over two full table scans of PO_COMMENT and a huge chewing of temp space ... so working on some alternate approaches.

>
> (The fact that PO_COMMENT is a very big table could be irrelevant if
> comments of type 'LE' were very rare).
Received on Fri Dec 14 2012 - 21:00:55 CET