Re: How do you like this SQL?

"John Hurley" <johnthehurley_at_gmail.com> wrote in message news: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
|>
|
|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).

The join and big hash group by looks as if Oracle has unnested and then done complex view merging.
Alternatives are:
- unnest, aggregate and join (which might turn into a nested loop join if the volume is relatively low and the indexes reasonably precise) - transform to existence (which might be suitable if earlier joins have filtered the data to a reasonable size - and might work very efficient with a min/max range scan if the approprirate index existed)

The best choice depends on the data volumes, and Oracle may be taking a very bad path because of problems with stats (possibly histograms, given the predicate on comment type) and lack of suitable indexes.

As far as correctness goes, my primary thought about correctness was that the developer might have been after the most recent comment of type 'LE' if such existed, but has actually written code to report comments that matched the timestamp of the most recent comment of type LE. (which would include the LE comment, of course, but any other of the same timestamp). The code as written wouldn't be an error if two comments of different types couldn't be created on the same timestamp (on the same po line).

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543 Received on Sat Dec 15 2012 - 10:41:56 CET