Re: Query Optimizer

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

Don't know if you got responses to this, my e-mail account has been mixed up
with all of that IBM-ATT transition stuff, but....

Need more info to give you a good answer. You say the that it built a match
to second of two logicals. Is there a where clause in the select? Is the
logical file *IMMED maint? Does the logical have select/omit? Are you sure
the keys are EXACTLY the same, not a different order? How much of the file
is ultimately selected?

As for the difference between an index created by SQL and DDS, that depends.
Ultimately, the only real indication of where the logical was created is a
singular flag in the object. It is not used for anything, it just indicates
the source of the logical. With the latest PTFs for database, and the new
indexes, there could be other differences. The access path size parameter
influences the behavior of the logical, but mostly in the case of siezes.
The only index that is different is the Encoded Vector Index available in
V4R4.

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

"The sum of all human knowledge is a fixed constant.
    It's the population that keeps growing!"

-----Original Message-----
From: Pete Hall <pbhall@execpc.com>
To: MIDRANGE-L@midrange.com <MIDRANGE-L@midrange.com>
Date: Thursday, November 04, 1999 2:23 PM
Subject: Query Optimizer


>Does anyone have a clue as to why the query optimizer would chose to build
>a temporary access path with a key that exactly matches the second of two
>logical files (no includes or omits)? The explanation says that "The cost
>to use this access path, as determined by the optimizer, was higher than
>the cost associated with the chosen access method" (which was to build a
>temporary index). Does the fact that this is a logical file (DDS) as
>opposed to and SQL index have any bearing on the matter? This is at V4R3,
>current on database PTFs.
>Pete Hall
>pbhall@execpc.com
>http://www.execpc.com/~pbhall
>+---
>| 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
>+---
>
+---
| 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
+---