Re: SQL Question

["John Taylor" <john.taylor@xxxxxxxxxxxxxxx>]

> -----Original Message-----
> From: owner-midrange-l@midrange.com
> [mailto:owner-midrange-l@midrange.com] On Behalf Of R. Bruce Hoffman,
> Jr.
> Sent: Friday, June 30, 2000 5:39 AM
> To: MIDRANGE-L@midrange.com
> Subject: Re: SQL Question

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

With respect to "proper" indexing, it should be mentioned that the query
optimizer does not always consider every index built over the file. For a
dynamic SQL statement (such as Brad's example), the optimizer is subject to
a time limit when choosing an implementation. Index selection is handled by
examining each index in turn from most recently created to the oldest, until
the optimizer runs out of indexes, or time.

A blanket statement such as "the group by hint does not appear to be
necessary on this platform" is a bit too generalized. More appropriately,
the group by does not appear to be necessary in your specific test case.
Something as minor as the addition of another index over one of your files
may cause the system to create a new access method, and thereby potentially
alter your performance results.

Since we can't directly control how the access plan is determined, the best
defense is to give the optimizer as much information as possible in your SQL
statement.

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

For the most part, I agree. The only thing that I question is the value of a
DBA. I think the developers need to understand this stuff themselves. You've
obviously invested some time in studying the issue yourself, so I'm sure the
you'll agree.

Regards,

John Taylor
Canada


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