Re: Query Optimizer

-----Original Message-----
From: Pete Hall <pbhall@execpc.com>
To: MIDRANGE-L@midrange.com <MIDRANGE-L@midrange.com>
Date: Monday, November 08, 1999 10:28 PM
Subject: Re: Query Optimizer


>The file is on the dependent side of a left outer join.


EEEK. I assume that this is the key you expect the query to use. I am not
100% certain of this, but the dependant side (right side) is an internal key
to the logical that implements the join, and as such, I don't think it is
"available" to the query optimizer.

You said it didn't use the access path, that it built it's own. So I also
assume that you had the job in debug mode to get the messages? If so, then
did you just get the message that it was building an access path because no
usable path was found?  Or did you get a message about the other index being
checked but not usable?

Again, I would assume that you get the former message as the optimizer does
not "see" the internal join key as available. If you got the latter message,
it should have a reason for rejection on it. If you never see the access
path considered and it builds it's own, then that would confirm my original
suspicion.



>>  How much of the file
>>is ultimately selected?
>Hmmm. I don't know. I'd estimate 60-80%. I was doing some testing on a
>rather complex view, looking for messages that would indicate which indexes
>were needed. What are the implications of selecting a large vs a small
>percentage of the data?


The size thing usually determines whether a table scan takes place. The rule
of thumb is around 20%. In other words, it you select a small subset of the
file, the index is more likely to be used, but if you select a large portion
of the data, then a table scan will work faster than the dual I/O imposed by
keyed access. When a larger percentage of the file is returned, then it is
usually something other than the where clause the builds indexes. Like join,
order by and group by.

As for the messages about indexes needed, are you using the Database monitor
(STRDBMON) output information? If you have V4R4, have you looked at the
database functions in OpsNav?

===========================================================
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!"


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