Re: SQL select via logicals

[John Carr <74711.77@xxxxxxxxxxxxxx>]

RE:     Re: SQL select via logicals

I forwarded our discussion on to Kent Milligan who works on DB2/400
to get his opinions.  This is what he wrote back.  (I apparently I had it
reversed,  It looks at the newest indexes first.  I was correct in saying
that if you specify the LF name, it will be looked at first.)
John Carr
EdgeTech


Kent said;
The optimizer tries to spend a fraction of the total query time in query
optimization (ie, looking at indexes).  The optimizer doesn't want to spend 3
minutes optimizing looking at all the indexes when your query only takes 3
seconds to run.  So there really isn't a good number to give you for the number
of indexes.  In addition, the SQL interface doesn't give you an option for
specifying that all indexes be considered - OPNQRYF does offer such a
parameter.

A couple of ways to influence the optimizer is by specifying the logical file
directly on the SQL statement so that the optimizer does look at that access
path first.  The other trick is recreating the index - due to the way index
information is organized in the underlying database file object the optimizer
is typically given indexes in a most recently created order (eg, first index
examined, tends to be the index most recently created).  These methods provide
ways of influencing the optimizer, but aren't guaranteed to work in all cases.


DB2/400 Home Page (Goto AS/400 Home Page, choose software, then database, and
you'll find a Coming Attractions link).  The actual URL is:
http://iws.as400.ibm.com/db2/new4art.htm


Kent Milligan, DB2/400 Solutions Team
AS/400 Partners In Development

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@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
+---