Re: SQL Question

["R. Bruce Hoffman, Jr." <rbruceh@xxxxxxxxxxxxx>]

-----Original Message-----
From: John Taylor <john.taylor@telusplanet.net>
To: MIDRANGE-L@midrange.com <MIDRANGE-L@midrange.com>
Date: Thursday, June 29, 2000 9:45 PM
Subject: Re: SQL Question


>> sidebar...  you don't (shouldn't) need the the group by clause in a
>> correlated subquery as the selection is already limited to a single order
>> number.  In V4R2 (definitely) and up you don't.  The requirement here is
>> that the correlated subquery return a single value, and with just the
>where
>> clause, it does so.
>
>You're absolutely correct Bruce. It isn't a requirement.
>
>Just keep in mind that on the AS/400, the access plan is determined by the
>query optimizer. In the above example,  the GROUP BY clause causes the
>optimizer to utilize a processing method referred to as "Index Skip Key
>processing". This improves performance by eliminating the need to process
>all index key values for the group, if there is an appropriate index built
>over the (OrderNo, SeqNo) column pair. In other words, the query doesn't
>have to check _every_ item line in order to determine the one with the
>lowest sequence number.
>


Yes, and the Group by also implies sorted order to the optimizer.  I have
had no performance improvements by including the group by in this particular
type of query, but as I mentioned, the data that I use this on regularly,
has the proper indexing.  The where clause does the trick on the 400.  With
the runstats info being live and index scans occuring, the group by hint
does not appear to be necessary on this platform.  On MVS, NT and Linux...
different story.

But you bring up a point about the underlying performance issues and the
reason to have a DBA in large database environments, including the 400.
Sometimes, you have to give the optimizer a "hint" about where to find the
"right" access path on a *given* platform.  Full understanding of the
structure of the data and it's indexes can be critical in some applications,
as does the understanding of the underlying optimizer.

And for all of IBM's bluster about one DB2, alas, there are *still* three
flavors.


===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified AS/400 Professional System Administrator
 -- IBM Certified AS/400 Professional Network Administrator
 -- IBM Certified Specialist - AS/400 Administrator
 -- IBM Certified Specialist - RPG IV Developer

"If all you have is RPG, then everything looks like a 400!"


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---